I have an income stream that does not feed an account in Tiller. It is not a traditional income stream but rather crypto-related. I tried to manually add an income transaction with the Tiller Community Solution but I have custom columns in my Transactions sheet and the manually added transaction via Solution disrupted the sheet. I do not need to know the Crypto account balance but rather just record the income value as “Other Income”.
So, is there a way to manually add a transaction without the Community Solution?
Not sure why the “Add Transaction” tool wouldn’t work, I have lots of extra columns and it works fine for me. In any case, you can always just insert a row in your Transactions sheet and type in the info manually. I often do this when PayPal doesn’t include a transfer transaction.
The Add Transaction workflow shouldn’t be disrupting your sheet with added columns. Like @jpfieber I have several custom columns and can use the Community Solution without any issues. That said, my custom columns are all manual data entries. I’m not sure if you are using formulas in your custom columns, so I’d have to play with it to see if formulas combined with the Add Transaction workflow can create issues.
Mind definitely does cause an issue. It injects the below column and throws a #REF! error. I also do have a custom column that pulls in the Groups and Tyes of the transactions from the Categories page. Below is a formula example:
Is it possible that the workflow is inserting a "" instead of a null character in the column, disrupting the formula’s ability to fill? Can you try deleting the contents of the entire column (or at least in the new row) and seeing if the ARRAYFORMULA() fills?
That’s a very good thought, @randy. I’ve been doing a lot of ARRAYFORMULA() work recently, and #REF expansion issues due to non-empty cells are something I’ve seen quite a bit of.
@hbwilliams22, what is the specific #REF error you get after adding the transaction?
I did some testing of my own since I’ve been doing some work with an ARRAYFORMULA() column in my Transactions sheet in the last couple of days, and I ran across an interesting issue… I didn’t get an #REF error like @hbwilliams22, but after following the Add Transaction workflow I noticed an offset in where my calculated column dropped values versus where they should be. Running the Fill Sheets workflow and adding new transactions to the sheet does not create any offset. I checked my formula in the header row, and noticed that after adding a manual transaction it changed from this
=QUERY({$B$2:$B,{arrayformula(iferror(-1*vlookup(ARRAYFORMULA(IFERROR(TEXT(TO_DATE($B$2:$B),"m/d/yyyy")&"|"&{ARRAYFORMULA(IFERROR(REGEXEXTRACT($P$2:$P,"^Amazon Order ID (\d{3}-\d{7}-\d{7}):")))}&"|"&{ARRAYFORMULA(IFERROR(REGEXEXTRACT($P$2:$P,"\(([[:alnum:]]+)\)$")))})),'Amazon Sales Tax'!$AC$2:$AD,2,false),IFERROR(1/0)))},$C$2:$C},"SELECT Col2 LABEL Col2 'Amazon Sales Tax Formula'")
to this
=QUERY({$B$3:$B,{arrayformula(iferror(-1*vlookup(ARRAYFORMULA(IFERROR(TEXT(TO_DATE($B$3:$B),"m/d/yyyy")&"|"&{ARRAYFORMULA(IFERROR(REGEXEXTRACT($P$3:$P,"^Amazon Order ID (\d{3}-\d{7}-\d{7}):")))}&"|"&{ARRAYFORMULA(IFERROR(REGEXEXTRACT($P$3:$P,"\(([[:alnum:]]+)\)$")))})),'Amazon Sales Tax'!$AC$2:$AD,2,false),IFERROR(1/0)))},$C$3:$C},"SELECT Col2 LABEL Col2 'Amazon Sales Tax Formula'")
It appears that the insertion workflow for Add Transaction is doing something differently than the insertion workflow for Fill Sheets, causing Google Sheets to shift the references ranges in the Transactions sheet even though the top row has been anchored with $s. The external sheet reference ranges were unaffected.
@hbwilliams22, do you notice anything changing with your formulas after following the Add Transaction workflow?
I think the issue is that the record has a Type attribute coming out of the add-transaction sidebar… and it is trying to fill it in a matching column. Could you see if it works if you call the formula-driven Type column anything else? I think it will.
