Difference between CONTAINS() and IN()

1

I have a table called Lists with a column called Name. It’ has 3 rows containing are AAA, BBB, CCC in the Name column.

I have another table called Items. It has a column called List which is a select column based on the Lists table. It has 2 rows containing AAA, DDD and in the List column.

I created a view called Bad Data.

If I filter Bad Data on NOT(Contains(Lists.Name,List)), I get the expected results. It show only the row containing DDD which is what I want.

If I filter Bad Data on NOT(In(Lists.Name,List)), it shows ALL the data.

So, what’s the difference between Contains and In?

Thanks … Jeff

1 Like
2

Hi @Jeff_Gordon, the main difference between Contains() and In() is that the first search parameter in each function is different:

  • Contains() - Returns true if any item in the first list is found in the subsequent items.
  • In() - Returns true if the first item is found in the subsequent items.

In your example, my hunch is that the In() function tries to find the entire list object returned from Lists.Name in the List field (which won’t find a match). Then by applying the NOT condition, the filter returns all the rows that it did not find.

3 Likes
3

Being a brand new user, I had completely missed the difference between IN and CONTAINS. I thought they were synonyms. The function explanations should really refer to similar functions and bold the critical differences between them.

I’ll have to read the functions more carefully.

1 Like
4

Hi Jeff, we’re doing a pass on the help content for our formulas now, so this is timely feedback. Thank you! I think the notion of calling out related formulas and what the differences are makes a ton of sense.

4 Likes
5

Hi Alden … Before retiring, I was in IT SW development and did a lot of tech writing, QA, and user documentation in addition to SW development. If you want any help in reviewing things, I’d be glad to.

4 Likes