Hello all,
IN statement seems not to be working.
Any ideas?
Please show the configuration of the “DOCS” table. Thanks.
1 Like
The status column in the DOCS table works
The list is being picked up in the SUBBIES table.
Yet, checking in the SUBBIES table for either of the values in the STATUS column in the SUBBIES table, doesn’t seem to work
DOCS table:
SUBBIES table
What is the expression of the Status column in DOCS?
IFS(
TODAY()>=[EXPIRY],“EXPIRED”,
AND(TODAY()+7>=[EXPIRY],[EXPIRY]>TODAY()),“ALERT”,
AND(TODAY()+14>=[EXPIRY],[EXPIRY]>TODAY()+7),“ATTENTION”
)
The DOCS table does not influence this.
The column STATUS in SUBBIES table has two values as displayed in the Details_view: ALERT and EXPIRED.
Next two columns are supposed to check first one if ALERT is in the list in STATUS and second one if EXPIRED is in the column STATUS.
They are, yet IN() brings back FALSE
Even though I am not sure if the same applies to your case I have experienced that list dereferencing can be unstable in some expressions.
Unless you have absolute requirements to use list dereferencing expressions, I would use select or filter to check the existence of rows that fit your conditions when things don’t seem to be working correctly.
1 Like
This is the table you are retrieving the values from, so it surely does.
Also, you can’t, just by looking at the interface, determine whether they exist or if an expression output is correct, especially when dealing with lists. You can be for example generating a list of lists and you wouldn’t be able to discern it from looking at the interface. Additionally you are generating empty list items.
With IFS(), it is always a good practice not to leave the expression with undefined matches, this has been shown to cause problems in the app. So I suggest that you modify it as follows:
IFS(
TODAY() >= [EXPIRY], “EXPIRED”,
TODAY() + 7 >= [EXPIRY], “ALERT”,
TODAY() + 14 >= [EXPIRY], “ATTENTION”,
true, “OK”
)
Some statements were redundant.
2 Likes
