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โ
)
So the value in the virtual column depends on the value in [REF], and the possible values available to select in [REF] depend on the value in the virtual column? How is this not circular?
What is a โvalue in that REFโ?
Yes.
Is not circular beacause of the expression it self in the Virtual.
The expression ends when the users enters the value โFโ from the [REF] (in the virtual expression this is kind of an else value), if you donยดt add that logical ending is going to be circular.
This makes pretty much zero sense. I have a guess as to what youโre doing here, but if that guess is true then your tip here requires a lot more explanation (well, it needs that either way). Can you describe the work/data-flow that is happening in the app here? Screenshots would help immensely, just as they do when you post a question.
I just add more info.
Does know make sense?
If you had an easier solution please share it.
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:
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]
Thanks for your explanation, it does make sense.
Question:
What is the difference by using SWITCH than IFS? The result is the same in both expression.
Thanks for your comments Marc!