Answered

Dataverse delegation for Bridge Table Filter Query

(0) Share

Report

Posted on by ThomasRConnor

I have a bridge table used to manage Many to Many relationships (DB_Lookups). The Bridge Table has 3 LookUp columns, one to People, one to Groups and One to Orders. This allows me to manage the relationships between People & Groups, Groups & Orders and Orders & People.

If I create a new Order and link it to People, the Groups column is Blank, if I Link People to Groups, the Order column is blank.

I've go a galley on an Order Page where I want to show the People linked to that order and use the LookUp table to do this and all works fine until I also have a Group associated with the Order is that record is also displayed as an entry is included in the LU_Order Column alongside the LU_Group (to make the relationship).

Filter(
    DB_Lookups,
    LU_Order.ORD_ID = var_OrderRecord.ORD_ID
)

To get around this, I tried the following...

Filter(
    DB_Lookups,
    LU_Order.ORD_ID = var_OrderRecord.Ord_ID And !IsBlank(LU_Groups)
)

But get a delegation error on the "And !IsBlank(LU_Groups)" part of the filter.

How do I get around this to only show records where the Order_ID = the current record (held in the var_OrderRecord array) and there is no entry in the LU_Group column?

Do I need a separate LookUp table to manage each many-to-many relationship?

TIA

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (1)

WarrenBelz 153,075 Most Valuable Professional on at

Like (0)

Report
Hi @ThomasRConnor ,

Try

Filter( DB_Lookups, LU_Order.ORD_ID = var_OrderRecord.Ord_ID And LU_Groups = Blank() )

Please click Accept as solution 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 giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
ThomasRConnor 62 on at

Like (0)

Report

Unfortunately this doesn't solve the issue - I still get a delegation warning under the And & LU_Groups, but the = also gives an error as they are incompatible types for comparison .

Was this reply helpful? Yes No
WarrenBelz 153,075 Most Valuable Professional on at

Like (0)

Report

@ThomasRConnor ,

I will shift this over to the Dataverse forum - I am a SharePoint user

Was this reply helpful? Yes No
Verified answer

ThomasRConnor 62 on at

Like (0)

Report
Turned out to be a simple fix / error on my part.

&& IsBlank(ThisRecord.LU_Groups))
Needed to add 'ThisRecord' to the query as it's in a gallery!!! D'oh.

Was this reply helpful? Yes No