How to merge two collections into nested table?

(1) Share

Report

Posted on by OliTFD

I have a collection 1 like

Title_A	Titel_B
Value_A1	Value_B1
Value_A2	Value_B2

and a collection 2 like

Fieldname	Description	Sorting
Titel_A	Desc_for_Title_A	1
Titel_B	Desc_for_Title_B	2

Is there any way to get a merged collection like

Title_A

Titel_B

Value: Value_A1

Description: Desc_for_Title_A

Sorting: 1

Value: Value_A2

Description: Desc_for_Title_A

Sorting: 1

Value: Value_B1

Description: Desc_for_Title_B

Sorting: 2

Value: Value_B2

Description: Desc_for_Title_B

Sorting: 2

eg. a table with nested tables, where the nested table contains the original value of collection 1 and the corresponding values of collection 2?

It thing it has something to do with a ForAll on collection 1 and lookups on collection 2, but I have now idea how to get it in code ;-)
Any help would be highly appreciated!

Categories:

Using Apps Using Formulas

All responses (6)

Answers (0)

OliTFD 21 on at

Like (0)

Report

How to merge two collections into nested table?

Thanks al lot, but this returns a table of 9 rows instead of the expected 3, and all the fieldnames are packed as table in every row.
I also don´t think that the way to shape col1 is possible, because it is collected from a datasource with the original structure.

Any other ideas?
Suggested answer

abc 123 784 Super User 2025 Season 1 on at

Like (0)

Report

How to merge two collections into nested table?

ClearCollect(col1,

{ Value: "A" },

{ Value: "BBR" },

{ Value: "3" }

);

col2 remains the same.

ForAll(col1 as c1,

ForAll(col2 as c2,

Collect(col3,

{

Fieldname: col2.Fieldname,

Value: c1.Value,

Description: c2.Description,

Sorting: c2.Sorting

}

)

)

)
OliTFD 21 on at

Like (0)

Report

How to merge two collections into nested table?

Thanks! After trying a lot of ways, I think that my original question was missleading, and a real world example would make things easier.
So here is the code for the two collections that should be merged, and my expected result as "col3"... I still found no way to get this expected resulting table.
Is it possible?

ClearCollect(

col1,

{

Prognose: "A",

Abschluss: "BBR",

Note: "3"

}

);

ClearCollect(

col2,

{

Fieldname: "Prognose",

Description: "Dies ist die Beschreibung für die Prognose",

Sorting: 1

},

{

Fieldname: "Abschluss",

Description: "Hier die Beschreibung für den Abschluss",

Sorting: 2

},

{

Fieldname: "Note",

Description: "Das sagt die Note aus",

Sorting: 3

}

);

/* Expected result

col3,

{

Fieldname: "Prognose",

Value: "A",

Description: "Dies ist die Beschreibung für die Prognose",

Sorting: 1

},

{

Fieldname: "Abschluss",

Value: "BBR",

Description: "Hier die Beschreibung für den Abschluss",

Sorting: 2

},

{

Fieldname: "Note",

Value: "3",

Description: "Das sagt die Note aus",

Sorting: 3

}

*/
abc 123 784 Super User 2025 Season 1 on at

Like (0)

Report

How to merge two collections into nested table?

I only used Title, because I didn't know your actual fieldname. Use only fieldnames that you've created.
Suggested answer

OliTFD 21 on at

Like (0)

Report

How to merge two collections into nested table?

Thanks for your quick answer!
I tried to build the code with the collections now, but it does not work:

ClearCollect(col1,

{Titel_A: "Value_A1",

Titel_B: "Value_B1"},

{Titel_A: "Value_A2",

Titel_B: "Value_B2"});

ClearCollect(col2,

{Fieldname: "Titel_A",

Description: "Desc_for_Title_A",

Sorting: 1},

{Fieldname: "Titel_B",

Description: "Desc_for_Title_B",

Sorting: 2});

ForAll(col1 As c1,

ForAll(col2 As c2,

Collect(col3,

{

ParentTitle: c1.Title,

Childfieldname: c2.Fieldname,

ChildDescription: c2.Description,

ChildSorting: c2.Sorting

}

)

)

)

c1.Title is not recognized.

Is something wrong with my collections 1 and 2, or what else could be the problem?
Suggested answer

abc 123 784 Super User 2025 Season 1 on at

Like (0)

Report

How to merge two collections into nested table?

Assuming collections col1 and col2 already exist.

Something like:

ForAll(col1 As c1,

ForAll(col2 As c2,

Collect(col3,

{

ParentTitle: c1.Title,

Childfieldname: c2.Fieldname,

ChildDescription: c2.Description,

ChildSorting: c2.Sorting

}

)

)

)