clearcollect addcolumns from related table

(2) Share

Report

Posted on by nick9one1

349

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 (6)

Answers (0)

Sort by

WarrenBelz 154,974 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 349 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 313 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 3,256 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 349 on at

Like (0)

Report
Copy link

Link copied!

Was this reply helpful? Yes No
nick9one1 349 on at

Like (0)

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