I have an excel with two columns: Location and Description
The excel it's like below:
Location Description
ES10 Description for ES10
ES10-00001 Description for ES10-00001
ES10-00001-100 Description for ES10-00001-100
....
ES10-00002-120 Description for ES10-00002-120
ES10-00002-130 Description for ES10-00002-130
.....
ES10-00003-002 Description for ES10-00003-002
ES10-00003-140 Description for ES10-00003-140
.....
ES10-STORE-02 Description for ES10-STORE-02
....
ES10-OTHERS-99 Description for ES10-OTHERS-99
....
and so on
I show them using a list, and I have enabled treeview using separator '-'.
I want to group ES10-00001*, ES10-00002*,ES10-00003* under a group called 'Show More Values' . I want group 'Show More Values' to appear in the three view as the first item below 'ES10' parent node. So in my script I do below:
[Location Temp]:
LOAD
[Location],
[Description] as [Location description],
If(
Left(Upper(Trim([Location])),9) = 'ES10-0001' or
Left(Upper(Trim([Location])),9) = 'ES10-0002' or
Left(Upper(Trim([Location])),9) = 'ES10-0003',Replace([Location],'ES10-','ES10-Show More Values-'),[Location]
)
as [Location Grouped],
If(Upper(Trim([Location])) = 'ES10', 1,
If(Left(Upper(Trim([Location])),9) = 'ES10-0001', 2,
If(Left(Upper(Trim([Location])),9) = 'ES10-0002', 3,
If(Left(Upper(Trim([Location])),9) = 'ES10-0003', 4, RecNo()+5)))
)
as Temp_Order_Location
FROM
[$(QvDocumentBaseName)\location.xlsx]
(ooxml, embedded labels, table is Sheet1);
[Locations]:
NoConcatenate
LOAD *,
RecNo() as Order_Location
Resident [Location Temp]
Order By Temp_Order_Location;
DROP TABLE [Location Temp];
DROP Field Temp_Order_Location;
Then I have a document with some sheets. In the main sheet (dashboad) I have a list configured as below:
in general tab, I choose "[Location Grouped]" as field, tree view enabled and character separator '-'. Then in expressions tab, I add "[Location description]" since I want to show its description next to "[Location Grouped]" field.
Finally in list order tab, I check on "Expression", choose Ascendent, and type the below expression:
=Order_Location
In the others sheets in the document I create copies of the list in the main sheet (dashboard). They are links, same object, not different.
In the main sheet (dashboard), which contains the main list, list is ordered correctly randomly. I mean, if I set the order expression when a filter is previously applied, then list is not ordered correctly. However, If no filter is set in the document and I set the sort expression, then the list is ordered correctly, it displays "Show More Values" under parent node "ES10" as the first item in the list and If I expand "Show More Values" node, it shows correctly its childes "ES10-00001*", "ES10-00002*" and "ES10-00003*".
Also, I have problems with the rest of objects (links) in the others sheets that points to the main list. Those lists are no correctly ordered according the sort expression set in the main list in the dashboard:
=Order_Location
even "Show More Values" node item is not the first item in the parent node "ES10". What am I doing wrong?
