Table formula to choose from a picklist

[Andras]

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 as Registered
• else if Deadline Date is not empty:
  • if Deadline Date is within 7 days, set status as Alarm
  • else if Deadline Date is within 14 days, set status as Warning
  • else set status as On Time
• 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.

[aj]

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.

[Andras]

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.

Cheers,
Andras