Sort columns in nested tables within a collection, create strings an sort collection

Hello together,

I am working one a solution to following issue, I just cannot figure out a working solution and hope one of you guys can help me out here:

I have a collection (based on a SharePointlist with choice values, multiple choices are used)

Collection name: CollectionOverview

It has three columns: Order, Country, City

in each column there are multiple rows with nested tables, holding the multiple choices

Order	Country	City
[Order A, Order B, Order C, Order D]	[France, USA, Spain, Italy]	[Paris, Mobil, Bosten, Clayton, Barcelona, Rom]
[Order B, Order A]	[France, Germany, Austria]	[Paris, Berlin, Graz, Vienna, Innsbruck]
[Order D]	[Switzerland]	[Bern, Genf]
[Order A, Order C, Order D]	[Spain, Netherlands, Austria]	[Madrid, Venlo, Linz, Innsbruck, Vienna]

Now I want do create a collection which has the elements of the nested table sorted alphabetically:

Order	Country	City
[Order A, Order B, Order C, Order D]	[Italy, France, Spain, USA]	[Barcelona, Bosten, Clyton, Mobil, Paris, Rom]
[Order A, Order B]	[Austria, France, Germany]	[Berlin, Graz, Innsbruck, Paris, Vienna]
[Order D]	[Switzerland]	[Bern, Genf]
[Order A, Order C, Order D]	[Austria, Netherlands, Spain]	[Innsbruck, Linz, Madrid, Venlo, Vienna]

and then I want the nested tables to be transfort to strings with line breaks.

Order	Country	City
Order A Order B Order C Order D	Italy France Spain USA	Barcelona Bosten Clyton Mobil Paris Rom
Order A Order B	Austria France Germany	Berlin Graz Innsbruck Paris Vienna
Order D	Switzerland	Bern Genf
Order A Order C Order D	Austria Netherlands Spain	Innsbruck Linz MadridVenlo Vienna

Sample Code:

 ClearCollect(CollectionOverview;
{
 Order:Table( {
 value: "Order A"
 };{
 value: "Order C"
 };{
 value: "Order B"
 };{
 value: "Order D"
 });

 Country:Table( {
 value: "France"
 };{
 value: "USA"
 };{
 value: "Spain"
 };{
 value: "Italy"
 });

 City:Table( {
 value: "Paris"
 };{
 value: "Mobil"
 };{
 value: "Bosten"
 };{
 value: "Clayton"
 };{
 value: "Barcelona"
 };{
 value: "Rom"
 })
 };
{
 Order:Table( {
 value: "Order B"
 };{
 value: "Order A"
 });

 Country:Table( {
 value: "France"
 };{
 value: "Germany"
 };{
 value: "Austria"
 });

 City:Table( {
 value: "Paris"
 };{
 value: "Berlin"
 };{
 value: "Graz"
 };{
 value: "Vienna"
 };{
 value: "Innsbruck"
 })
 };
{
 Order:Table( {
 value: "Order D"
 });

 Country:Table( {
 value: "Switzerland"
 });

 City:Table( {
 value: "Bern"
 };{
 value: "Genf"
 })
 };
{
 Order:Table( {
 value: "Order A"
 };{
 value: "Order C"
 };{
 value: "Order D"
 });

 Country:Table( {
 value: "Spain"
 };{
 value: "Netherlands"
 };{
 value: "Austria"
 });

 City:Table( {
 value: "Madrid"
 };{
 value: "Venlo"
 };{
 value: "Linz"
 };{
 value: "Innsbruck"
 };{
 value: "Vienna"
 })
 }
);;

Solution I have so far, but I am not sure if there isn't a better way ...

// Sort and concat the Order Data
ClearCollect(CollectionOverviewOrder;
 ForAll(CollectionOverview; Concat(Sort(ThisRecord.Order;value;Ascending);ThisRecord.value; "
 ")
));;

// Sort and concat the Country Data
ClearCollect(CollectionOverviewCounty;
 ForAll(CollectionOverview; Concat(Sort(ThisRecord.Country;value;Ascending);ThisRecord.value; "
 ")
));;

// Sort and concat the City Data
ClearCollect(CollectionOverviewCity;
 ForAll(CollectionOverview; Concat(Sort(ThisRecord.City;value;Ascending);ThisRecord.value; "
 ")
));;

// Combine Order, Country and City Data
ClearCollect(CollectionOverviewAll; 
 ForAll(Sequence(CountRows(CollectionOverviewOrder));
 {
 Order: Index(CollectionOverviewOrder;Value).Value;
 Country: Index(CollectionOverviewCounty;Value).Value;
 City: Index(CollectionOverviewCity;Value).Value
 }
 )
)

Categories:

General Questions