add column from related many*many table

(0) Share

Report

Posted on by nick9one1

336

I cant quite get this to work..

I have two tables that have a many to many relationship. I'm trying to clearcollect and create a string of names from the second table..

the second table 'Users' is the dataverse systemusers table.

e.g.

ClearCollect(
    colMetricSummary,
    AddColumns(
        ELT_Metrics,

        AssignedUsers,

        Concat(ThisItem.Users, 'Full Name', ", ")
    )
)

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

11manish 849 on at

Like (0)

Report
Copy link

Link copied!

In Dataverse, you cannot add custom columns directly to a native Many-to-Many (N:N) relationship table because it is an internal "intersect" table managed by the system. This internal table only stores the unique IDs (GUIDs) needed to link records.

If you need to store additional data—such as a "Date Joined" or "Status"—associated with that relationship, you must use a Manual Many-to-Many relationship instead

try to follow :

https://www.youtube.com/watch?v=jIJ8mfQRr0M

Was this reply helpful? Yes No
Suggested answer

Haque 1,419 on at

Like (0)

Report
Copy link

Link copied!
Hi @nick9one1,

Let's evaluate the written code:

ClearCollect( colMetricSummary, AddColumns( ELT_Metrics, "AssignedUsers", Concat(ThisItem.Users, 'Full Name', ", ") ) )

Is ThisItem inside AddColumns referring to the current record of ELT_Metrics? Instead, inside AddColumns, we should refer to the current record using the ThisRecord keyword (or just the implicit record context).

Also, since Users is a many-to-many relationship (a table of related users), we need to access the related users for each record in ELT_Metrics properly.

ClearCollect( colMetricSummary, AddColumns( ELT_Metrics, "AssignedUsers", Concat( ThisRecord.Users, 'Full Name', ", " ) ) )

Here, the key is to use ThisRecord inside AddColumns to refer to the current record of ELT_Metrics.

NOTE: If delegation is a concern, test with smaller datasets or consider server-side filtering.

I am sure some clues I tried to give. If these clues help to resolve the issue brought you by here, please don't forget to check the box Does this answer your question? At the same time, I am pretty sure you have liked the response!

Was this reply helpful? Yes No