ORDERBY on an EnumList REF column

[Jake_Naude]

Hi,

I’m having difficulty using ORDERBY on [asset_name] to alphabetically sort an EnumList of Ref Type

This is what I’m trying to do in the ValidIf of [asset_name]

ORDERBY(
UNIQUE(
SELECT(
rf_design[Source Asset ID],
AND(
[_THISROW].[select_asset_type]=[source_asset_type],
[main_asset_name] = [_THISROW].[select_cabinet_to_survey],
[lcs] = [_THISROW].[LCS]
)
)
+
SELECT(
rf_design[Destination Asset ID],
AND(
[_THISROW].[select_asset_type]=[destination_asset_type],
[main_asset_name] = [_THISROW].[select_cabinet_to_survey],
[lcs] = [_THISROW].[LCS]
)
)
)
, [asset_name],FALSE)

[Source Asset ID] & [Destination Asset ID] are virtual columns.

Any assistance would be appreciated… Don’t want to keep banging my head on this wall if sort not possible… Thanks…

[AlexM]

Don’t think you can sort an EnumList.
Can’t think of a way to do it.

I would create a table for those values and a sorted slice for it.
Then populate that column from the slice.

[Jake_Naude]

Hi @AlexM… Thanks for your suggestion…

In an earlier version @Suvrutt_Gurjar helped me to sort an EnumList Text Type column where [asset_name] was pulled as text from two columns in the same rf_design table and sorted.

However, now in the updated version of the app the virtual columns return lists of numeric keys from a different table and the label returns a readable text value for the buttons. i.e.

[Source Asset ID] (Ref)

ANY(
SELECT(
Asset[id],
AND(
[lcs]=[_THISROW].[lcs],
[asset_name]=[_THISROW].[source_asset_name]
)))

[Destination Asset ID] (Ref)

ANY(
SELECT(
Asset[id],
AND(
[lcs]=[_THISROW].[lcs],
[asset_name]=[_THISROW].[destination_asset_name]
)))

I could be wrong but it feels like the sort is still works - but it’s now sorting the numeric keys and therefore presenting the labels as unsorted…?

I had high hopes that ORDERBY() could look at the numeric lists [Source Asset ID] & [Destination Asset ID] and then sort by values held in a text column [asset_name]…

[AlexM]

He is a magician

[Steve]

Do Source Asset ID and Destination Asset ID reference the same table?

Are CAT092 and CAT141 (e.g.) asset names, or key column values?

[Jake_Naude]

Hi @Steve,

Yes…[Source Asset ID] & [Destination Asset ID] are both in the rf_design table.

They reference [ID] of the asset table and return names like CAT092 and CAT141 which are text names in [asset_name].

[Suvrutt_Gurjar]

Could you please add if you are curently using both the below expressions?

ORDERBY(
UNIQUE(
SELECT(
rf_design[Source Asset ID],
AND(
[_THISROW].[select_asset_type]=[source_asset_type],
[main_asset_name] = [_THISROW].[select_cabinet_to_survey],
[lcs] = [_THISROW].[LCS]
)
)
+
SELECT(
rf_design[Destination Asset ID],
AND(
[_THISROW].[select_asset_type]=[destination_asset_type],
[main_asset_name] = [_THISROW].[select_cabinet_to_survey],
[lcs] = [_THISROW].[LCS]
)
)
)
, [asset_name],FALSE)

as well as

ANY(
SELECT(
Asset[id],
AND(
[lcs]=[_THISROW].[lcs],
[asset_name]=[_THISROW].[source_asset_name]
)))

and

ANY(
SELECT(
Asset[id],
AND(
[lcs]=[_THISROW].[lcs],
[asset_name]=[_THISROW].[destination_asset_name]
)))

Also are columns [asset_Name] where valid_if is used and columns [Source Asset ID] & [Destination Asset ID] in the same table ,namely rf_design

Also any specific reason you have used function ANY() in the expressions of [Source Asset ID] & [Destination Asset ID] They are list type columns but using ANY() willapply just one text value to that column value?

[Jake_Naude]

Hi @Suvrutt_Gurjar,

Thanks for stepping in again… I’m busy building on something you helped me with before… I’m currently still using SORT but it broke when I started using the virtual columns that reference an ID column in another table - asset. Is ORDERBY the way forward…?

I am using all three of these expressions:

Yes, [asset_name] is where the ValidIf is being used:
survey[asset_name] EnumList which references the asset table

SORT(
UNIQUE(
SELECT(
rf_design[Source Asset ID],
AND(
[_THISROW].[select_asset_type]=[source_asset_type],
[main_asset_name] = [_THISROW].[select_cabinet_to_survey],
[lcs] = [_THISROW].[LCS]
)
)
+
SELECT(
rf_design[Destination Asset ID],
AND(
[_THISROW].[select_asset_type]=[destination_asset_type],
[main_asset_name] = [_THISROW].[select_cabinet_to_survey],
[lcs] = [_THISROW].[LCS]
)
)
)
, FALSE)

[Source Asset ID] is a virtual column in the and rf_design table

