I'm building an app to store client information, relevant dates and submission deadlines for those clients. I want to take conditional actions based on the Deadline Date column which is a date, so instead of putting the complex logic of if Review Date is not empty AND Registration is empty AND Review Date is within 7 days OR 14 days, I thought I would create a new column called Deadline Status using a formula to select which status the client is in.
The challenge I have is, I thought of using a picklist for the Deadline Status column referencing the ClientsDeadlineStatus table but this makes the formula much harder to create and read because I believe I have to use FINDROW() to properly select the values from the picklist instead of just entering plain text in the cells. Here is the formula for the Deadline column:
Hi @Andras . Excellent questions you have here. I'll take a crack at answering them one-by-one.
Is there an easier way to do all this?
It depends. As you had mentioned, FINDROW certainly makes the expression more complicated. The good news is that you may not necessarily need to use another table with rowlinks involved, which warrants the FINDROW function. But this depends on whether you need to pull additional information from the status (Deadline Date) table (which is the real power of rowlinks and picklists). If this table is simply a column with the various statuses, without any additional columns associated with the each status, then you likely don't need the extra table. That way you simply set the variable independently with standardized statuses in the formula. Something like this: =IF([Registered Date]<>"","Registered",IF([Deadline Date]<>"",IF([Deadline Date]-7<TODAY(),"Alarm",IF([Deadline Date]-14<TODAY(),"Warning","On Time")),"Missing"))
Is there a better way to create complex, nested IF conditions?
Not really. You could perhaps break down the formula in separate columns using AND and OR functions, e.g. <> empty, < 7 days, and so on, and then basically have another column that uses IFs for TRUE and FALSE values. But it's still going to be nested IFs. The only advantage there might be that it's a bit more clearer on which value check is causing the specific status (alarm, warning, etc.)
Is it possible to have a formula choose from a picklist without FINDROW()?
No. FINDROW is designed to pull data from a picklist (rowlink).
Should I even bother using a picklist or just use a plain text value?
See response above. I'm thinking you may not need a picklist here. Picklist is beneficial if you need to pull additional data (columns) from the additional table(s). Also helpful to standardize a selection for an app user to choose from. But neither of these appear to be happening in your use case.
Anyway, hope this helps. Let us know if you have addt'l questions.
Hey @aj , thanks a lot for your detailed reply, addressing all my questions, this is exactly what I was looking for!
Makes perfect sense, I will convert the column formula to just add a text value instead of a picklist rowlink. I was thinking if I'm doing something wrong with the Picklists and FINDROW() but your explanation clarified it.