Comparing lists without saving

[Ami]

I have this syntax in a valid_if ref:
SELECT(REQUESTS[EMP ID],[D1S2]=TRUE)-SELECT(SCHEDULE[D1S1],[SITE ID]=[SITE ID])

The idea is this: The user selects a value in column D1S1, then what should happen is: If the list of values in my request table has the value selected in D1S1 it removes the value from the list.

This works great only if i save first and then go back and edit the row again.

I need this to run without saving the record first.

I have the same syntax in a virtual column and it doesn’t re-evaluate as well.

Any idea would be appreciated.

Thanks

[Aleksi]

Not sure if this is relevant or if I have understood your request correctly, but at least you should use [_THISROW].[SITE ID]=[SITE ID] rather than just [SITE ID]=[SITE ID] which is actually always TRUE.

[Ami]

Hi
I’m using [_thisrow] but still doesnt help.

My valid_if for D1S1 is : SELECT(REQUESTS[EMP ID],[D1S1]=TRUE)
My valid_if for D1S2 is: SELECT(REQUESTS[EMP ID],[D1S2]=TRUE)-SELECT(SCHEDULE[D1S1],[_THISROW].[SITE ID]=[SITE ID])

I need this formula to work while i’m filling the form, so it would be dynamic.
My schedule table looks like this:

I have thought of creating a table for each value and then ref each to the parent table, however, i need my end result to be in one table.

[Aleksi]

It won’t work because you haven’t save the record yet and the SELECT() doesn’t find it. You need to use something like… SELECT(REQUESTS[EMP ID],[D1S2]=TRUE)-LIST([D1S1])