Hi,
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:
=IF(
[Registered Date]<>"",
FINDROW(ClientsDeadlineStatus,"ClientsDeadlineStatus[Deadline Status]=%","Registered"),
IF(
[Deadline Date]<>"",
IF(
[Deadline Date]-7<TODAY(),
FINDROW(ClientsDeadlineStatus,"ClientsDeadlineStatus[Deadline Status]=%","Alarm"),
IF(
[Deadline Date]-14<TODAY(),
FINDROW(ClientsDeadlineStatus,"ClientsDeadlineStatus[Deadline Status]=%","Warning"),
FINDROW(ClientsDeadlineStatus,"ClientsDeadlineStatus[Deadline Status]=%","On Time")
)
),
FINDROW(ClientsDeadlineStatus,"ClientsDeadlineStatus[Deadline Status]=%","Missing")
)
)
This can be written in a single line but I split it up for easier readability. The logic is:
- if
Registered Date
is not empty, set status asRegistered
- else if
Deadline Date
is not empty:- if
Deadline Date
is within 7 days, set status asAlarm
- else if
Deadline Date
is within 14 days, set status asWarning
- else set status as
On Time
- if
- else set status as
Missing
My questions are:
- Is there an easier way to do all this?
- Is there a better way to create complex, nested IF conditions?
- Is it possible to have a formula choose from a picklist without
FINDROW()
? - Should I even bother using a picklist or just use a plain text value?
Sorry for the long post and hope at least someone finds this useful.
Thanks.