NOT IN() or Non Intersecting

[Bcoley]

I have a table that lists the top 10 movies for each contact. For table2, I have an enum list of that person’s movie titles based on Table 1. Easy enough. However, when the user selects a title and writes a record into table 2, I would like to remove that title from the enum list as a possibility to select again. I tried putting a “USED” column table1 and using a workflow to update the value of that column to TRUE. I can then base the enum in the second table on a slice of USED=false. The only problem is that I’m using a sync delay so it doesn’t refresh until the sync happens. I was wondering whether it would work by comparing the two lists and somehow having an expression that the enum is based on which looks at what movie titles are Not in table 2 but are in Table 1 for that contact. Just don’t know how to build that syntax.

[Steve]

help.appsheet.com

List Subtraction

Subtract lists to produce another containing only the unique items of the first list that aren't also in the second.

[Bcoley]

Thanks, Steve, so much! I guess I just don’t know how and where to put this expression. I tried using the expression in the data validation for the enum : List(Table1) - List(Table2) but it didn’t seem to work.

[LeventK]

@Bcoley
I’m not aware of your table structure and how you filter those movie titles as per user, but you can try with this expression in Valid_If property of the required column in Table2.

SELECT(
	Table1[MovieTitles],
	AND(
		USEREMAIL() = [EMAIL],
		NOT(
			IN(
				[_THISROW].[MovieTitles],
				Table2[Titles]
			)
		)
	)
)

[Bcoley]

I’ll give it a try. THanks!

[LeventK]

You’re welcome