Filtering a gallery based on a second level relationship - Delegation warning

[Anonymous]

Hi There, I'm still on the beginning of my learning curve for powerapps and I am fighting with these delegation warnings.  Hope someone can help me on the best practices 😉

These are my tables relationship:

Vendors 

          Purchase Orders 
                       Invoices 

I am creating a gallery to assist users on searching for invoices and one of the searching fields is the Vendor Name.

To show the vendor name on the gallery I used the lookup function and that is working fine.

Now, my problem is to apply that Vendor filter (In orange) on my gallery, when I use the fields up to the PO Level I have no problem, but when I try to use the cascaded vendor fields, I get the delegation warning.

Is there a way to fix this other than duplicating the vendor ID field on the invoice table.   (That would open a hole on my data integrity).

Thanks in advance,

Leo

[Anonymous]

@Anonymous 

What is your data source?

[Anonymous]

Sorry, forgot to mention..  It's Microsoft Dataverse. (Commom Data Service).

[Anonymous]

@Anonymous 

Apologies for the delay in getting back to you.

Unfortunately, I don't have any experience using CDS as a datasource. I'll tag a few people I know who may be able to assist you.

@RezaDorrani @PowerAddict @WarrenBelz @RandyHayes @Pstork1 

[Anonymous]

No worries, Eelman.   Thanks for your help anyway.

Just to complement my initial post, is there a way to create a query with these tables (Vendor, Purchase Order and Invoices) on Dataverse?  Exactly like we do on SQL.   This way I could use this query on my gallery and then the filter process would be very easy.

Leo

[PowerAddict]

Thanks @Anonymous for the tag!

@Anonymous you could use the AddColumns function. Here is an example: 

Filter(
    AddColumns(
        'T-Invoices',
        "VendorName",
        'T-Purchase Orders'.'T-Vendor ID'.Name
    ),
    SearchInvoices.Text in 'Invoice ID',
    SearchInvoices_status.Text in 'Invoice Status',
    VendorName = SearchInvoices_Vendor1.Selected.Name
)

Let me know if this works. 

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

Thanks!

Hardit Bhatia

Microsoft Business Applications MVP

Microsoft Certified Trainer MCT

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

[Anonymous]

Thanks Hardit.    I didn't know about that AddColumn function, it makes life easier. 😉 Awesome.

I was able to progress, but the Vendor name that we added is returning as a table and then I am not able to conclude the filter.   Anything I am missing here?

Thanks again,

Leo

[PowerAddict]

So I think the problem is that 'T-Purchase Orders' is an entity/table. Can you tell me what the data structure is? Is there a lookup field on T-Invoices table that looks up to the Purchase Orders table? If so, let's say the lookup field name is PurchaseOrder, then the expression would be:

Filter(
    AddColumns(
        'T-Invoices',
        "VendorName",
        PurchaseOrder.'T-Vendor ID'.Name
    ),
    SearchInvoices.Text in 'Invoice ID',
    SearchInvoices_status.Text in 'Invoice Status',
    VendorName = SearchInvoices_Vendor1.Selected.Name
)

Let me know if the data structure is different from what I have assumed. 

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

Thanks!

Hardit Bhatia

Microsoft Business Applications MVP

Microsoft Certified Trainer MCT

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

[Anonymous]

That's exactly like you described:   

TABLES:                      T-Invoices     -                          T-Purchase Orders       -   T- Vendors

LKP COLUMNS:          T-Purchase Order ID                T-Vendor ID                     

Target column:                                                                                                     Name

I was able to remove the error after looking into your description.

but the point now is that the Vendor Name column is returning blank.    For this next test, I am just removing the filter function and letting the Addcolumns on the item property of the gallery.      And then I am applying the new added column to the Vendor column, like below:

Thanks,

Leo

[PowerAddict]

Try this: 

Filter(
    AddColumns(
        'T-Invoices',
        "Vendor Name",
        LookUp('T-Vendors', ID = 'T-Purchase Order ID'.'T-Vendor ID'.ID, Name)
    ),
    SearchInvoices.Text in 'Invoice ID',
    SearchInvoices_status.Text in 'Invoice Status',
    VendorName = SearchInvoices_Vendor1.Selected.Name
)

Let me know if this works (ID is what I have assumed to be the GUID field for T-Vendors). If not, I have another variation that we can try. 

---
If you like this reply, please give kudos (Thumbs Up). And if this solves your problem, please mark this reply as a solution by selecting Accept as Solution. This makes it easier for others to find answers to similar questions. 

Thanks!

Hardit Bhatia

Microsoft Business Applications MVP

Microsoft Certified Trainer MCT

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email