LinkedIn
Twitter
Hi!
This work well in some kind of status follow up.
This is a helpful tip for those that needs to filter REF values somehow.
If you have a REF column and you want to filter the values depending on the selection of some value in that REF, you can do it in a very simple way.
Ref column:
Create add an Inicial value “A” on [REF]:
Then add a virtual column with IFS and add a value to each value of the REF.
Eg:
IFS(
[REF]=“A”,“1”,
[REF]=“B”,“1”,
[REF]=“C”,“2”,
[REF]=“D”,“3”,
[REF]=“E”,“4”,
[REF]=“F”,“5”,
)
Then in the REF column in Valid If, you add the experssion filtering the values according to the results of the IFS in the virtual column.
Eg;
IFS(
[Virtual-Column]=“1”,LIST(“A”,“B”,“C”),
[Virtual-Column]=“2”,LIST(“C”,“D”),
[Virtual-Column]=“3”,LIST(“C”),
[Virtual-Column]=“4”,LIST(“E”),
[Virtual-Column]=“5”,LIST(“F”)
)
In this way you can filter the selection in relation to the IFS rule of the Virtual-Column.
I hope you find it useful.
And if you want to make sure the items of the REF wont disappear if the user deselected the last value or some other value add into Editable IF fo the [REF], making sure that the last value selected in the [REF] corresponds to the end of the logic expression from the Virtual column:
OR(
[_THIS]="",
[_THIS]=“A”,
[_THIS]=“B”,
[_THIS]=“C”,
[_THIS]=“D”,
[_THIS]=“E”
)
It makes sense enough to confirm that my guess was correct. It’s a good problem to have a documented solution for. Thank you for posting this. I’m just going to give my shot at explaining it in a different way, perhaps it will make more sense to others.
The tip here is related to consecutive edits being made to a column’s value in a single record. Those values needs to progress in a certain order. A common scenario for that would be in a [status] column, where the values must progress in this order:
- Pending
- In Progress
- Complete
To force the proper order of progression, an app editor can use valid_if to ensure that only the appropriate next values are possible, given the current value.
The usage of a Ref type column in the original post is completely unnecessary and confusing. This tip can be applied to any type of column.
Further, I see no reason that the Virtual Column needs to be used as well. We should be able to just use the [_THISROW_BEFORE] value in the column’s valid_if. Consider this expression for valid_if:
SWITCH(
[_THISROW_BEFORE].[column] ,
"A" , LIST("A","B","C") ,
"B" , LIST("A","B","C") ,
"C" , LIST("C","D") ,
"D" , LIST("C") ,
"E" , LIST("E") ,
LIST("F")
)
Then for a further tip, let’s actually bring back the fact that it could be a Ref to a value in another Table. And then use that to our advantage to make any updates to the value progression easier to manage, without messing with large expressions.
Set up your referenced table like this:
… with [next] being set as an EnumList.
Then the valid_if can simply be:
[_THISROW_BEFORE].[column].[next]