ANY(
SELECT(
Asset[id],
AND(
[lcs]=[_THISROW].[lcs],
[asset_name]=[_THISROW].[source_asset_name]
)))

[Destination Asset ID] is a virtual column in the and rf_design table

ANY(
SELECT(
Asset[id],
AND(
[lcs]=[_THISROW].[lcs],
[asset_name]=[_THISROW].[destination_asset_name]
)))

Yes, the use of ANY() in the expressions of [Source Asset ID] & [Destination Asset ID] is deliberate. I think that was done so that my UX format rules can single out the specific asset IDs:

Here’s one of those format rules for: Survey Status Format for "ON HOLD"

AND(
NOT(
[asset_type]=‘Cabinet’),
COUNT(
[related surveys])>0,
ANY(
SELECT(
survey[survey_status],
AND(
ISNOTBLANK([last_changed_on]),
[last_changed_on]=
MAX(
SELECT(
survey[last_changed_on],[asset_id]=[_THISROW-2].[id])
))))=“On Hold”)

I hope that helps…?

[Jake_Naude]

Hi @Suvrutt_Gurjar,

It’ll probably help you to know that there is also a [asset_name] column in the asset table…

[Suvrutt_Gurjar]

Thank you. Since [Source Asset ID] & [Destination Asset ID] expressionss use ANY(), they will always return a single value. Can they be converted to TEXT ?

[Suvrutt_Gurjar]

or may be ref type?

[Jake_Naude]

Hi @Suvrutt_Gurjar,

[Source Asset ID] & [Destination Asset ID] are already ref columns:

I’m not sure what impact it’ll have if we convert [Source Asset ID] & [Destination Asset ID] to TEXT but I’m happy to give it a try…?

FYI: This is survey[asset_name]

[Suvrutt_Gurjar]

Thank you. So, is current issue related to only sorting the [Asset_Name] alphabetically. Basically [Asset_Name] options are visible in the enumlist but these are not sorted after new changes, after the Asset table was introduced. Is it correct understanding.

[Jake_Naude]

Hi @Suvrutt_Gurjar,

Correct… That is it exactly…

[Suvrutt_Gurjar]

Hi @Jake_Naude,

While I may certainly suggest certain changes , I believe this functionality is somewhat long and extends over three tables. Also there are substantially large SELECT() conditions that are there in all the three expressions.

You seem to be getting Asset Name details from Asset Table to RF_Design to Survey Table with substantial conditions.

So I believe this discussion thread could go on for a longer period.

So a few pointers are based on my current understanding and discussions in this thread so far are

1. can the [Source Asset ID] & [Destination Asset ID] be simply list type IDs with their respective SELECT() statments and without wrapping by ANY().

2. You may wish to elaborate/ evaluate reasons for conditions in the Enummlist Asstet_Name Valid_if constraint after having almost same conditions in SELECT() statements for [Source Asset ID] & [Destination Asset ID]

3. In the current ORDERBY statement you have shared, the SELECT() statement is on RF_Design table, so I believe the column to ORDERBY, that is [Asset_Name] also needs to be in the RF_DESIGN Table. ORDERBY() is possible only on Keys . I believe the RF_Design table does not have [Destination Asset ID] or [Source Asset ID as key. So I believe ORDERBY() will anyway not work and ORDERBY() column is also incorrect as I believe Asset_name is not in the RF_Design table.

In summary, I believe you may need to have Valid_if condition for the Asset_name Enumlist column in Survey table based on LIST type and not reference type columns [Source Asset ID] & [Destination Asset ID] Then on this combined list of [Source Asset ID] & [Destination Asset ID] , SORT(0 could be applied as before.

Hope this helps in some way.

[Jake_Naude]

Hi @Suvrutt_Gurjar,

Thank you for the time spent on this… You have given me a great deal to consider… I will take this away, digest and get back to you once I have gotten my head around it… Many thanks…

[Jake_Naude · Accepted solution]

Hi @Suvrutt_Gurjar & @Steve,

I don’t pretend to understand any of it but the solution to sorting alphabetically by [asset_name] was something along the lines of getting “…the context of the select expression into the asset table rather than the rf_design table…”

Thanks for your help gents and thanks to @Landan_Quartemont from QREW who came up with the solution…

ORDERBY(
SELECT(
asset[id],
IN(
[id],
SORT(
UNIQUE(
SELECT(
rf_design[Source Asset ID],
AND(
[_THISROW].[select_asset_type]=[source_asset_type],
[main_asset_name] = [_THISROW].[select_cabinet_to_survey],
[lcs] = [_THISROW].[LCS]
))
+
SELECT(
rf_design[Destination Asset ID],
AND(
[_THISROW-2].[select_asset_type]=[destination_asset_type],
[main_asset_name] = [_THISROW-2].[select_cabinet_to_survey],
[lcs] = [_THISROW-2].[LCS],
NOT(
[destination_asset_type]=‘Cabinet’
)))),FALSE
)),TRUE
),[asset_name]
)

[Suvrutt_Gurjar]

Thank you for the update @Jake_Naude . Nice to know that you have got the solution.