How to filtering Ref values with a virtual column

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”?

2 Likes

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.

1 Like

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:

  1. Pending
  2. In Progress
  3. 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:
image
… with [next] being set as an EnumList.

Then the valid_if can simply be:

[_THISROW_BEFORE].[column].[next]
4 Likes

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.

  1. Clarity: Expressions with less stuff in them are easier to read.
  2. Performance: The first argument in SWITCH is only evaluated a single time, versus the 6 times that the same sub-expression would be evaluated when using IFS() in this case.
2 Likes

Thanks for your comments Marc!