Hello! Day one using Tiller and I feel like I’m understanding most of it but I’m having an issue with auto-cat. Basically I’ve set two rules, one for adding a category to my transaction based off description and the other for adding to a custom column (personal/shared) based off of the transaction account.
Category
Description Contains
Account Equals
Personal/Shared
Blue Cash Preferred
Shared
Groceries
Whole Foods
When there’s a transaction where both of these rules apply, it just applies the first rule and not the second. Re-ordering the rules will change which rule it applies but I don’t understand how to apply both. Should I not use auto-cat for this?
I don’t want to do that because these two rules aren’t related. How I categorize “Whole Foods” has nothing to do with which Credit Card I used to make the purchase.
But if you use your Amex at Whole foods, isn’t that groceries and shared? Basically, autocat stops running when it gets a match so you might need to create a few rules to make sure you catch everything but allow for other options.
I hear you but that would get wayyyy too complicated, I can’t write a rule for every possible combination of category and payment type. My other thought was I could try to write a formula for personal/shared but I’d prefer this field was editable. Hm…
I may be wrong, but I believe that AutoCat will only apply a single rule to a transaction per run. Here’s another experiment you can try… Make sure the Personal/Shared column rule is before the rule that sets the category and run AutoCat with at least one qualifying uncategorized transaction. Verify that the Personal/Shared column is populated, and then run AutoCat again. This time the category should be set. It’s still an extra step, but this way you at least won’t need to make a rule for every permutation of category and payment method.
If you don’t want to manually run AutoCat a second time every time, it should at least become a little more organic as you import more transactions. Personal/Shared will populate on the day transactions are imported, and the category will be set the next time transactions are imported and AutoCat runs.
As @cculber2 points out, the AutoCat function, while amazing, stops running for a rule when the first set of criteria are satisfied. So, let’s skin this cat (Ha! had to say it… ) another way by treating the issues separately.
In AutoCat, keep your rule for Whole Foods
In AutoCat, delete your rule for the Blue Cash Preferred Account (We’ll nail this another way.)
This fix assumes there is only one condition where the word, “Shared” needs to appear in your custom column: where the Account is “Blue Cash Preferred.” If this is not the case, let me know, and we’ll find an alternative.
On your Transaction Sheet, make a copy of the data in your custom column called, “Personal/Shared,” if you want to keep it. To add this fix, you’ll need to delete the data in the column so the header formula can run.
Clear all the data out of your custom column called, “Personal/Shared.”
In your spreadsheet, note which column holds your Account names. If you’ve made few changes from the out-of-the-box template, it is likely column D, and the formula below should work. If it isn’t column D, then replace the “Ds” in the formula below with the letter of your sheet’s column for Account.
Delete the header label from your custom column and in its place, copy and paste this formula:
Your Personal/Shared column should now populate “Shared” automatically for each instance of the account, “Blue Cash Preferred” and include new transactions as they drop into your sheet.
Hoping this works for you!
Let us know…Working with out a net, here, and lots of really smart people in this community. (There is a high likelihood one will come to our rescue.)
This is a very nice outside-the-box solution, @Brad.warren! The only caution I will make with this approach is that any manual transactions (Tiller Community Solutions → Add Transaction) will break custom formulas in the header row via row drift (D2:D becomes D3:D, etc). This will occur whether or not you use $ to make the row reference constant.
You are correct, @cculber2 ! Great work. I had forgotten that the semi-colon in the formula actually stacks the terms so that the array formula is actually in row 2. Bummer. I was only thinking of how new transactions are added to the end of the sheet then sorted upward by date.
Here is one alternative, @hayleyharrison17,if you can live without a heading for the custom row. Put this formula in place of your header, and the formula will not be affected by the addition of new rows at the top:
Great Q, I would need it to be multiple cards that would designate as Shared and multiple cards that would designate as Personal. Now that I’m thinking about it the account number would probably be better to use.
Thanks so much for sharing the formula, I’m pretty bad at writing them so I’d love any advice on how to add those additional options here.
The only negative about using a formula imo is that I can’t change the designation if it’s wrong (say I used the wrong card). Some background, I used to use a money manager app called Zeta (it’s shutting down) and it was really built around couples financing so I’m trying to recreate as much of that in Tiller as possible.
You should use the following, which will also give you a header, and fix the error. If you use the quote character, the forum makes it fancy, which breaks the formula. I pasted it in using tags, so you should be able to just copy/paste.
I tested this out and after rereading the AutoCat documentation, this result makes sense. The first rule will always match first and stop processing other rules for that transaction. I found a way to get around it and allow the second run of AutoCat to add the category. I added a new criteria column to the first rule, Personal/Shared Regex = ^$. This will cause the rule to only match if Personal/Shared is not populated and allow a second run to set the category.
I should have guessed about the quotes. I wasn’t able to get the header working (I’m going with “Ownership” for the column name) so I cobbled together an adjustment based on another formula I was using:
This seems to work fine but was there a specific reason to use the error formula? Otherwise the only thing I’d want to figure out is if I can add multiple options where for example “A” or “B” or “C” give me “Shared” and “D” or “E” or “F” give me “Personal.”
I actually had a thought where maybe I could combine the autocat and this formula. The formula would be like an initial assessment and then autocat would essentially copy that for me into an editable version of the field. It would mean that every rule would need a “personal” and “shared” version but that’s a lot more manageable than a rule for every payment type.
For my formula, you’d put that in the header. This way as you add rows it will still calculate. The formula then returns an error in the column header row, so it then returns the string you insert.
After the formula fills out the column, copy the entire column and paste as values to make it permanent and editable.
Going forward you can use two AutoCat rules, one rule to catch all “Shared” accounts, and one rule to catch all “Personal” accounts, followed by your normal category rules. It still requires two runs of AutoCat, but reduces the number of rules needed for the “Ownership” column.
Multiple Match Criteria
AutoCat supports multiple match criteria in text filter criteria fields.
For example, a “Description Contains” rule can be:
Multiple match criteria are OR-ed. In other words, the rule will be applied if the row matches just one of the keywords in the list for the rule.
Each multiple-match criteria keyword must be wrapped in quotes and separated by commas.
) another way by treating the issues separately.