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โฆ
Solved! Go to 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]
)
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.
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]โฆ
He is a magician
Do Source Asset ID and Destination Asset ID reference the same table?
Are CAT092
and CAT141
(e.g.) asset names, or key column values?
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].
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?
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โฆ?
Hi @Suvrutt_Gurjar,
Itโll probably help you to know that there is also a [asset_name] column in the asset tableโฆ
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 ?
or may be ref type?
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]
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.
Hi @Suvrutt_Gurjar,
Correctโฆ That is it exactlyโฆ
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
can the [Source Asset ID] & [Destination Asset ID] be simply list type IDs with their respective SELECT() statments and without wrapping by ANY().
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]
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.
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โฆ
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]
)
Thank you for the update @Jake_Naude . Nice to know that you have got the solution.
User | Count |
---|---|
42 | |
30 | |
25 | |
23 | |
13 |