Hello!
I have been working on figuring out this issue forever and could really use some help. Basically, I have a switch formula which pulls in states from another table based on certain criteria. But there are times I need to make an exception for a particular state so I created a column to list states a user might want removed from the list of states. For instance, if there is a list of 30 states which includes AL, but AL cannot be used, I want the user to be able to select AL in the Exception column and it not show in the State column. The problem is the way the state data shows, it groups the states together from the table the formula is referencing, so it’s not as easy as using Not(Contains) or !=. I’ve tried listcombine() for the state and I can get it to show only the exception state with a formula like: Variations.State.ListCombine().Unique().Filter(ToText(CurrentValue).Contains("AL")).State
… but I can’t get it to do the opposite.
I’ve made a dummy doc which is paired down to illustrate my issue. I’m working in the State column on the Schedule page/Asset Schedule table.
(The State Override column is for when you have a small number of states which need to override a large number of states. The Exception is for when you need to pull out a small number of states from a large number.)
Any help/suggestions on how to do differently (if that’s what is needed) would be appreciated.
Thank you - Julie
Actually, you can by just adding the Not() formula.
Hi @Julie_Duggins ,
I added an additional column (State 2) to your table to see if I correctly understood - and hopefully solved - your problem.
This should also simplify the overall formula.
Please, let me know if I’m in the right direction.
Cheers!
1 Like
Hi @Federico_Stefanato !
Thanks for your help. Yes, that is the right direction. I was using a switchif() because I actually have another variable to consider. I took your suggestion with the not(), but when I add that new variable, it doesn’t work right. I’m sure I’m doing something wrong. I updated the doc to show the additional variable and added it to your new column - will you please take a look. row 4 is not showing the correct result.
Why are there duplicates in here?
Minor shows up multiple times
@Connor_McCormick Sorry - I copied this from a client doc which didn’t have dupes like that. I added in more chains so it looks more like what I’m working with. But let’s say I’m working with state restrictions, so Minor, Non Minor and Non Minor only will show up multiple times, but there will be nationwide (no chain) and restricted by chain.
Hi @Julie_Duggins ,
Please, have a look at your sample doc.
I dug into it a bit deeper and I realised it might me even more simplified.
As you can see, I added direct lookup references (columns) with the Variations table, taking advantage of the relational information - one of Coda’s biggest perks.
That way, the State Formula just have to consider State Override and Exception.
Please, let me know if something is not clear.
Cheers!
@Federico_Stefanato From a user standpoint, I’m not sure I like combining the 2 fields to create one. It starts to look redundant and a little cluttered when you might be choosing 10 or so chains and they all say Minor/Non Minor, etc. I would prefer to keep them separated.
I had the same thought @Federico_Stefanato
I have an idea.
How does this look?
A good thing to note is that most of the time it’s good to have one table per noun. So in this case that would mean a variation table and a chain table which would be separate.
1 Like
Hi @Julie_Duggins ,
obviously, the perception of the solution is depending on needs and… personal tastes.
That “display column” was just a quick and easy way to achieve the actual goal: keeping the source of truth consistent and not letting the logic to be spread in different tables; i.e. your Variation table keeps the classification and Asset Schedule acts as a filter over that classification.
Also, what @Connor_McCormick suggests might be a great alternative: it really depends on your data flows (how you feed, consume and change it).
So, my suggestion is just to think if you are happy with your solution and it is consistent with your daily activity. In that case, it’s just perfect!
Cheers!
I agree on that and it’s usually a great design principle.
Although more than often I see it’s a matter of personal confidence or habit 
@Connor_McCormick I got it figured out. I just really needed to simplify my formula. Thanks so much for your help!
@Federico_Stefanato thank you for your help as well!
2 Likes
Nice work!
Could you share an explanation of your solution so that other people can benefit from it?
I basically just needed to break out the state filter (variation + chain = state) into 1 column, then put in a separate State Final column with a simple if statement that if the State Override is blank, filter the states to remove the exception states, if not use the state override. Updated the doc.
1 Like
Nice, so in this case it helped to break complex formulas into multiple simple columns and that made the issue pretty easy to solve?
Yes, I really only needed to break it out into 1 more column - filter out the states in one and then another column to override. And I’ve adjusted my original doc to utilize more of the lookup values. Working well now.
1 Like
