Unanswered

Many-to-Many "Table" Referencing?

(0) Share

Report

Posted on by BenKraft

I interpret that Many-to-Many relationships mean that any one record of a table could reference multiple other records of another table, and vice versa. I have two tables, "Assembly - Operators" and "Assembly - Lines" and I've made a small model-driven app to create those relationships between them (operators are assigned to 1-3 lines).

I have successfully used lookups (many to one) in Power Apps, where I can reference a single other record from another, and I expected that many to many would be similar, other than that I would instead be referencing a selection of an auto-generated table of records instead of a column I created that contains a single record.

I have had some success in extracting the line record from single operator records, in galleries where their Items are set to something like:

I can even do things like:

This made me think that 'Assembly - Lines' can be referenced like a column, where it is a table associated with that specific record that hold the related Lines.

However, I'm running into a lot more issues when trying to use this in more complex ways, for example when attempting to filter the Operators by if they are related to a Line with the name "Z-MAX":

This seems counter-intuitive to what I am able to do with 'Assembly - Lines' in other contexts. Is there a better way to accomplish this?

Categories:

Building Power Apps

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Gil0 90 on at

Like (0)

Report
Hello,

Using the 'in' operator within the filter function typically pulls matching results from Many-to-Many relationships.

Filter( 'Assembly - Operators', "Z-MAX" in 'Assembly - Lines'.Name )

If my response helped figure out the issue, please mark as resolved so it helps direct others.

1 people found this reply helpful.

Was this reply helpful? Yes No
BenKraft 9 on at

Like (0)

Report

Hi @Gil0,

Thanks for the help! Funnily enough, this is what I tried first. Delegation issues are something I try to avoid, so I've gotten into the habit of using the !IsBlank(LookUp([...])) convention. Unfortunately, I get the same error with this syntax:

2 people found this reply helpful.

Was this reply helpful? Yes No
Gil0 90 on at

Like (0)

Report

No problem! Good thinking on delegation issues.

I just noticed that LookUp is being used with ThisRecord as the reference, which pulls a single row. LookUp requires a table reference. Since an operator is related to 1-3 line items, try this instead:

Filter(
[@'Assembly - Operators'],
'Assembly - Lines'.Name = "Z-MAX"
)

It should return a table filtered with only the records related to Z-MAX.

A dynamic approach, depending on what you're trying to accomplish with this function, would be:

Filter(
[@'Assembly - Operators'],
'Assembly - Lines'.Name = ThisRecord.Name
)

Hope this helps!

Was this reply helpful? Yes No
BenKraft 9 on at

Like (0)

Report

@Gil0 I'm not sure if I follow. When would 'Assembly - Lines'.Name ever be equal to a string? Would it not, at the least, be an isolated Name column from the sub-grid 'Assembly - Lines' table from a single record of 'Assembly - Operators'?

In your second snippet, would ThisRecord not refer back to the Operator? I want the Line name tested for to be literal.

Was this reply helpful? Yes No
Ram Prakash Duraisamy 5,597 Super User 2025 Season 2 on at

Like (0)

Report
Hi @BenKraft

Can you give a try for below Code

With( { LineOperators: Filter( 'TableName', 'Assembly - Lines'.Name = "Z-MAX" ).'Assembly - Operators' }, Filter( 'Assembly - Operators', OperatorId in LineOperators ) )

Please mark as answer if my suggestion helps.
Subscribe here for More Useful videos : https://www.youtube.com/@rampprakash3991

Was this reply helpful? Yes No