If you use a tag with an apostrophe in it (like, for example, “John’s room”) it looks like the Tags Report includes it in the All Tags total, but it will not provide a breakdown in the Tags Transaction Details when you filter on that particular tag: the apostrophe trips up the filter. Not a big deal (I can live without the apostrophe), but this seems like a bug (perhaps, a gnat-sized one).
Hi @mtjfinancial,
You are correct that an apostrophe will break the Tags Transactions Details section. Thanks for making us aware of this gnat.
As a quick fix you can update the formula in E9 to:
=IFERROR(IFNA(QUERY({INDIRECT(Q5),INDIRECT(Q6),INDIRECT(Q7),INDIRECT(Q8),ARRAYFORMULA(","& INDIRECT(Q9) &",")},"SELECT Col1,Col2,Col3,Col4 WHERE Col5 CONTAINS "","&F4&","" AND Col5 != ',,' AND Col1 >= date '"&TEXT(O18,"yyyy-mm-dd")&"' AND Col1 <= date '"&TEXT(O19,"yyyy-mm-dd")&"' ORDER BY Col1 ASC",0),"No results"),IFERROR(1/0))
This new formula should escape any apostrophe’s in the Tag search filter box.
The original formula:
=IFERROR(IFNA(QUERY({INDIRECT(Q5),INDIRECT(Q6),INDIRECT(Q7),INDIRECT(Q8),ARRAYFORMULA(","& INDIRECT(Q9) &",")},"SELECT Col1,Col2,Col3,Col4 WHERE Col5 CONTAINS ',"&F4&",' AND Col5 != ',,' AND Col1 >= date '"&TEXT(O18,"yyyy-mm-dd")&"' AND Col1 <= date '"&TEXT(O19,"yyyy-mm-dd")&"' ORDER BY Col1 ASC",0),"No results"),IFERROR(1/0))
had just a single apostrophe around the Tag search term in F4. The new formula includes a double double-quote around the search with solves the problem of a single quote inside the search term.
We will consider making an update to the template after some further testing.
Let us know if this fixes the problem for you.
Jon
Great - that worked! I’ll let you know if I notice any issues. Thanks!
1 Like
@jono It looks like the formula in the Category Details area needs a similar change; I added the quotes and it works. I modified the report to pull in the notes column from my Transactions tab so I’m not sure of the original cell location - probably J9?
@mtjfinancial,
Thanks again for alerting us. You are correct.
We will be making a change to the template shortly.
To get the Category Details section to work the an apostrophe in the tag name, the formula in J8 should be changed to include double quotes as suggested:
=IFERROR(IFNA(query({INDIRECT(Q7),INDIRECT(Q8),INDIRECT(Q5),ARRAYFORMULA(","& INDIRECT(Q9) &",")},"SELECT Col1,SUM(Col2),COUNT(Col2) WHERE Col4 CONTAINS "","&F4&","" AND Col1 IS NOT NULL AND Col4 != ',,' AND Col3 >= date '"&TEXT(O18,"yyyy-mm-dd")&"' AND Col3 <= date '"&TEXT(O19,"yyyy-mm-dd")&"' GROUP BY Col1 ORDER BY SUM(Col2) LABEL Col1 'Category',SUM(Col2) 'Total', COUNT(Col2) 'Count'"),"No Results"),"Category")
This was the original:
=IFERROR(IFNA(query({INDIRECT(Q7),INDIRECT(Q8),INDIRECT(Q5),ARRAYFORMULA(","& INDIRECT(Q9) &",")},"SELECT Col1,SUM(Col2),COUNT(Col2) WHERE Col4 CONTAINS ',"&F4&",' AND Col1 IS NOT NULL AND Col4 != ',,' AND Col3 >= date '"&TEXT(O18,"yyyy-mm-dd")&"' AND Col3 <= date '"&TEXT(O19,"yyyy-mm-dd")&"' GROUP BY Col1 ORDER BY SUM(Col2) LABEL Col1 'Category',SUM(Col2) 'Total', COUNT(Col2) 'Count'"),"No Results"),"Category")
Jon
We have an updated version of the Tags Report, V1.01, which now handles tags correctly if they have apostrophes in them. Go to the Tiller Labs Add-on and update your Tags Report to get the new sheet. h/t to @mtjfinancial for pointing out this issue.
Jon