Validation with a Ref Col and implementing ORDERBY

[aijac1314]

Hi, doing some research of sorting a dropdown list and knoing that we may use the ORDERBY() in VALID_IF.

I am facing a problem here, I do have a column, which is a ref col, and initially I do implemented a VALID_IF to checking the validity of the data that input from user. And now I do need to sort the column value with date, how can I do so?

EG:

VALID_IF: [_this]<>100  <---column validation.

ORDERBY(TABLE[COL],[DATE],true)

how can I insert both of this in VALID_IF?

Your help is appreciated, thank you.

[Suvrutt_Gurjar · Accepted solution]

---

@aijac1314 wrote:

EG:

VALID_IF: [_this]<>100  <---column validation.

ORDERBY(TABLE[COL],[DATE],true)

---

It sounds that you are essentially looking for ordering the reference column by date, however you do not wish the user to enter certain values of the reference column. In that case one way will be to omit those invalid values from the choice presented to the user so that you do not have to additionally check that validity.

So your valid_if expression could be something like 

ORDERBY(SELECT(TABLE[COL], [COL]<>100), [DATE], TRUE)

You could also create a slice called say "Allowed_Values" on the table with a slice filter expression something like [COL]<>100

Then your valid_if expression can be

ORDERBY(Allowed_Values[COL], [DATE], TRUE)

If there are just one or two values that you do not wish to include, then an easier expression could be something like

ORDERBY(TABLE[COL] -LIST("100", "200"),[DATE],true)

This will ensure that the dropdown value options omit the value of 100 or whatever values you want to omit and present only valid values to the user.

[aijac1314]

Dear, thanks for the prompt reply.

I tried, but it doesn't work for me. 

My case:

1. I do create a list that already filtered with some condition
2. This (file code) dropdown column is a ref to the filtered slice.
3. I set in the valid_if to validating the option that select by user, and the validation is depend on the input from the other column. Like I scan a barcode, I will check the barcode prefix, and whenever user select the File Code drop down, valid_If will check the scanned barcode and the [File Code] prefix. It work well if I didn't add in the ORDERBY(), my validation is "LEFT([Barcode],LEN([Barcode Prefix]))=[Barcode Prefix]"
4. [Barcode Prefix] is a varry prefix code, which depend on the [File Code].[DEPT]
5. As the above setting works well, but after I add in the ORDERBY(), the column is not listed any value at all, ORDERBY(SELECT(FILE[FILE CODE],LEFT([Barcode],LEN([Barcode Prefix]))=[Barcode Prefix],[Date],TRUE)

Not sure where it went wrong.

[Suvrutt_Gurjar]

Could you update in which column's valid_if you are using this expression? And is [Barcode Prefix] is also entered by the user in the same record before scanning or validating the barcode through the [Barcode] column? does [File Code] in the [FILE] table contain the barcodes? Is [FILE CODE} key column of the "FILE" table?

In the meantime , till you respond back on the above query, could you test the following expression

ORDERBY(SELECT(FILE[FILE CODE],LEFT([FILE CODE],LEN([_THISROW].[Barcode Prefix]))=[_THISROW].[Barcode Prefix],[Date],TRUE))

Also now that you have mentioned, you are using it in barcode scanning I am unsure how ordering by date the valid_if dropdown will help. Could you elaborate the basic requirement itself?

[aijac1314]

Hi @Suvrutt_Gurjar 

Thanks for your help, I found the way to fix that issue, and big thanks to your suggestion. Thank you very much

[Suvrutt_Gurjar]

You are welcome.  Thank you very much for the update. Good to know you have got a working solution. May I request you to post your final solution. That will help anyone reading this post thread in future.