cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OliTFD
Frequent Visitor

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
OliTFD
Frequent Visitor

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.

View solution in original post

6 REPLIES 6
v-bofeng-msft
Community Support
Community Support

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

 

OliTFD
Frequent Visitor

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.

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

vbofengmsft_0-1644196545138.pngvbofengmsft_1-1644196561828.png

 

Best Regards,

Bof

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. 

Bildschirmfoto 2022-02-07 um 11.00.01.jpgBildschirmfoto 2022-02-07 um 10.59.39.jpgBildschirmfoto 2022-02-07 um 10.59.25.jpgBildschirmfoto 2022-02-07 um 10.58.59.jpg

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)

vbofengmsft_0-1644281322931.png

Best Regards,

Bof

 

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?

Bildschirmfoto 2022-02-08 um 09.32.12.jpg

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,071)