Checking multiple columns for values

1

Hi there,

This is probably something very simple but I’ve tried multiple approaches and things aren’t working as I’d expect. I have 2 tables, Table 1 with some reference numbers on and Table 2 that is “looking up” those references and will eventually be populated with all the reference numbers as our project progresses. To keep track of progress I wanted to add a column to Table 1 that gives a simple True or False results if it finds the reference number from Table 1 in Table 2. I found it easy enough to do when checking a single column by using this formula:

If([UMPC / RSL 30-Week Plan].[RSL Grade 1].Contains(thisRow.[E&B Reference #]), “YES”, “NO”)

However we have 8 grades an therefore 8 columns to check against that are in Table 2. My question is how do I put that in a neat formula? I feel like I’m missing something blindingly obvious, which is probable lol.

Thanks in advance!

2

Dear @Lee_Head,

For sure it should be possible, would you mind to post a copy, without sensitive data, just to make it more easy to work towards solving your question?

3

Sure thing! Here’s a copy of the doc with irrelevant / sensitive data stripped: https://coda.io/d/RSL-Copy_d1zbEoR0U5l

The far right column on the first table is the one with the formula and currently it is only looking at Grade 1 column (in second table) but I want it to check against all grades 1-8. Hope that makes sense!

4

@Lee_Head

See the far right column for one possible approach:

3 Likes
5

Wow, so easy lol. Much more elegant than the monstrosities I was trying to hack together. Never thought about “ListCombine”. Many thanks!! :pray:

1 Like
6

@Ander I looked at the new sample and see that it works, but I don’t understand how it works. I would have expected in the new formula to see “thisRow.[E&B Reference #]”, and not only “thisRow”. What am I missing here?

7

Oops - figured it partly out. E&B Reference # is the display column, and ThisRow returns the value of that field in thisRow. So I understand why this works.
But I don’t understand why thisRow.[E&B Reference #] doesn’t work - I would expect it to return the same value as thisRow by itself?

8

@joost_mineur

It’s just a data type issue.

We are dealing in row objects (thisRow), not text strings (the Display column called E&B Reference #, holds a text string, not a row object).

The single select values in the 8 columns hold row objects (the dropdown selections).

While the column E&B Reference # column holds a formula that results in a text string.

Does that make sense?

9

@joost_mineur

Just saw your other comment.:grinning:

thisRow was coded to refer to a row object.

thisRow followed by a dot and then a column name was coded to refer to a column value on a row object.

“dot notation” is an established way of accessing object properties. In our case, the object is the row, and the property is the column value on that row.

1 Like
10

Thank you for you clear answer - I do understand. in one the the formula fitness sessions was even explained that issues often relate to the object type - but I forgot to link that remark to this example

2 Likes