Expression Possible Problem

[Austin]

The above formula is to prevent us from duplicating an invoice number, the first half of the expression is getting the list of all current invoices that are not being moved out of the system via Denied/Removed status. The second portion is bringing in the set of all paid invoices based on vendor ID. My test on expression assistant is telling me that it should be invalid but it is not. I don’t see anything inherently wrong with the formula but it seems to be happening elsewhere as well so I’m guessing there is?

[Steve]

Austin_Lambeth:

My test on expression assistant is telling me that it should be invalid but it is not.

What does this mean?

Austin_Lambeth:

I don’t see anything inherently wrong with the formula but it seems to be happening elsewhere

What does this mean?

[Austin]

The test in expression assistant is return False for my valid if formula on the specific row I have found where it in not setting the value to invalid

It is not isolated to this row, I forgot that I didn’t mention any specifics so that statement doesn’t make much sense.

[Austin]

It appears to be the second part of my list that is not being brought in at all.

I have the formula NOT’d because I don’t want numbers in the list so I removed that to see what it was disallowing and it is only bringing in the Current invoice numbers.

[Steve]

Consider breaking the expression into smaller parts and testing them individually to make sure each part gives the expected result.

[Austin]

AND both list individually?

[Steve]

I’d start with the first SELECT() only, noting else. Does it return the expected list?

[Austin]

Returns everything except the current row which is by design.

When testing the second portion I have nothing. I have added a virtual column that is the select statement of my IN() and the correct list shows up…

Second valid if expression tested

[Austin]

So this works as intended

Where Historical Invoice Numbers is a virtual column with the exact same formula as the select statement that was in its place before hand…

[Austin]

So far the only thing I’ve learned about the IN() statement is just try not to use it. Breaks in Slice Filters, breaks in security filters, and apparently can break in valid if statements.

[LeventK]

Wrap it with:

AND(
    TRUE,
    IN(.....)
)