Dropdown depending on previous value

[Benoit1]

Hi all,

I’m having some trouble implementing a dropdown that depends on the value that is currently saved in the field of the dropdown.

The feature I want to implement:
In a table called ‘projects’ where each row is a project, I want the dropdown for the stages to be dependent on the current stage. For example: if I am in stage 2, I want the dropdown to show:

• Stage 1
• Stage 2
• Submit for stage 3 approval

‘Projects’ table:

My (flawed) implementation:
I’ve created a table called ‘Stages’ containing all the possible stages, and the values the dropdown should show when in a certain stage.
‘Stages’ table:

I then used the following code in the ‘Valid If’ field of the stages column of the projects table (explained below):
IF(
“”=[_THISROW].[Old stage],
LIST(Stage 1),
LIST(
TEXT(SELECT(Stages[Previous],[Stage]=[_THISROW].[Old stage])),
TEXT(SELECT(Stages[Current],[Stage]=[_THISROW].[Old stage])),
TEXT(SELECT(Stages[Next],[Stage]=[_THISROW].[Old stage]))
)
)
(where the if statement accounts for the case a new project is added (through a form) and the stage should be fixed to Stage 1)
The second list is called when in the edit screen and looks up the correct previous, current and next stages in the Stages table to show in the dropdown.
Note that I refer to [Old stage] in the projects table instead of [Stage], as explained below.

The problem:
When I used [Stage] instead of [Old stage] in the Valid If statement, the values of the dropdown would change as soon as I picked a new stage (which should only happen after saving the changes). If for example I cleared the stage, the dropdown would only show 3 blank options. Also, if I am in stage 3 and click stage 2, the new options would become stage 1, stage 2 and submitted for stage 3 approval. The only way to go back to stage 3 would be to discard the edit. I solved this by creating the [Old stage] column, that holds the stage before the edit.

The only issue here is that an updated stage will update the [Stage] column in the sheet at the next sync, which will then update [Old stage] because of the formula in the sheet itself (see screenshot), which then has to be read by Appsheet again at the next sync. As a result the ‘Stage’ column is not visible when I reopen the edit in the first 10 to 15 seconds after saving a new stage.

My question:
Is there a better approach that allows me to keep the same functionality while eliminating the delay on reopening the edit screen?

Thank you very much for your help!

Kind regards,
Benoit

[Benoit1 · Accepted solution]

I solved the issue by replacing [_THISROW].[Old stage] (which gives the currently selected value in the dropdown) with LOOKUP([_THISROW].[Label],“Initiatives”,“Label”,“Stage”) (which gives the value actually written in the table).

The new Valid If statement becomes:
IF(
NOT(IN([Label],Initiatives[Label])),
LIST(Stage 1),
LIST(
TEXT(SELECT(Stages[Previous],[Stage]=LOOKUP([_THISROW].[Label],“Initiatives”,“Label”,“Stage”))),
TEXT(SELECT(Stages[Current],[Stage]=LOOKUP([_THISROW].[Label],“Initiatives”,“Label”,“Stage”))),
TEXT(SELECT(Stages[Next],[Stage]=LOOKUP([_THISROW].[Label],“Initiatives”,“Label”,“Stage”)))
)
)

Works like a charm!