Contains() is only matching first option on list

Hi all

I’m using the the following formula:

if(AND(Contains(thisRow.Service, "Manufacture"), Contains(thisRow.[Job Type], Laminates.[Job Type].split(","))), "Yes", "no")

thisrow.[job type] is a selection box with the choices: Vehicle Graphic, Window Graphic and Banner

Laminates.[Job Type] is a list of those same three choices: Vehicle Graphic, Window Graphic, Banner

Using the above formula, If I select Vehicle Graphic it give me the result “Yes” which is correct as its a match. If I change the selection to “Window Graphic” or “Banner” is gives me the result “No” which is WRONG. It should still be a match.

Whats wrong with my formula?

Can you be more clear with what you need your formula to do?

Please be so kind to post at least a minimalistic version of your document, with an explanation what you want to solve? :bulb:

Hey guys

Here is a sample doc.

I’ve started the doc fresh and going through one column / problem at a time.

Issue 1 is:
Job Description isn’t working for the service ‘Manufacture’ in the ‘Quote’ table
If you select Manufacture > Vehicle Graphic you should get ‘Yes’ for job description but I’m currently getting ‘No’. If you change the ‘Media’ Tables Job Type so that they are all Vehicle Graphic you will then get a ‘Yes’. For some reason, having ‘Banner’ (or any other option other than Vehicle Graphic) messes it up

Here is my doc.

As previous posters said, it’s hard to guess without an example doc. But if you want to check if there’s at least one common item in two lists (i.e. for thisRow.[Job Type] and Laminates.[Job Type].Split(',')), you should try something like this:
thisRow.[Job Type].Filter(CurrentValue.In(Laminates.[Job Type].Split(',')).Count() != 0

I don’t think that Contains(List, List) will work correctly; the documented formula is Contains(List, Item[, Item2, Item3…])

Oh wow, took me a while to understand how your doc was organized. The formulas are in “selectable options” FYI all.

My first instinct would be to reorganize the doc completely, and start with combining Design, Installation, and Media tables all into one common table. It would be much easier to just filter options from that single table rather than write lots of IFs. Even if the third table has “Job type” while others don’t.

I copied the doc and rewrote the formula for dropdown options for job description. It filters Design table by selected service and job type, and outputs available “Job description” options for that match. I guess that’s what you wanted — but if you actually want “yes” and “no”, it’s a relatively easy edit from there.

P.S. The reason why thisRow.[Job Type]=Media.[Job Type].Unique() wouldn’t work where there are more than one Job Type is this. When Unique() (or any list for that matter) returns only one element, Coda would calculate that formula as comparing two items directly. If Unique() returns multiple rows, Coda would try to compare a value with a list, which is a priori not equal. Media.[Job Type].Contains(thisRow.[Job Type]) would work, but in your case it makes sense to just use the Filter().

1 Like

Thank you thank you, thank you!!!

I’ve been playing with this for ages and after your help, it seems to be coming along.

I’ll add some more columns later tonight and i’ll post if I run into any issues.

Again, thank you.

Happy to help :slight_smile:

P.S. Here’s the solution with a combined table of all services. Look at how much simpler all formulas get, while all behavior stays the same :wink:

2 Likes

Hi @Paul_Danyliuk

Got moving along and just hit a stumbling block hopefully you can help me work out.

Under the Media table > Job Type the selection is supposed to be a multi selection column, as the media could be used for Vehicle Graphics and Window Graphics.

When I change the box to a multi select column, the formula stops working, any ideas?

Thanks

nevermind, i changed the ‘if =’ in the filter to ‘if contains’.

Updated both sections to support multi-select Job types. And yes, you’ve got it right :slight_smile:

BTW, in the section with single Services table, I had already written a formula to look up the price as well. Feel free to use as a guide.

Thank you.

I’ll take a look at that now.