clearcollect addcolumns from related table

(2) Share

Report

Posted on by nick9one1

353

I have a two tables related many * many - 'ELT_Metrics' and the system table 'Users'

I'm trying to clearcollect and include the first related users email. But it wont work. Any ideas?

ClearCollect(
    colMetricSummary,
    ShowColumns(
        AddColumns(
            ELT_Metrics,
            
            'RelatedUserEmails', 

           First(Users).'Primary Email' 
        ),
        'Metric Name',
        'Domain',
        'InputCount',
        'CompletedInputCount',
        'RelatedUserEmails' 
    )
);

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

Sort by

WarrenBelz 155,242 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!
Hi @nick9one1,

When dealing with a many-to-many relationship the output is expected to be a Table, so try this
ClearCollect( colMetricSummary, ShowColumns( AddColumns( ELT_Metrics, 'RelatedUserEmails', Concat( ThisRecord.Users, 'Primary Email', "; " ) ), 'Metric Name', 'Domain', 'InputCount', 'CompletedInputCount', 'RelatedUserEmails' ) );

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like ♥
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
nick9one1 353 on at

Like (1)

Report
Copy link

Link copied!

Thanks for the reply Warren.

Unfortunately I get this error;

if it helps, this is my relationship in dataverse;

Was this reply helpful? Yes No
Suggested answer

VASANTH KUMAR BALMADI 352 on at

Like (2)

Report
Copy link

Link copied!

@nick9one1

ClearCollect(
colMetricSummary,
ShowColumns(
AddColumns(
ELT_Metrics,
"RelatedUserEmails",
First('Users (ELT_Metrics_Users)').'Primary Email'
),
'Metric Name',
'Domain',
'InputCount',
'CompletedInputCount',
'RelatedUserEmails'
)
);

Was this reply helpful? Yes No
Suggested answer

Valantis 4,819 on at

Like (2)

Report
Copy link

Link copied!

Hi @nick9one1,

Both Warren and VASANTH are on the right track but the key is finding the exact navigation property name for your many-to-many relationship.

In Dataverse many-to-many relationships, you navigate via the relationship name, not the table name directly. The relationship name in your formula needs to match what Dataverse exposes as the navigation property.

To find the correct name:

1. Open make.powerapps.com > Tables > ELT_Metrics > Relationships
2. Find the relationship to Users and note the Relationship name (it will look something like ELT_Metrics_Users or similar)
3. Use that exact name inside ThisRecord in the formula

So Warren's approach is correct in structure:

Concat(ThisRecord.'Your Relationship Name', 'Primary Email', "; ")

And VASANTH's suggestion of First('Users (ELT_Metrics_Users)') is also trying to reference the same thing.

Can you share the exact error message you got with Warren's formula and the relationship name shown in your Dataverse table settings? That will tell us exactly which navigation property name to use.

Best regards,

Valantis

✅ If this helped solve your issue, please Accept as Solution so others can find it quickly.

❤️ If it didn’t fully solve it but was still useful, please click “Yes” on “Was this reply helpful?” or leave a Like :).

🏷️ For follow-ups @Valantis.

📝 https://valantisond365.com/

💼 LinkedIn

▶️ YouTube

Was this reply helpful? Yes No
nick9one1 353 on at

Like (1)

Report
Copy link

Link copied!

Was this reply helpful? Yes No
nick9one1 353 on at

Like (1)

Report
Copy link

Link copied!

Thanks @Valantis I think that gets me a lot closer

in the relationship I see two names

ELT_Metrics_SystemUser_SystemUser and elt_metrics_systemuser

I have swapped out the concat line, with what you suggested, and replaces the table name with both of the relationship names, but neither works?
I have tried;

ELT_Metrics_SystemUser_SystemUser
elt_metrics_systemuser
cr20b_ELT_Metrics_SystemUser_SystemUser
cr20b_elt_metrics_systemuser

Was this reply helpful? Yes No
Suggested answer

Valantis 4,819 on at

Like (1)

Report
Copy link

Link copied!

Hi @nick9one1,

The reason none of the relationship name variants work is that the systemuser table is a restricted system table and canvas apps can't traverse the N:N navigation property to it directly the way they can with custom tables. This is a known limitation.

The workaround is to query the intersection table directly instead. Your intersection table is cr20b_elt_metrics_systemuser. Use that to get the related user IDs, then look up their emails.

Replace the AddColumns line with this:

ClearCollect(
colMetricSummary,
ShowColumns(
AddColumns(
ELT_Metrics,
'RelatedUserEmails',
Concat(
Filter(
cr20b_elt_metrics_systemuser,
elt_metricsid = ThisRecord.elt_metricsid
),
LookUp(Users, systemuserid = systemuserid).'Primary Email',
"; "
)
),
'Metric Name',
'Domain',
'InputCount',
'RelatedUserEmails'
)
)

The column names inside the Filter and LookUp will depend on the exact logical names in your intersection table. Open cr20b_elt_metrics_systemuser in Power Apps > Tables > Columns and check the exact names for the metrics ID and user ID columns.

Best regards,

Valantis

✅ If this helped solve your issue, please Accept as Solution so others can find it quickly.

❤️ If it didn’t fully solve it but was still useful, please click “Yes” on “Was this reply helpful?” or leave a Like :).

🏷️ For follow-ups @Valantis.

📝 https://valantisond365.com/

💼 LinkedIn

▶️ YouTube

Was this reply helpful? Yes No