Answered

How to list Dataverse m:n related items in a collection?

(0) Share

Report

Posted on by OliTFD

I have a Dataverse table A, where one field is m:n related to table B. Multiple items of B can be selected for one record of A.
In a gallery of A, I can access them with Concat(ThisItem.B, B_Value) and show them together as one text line.

Is there any similar way for a collection? My target is to get a collection of A, with an added column with the values of B in one text value.
If I try something like ClearCollect(List,AddColumns(A,"B_Values",Concat(B, B_Values))) I get an error.
Any ideas?

Categories:

Building Power Apps

All responses (6)

Answers (1)

Sort by

v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @OliTFD :

I assume 'Name' is the primary key of table A,Please try:

ClearCollect( List, AddColumns( A, "B_Values", Concat( LookUp(A,Name=ThisRecord.Name).B, B_Value ) ) );

Best Regards,

Bof
Verified answer

OliTFD 21 on at

Like
a
(0)

Report
Copy link

Link copied!

Dear Bof,

thanks, but as far as I see this will not work. The two tables are m:n-related in Dataverse, with some hidden table between them. So there is no common ID to connect, and the hidden table is not directly visible in PowerApps. Even the field holding the ID related to the hidden table is not visible in Powerapps.

Anyway, I found a solution via PowerAutomate and FetchXML in the query, so there is no more direct need for me to answer this question. But if someone knows how: Maybe it can help others.
v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @OliTFD :

Thanks for your feedback, glad to hear you solved the problem.

In addition, the solution I provided is based on successful test results, in general it should work

Best Regards,

Bof
OliTFD 21 on at

Like
a
(0)

Report
Copy link

Link copied!

Dear Bof,

thanks for clearing things. I misunderstood "primary key" on the first run. As I am still interested in doing the concat right in powerapps (I found no solution to do this directly with FetchXML, so the routine in PowerAutomate became a little complicated), I tried to follow your solution with the correct tables. "A" and "B" were just placeholders.
But I can not get it to work. Do you have any idea why? I post some screenshots of the powerapp and tables, I hope you will get the idea even if they are in german.
"A" is called "Team_ZeitnachweisTageswerte",
"B" is called "ESF_Tätigkeitenliste".
The red underlinig for error starts the moment I type "Team_ZeitnachweisTageswerte" after the Lookup.
v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @OliTFD :

I noticed there are double underscores here, and I'm guessing it's a problem with the reference to tableB. Could you try re-entering TableB's name? (It is recommended to select the automatically filled item of the prompt)

Best Regards,

Bof
OliTFD 21 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Bof,
thanks for staying on this. I tried a few times only using the automatically offered items, and finally got at least the Lookup-Part to work... but now I am stuck with the last part. For the "B-Value" (primary name of B), I get only fields of table A offered, non of B.
I tried it at least 5 times, every time with the same result. Do you have any idea how to fix this?