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]

I am getting that original error (multiple levels of many-to-one) when applying for the lookup function using the Vendors table:

**The ID you referenced is my 'T-Vendor' column.

Then, I changed the lookup to get from the Purchase Order table.  But it still shows as blank.

Then, to verify if the lookup is working, I created another column on my gallery and it is working fine there.

Thanks again,

Leo

[PowerAddict]

@Anonymous interesting. I might be missing something basic. Will test it out on my side to see if I can identify the issue. In the meantime, if we can review it together, that will be awesome. If you can, please ping me hardit.bhatia@thepoweraddict.com. 

---
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

[PowerAddict · Accepted solution]

@Anonymous 

Since the above mentioned expressions weren't working to go 2 levels down, splitting the AddColumns into two helped us get past the issue. This is what worked: 

Filter(
    AddColumns(
        AddColumns(
            'T-Invoices',
            "PurchaseOrderGUID",
            'T-Purchase Order ID'.'T-Purchase Order'
        ),
        "Vendor Name",
        LookUp(
            'T-Purchase Orders',
            'T-Purchase Order' = PurchaseOrderGUID
        ).'T-Vendor ID'.Name
    ),
    SearchInvoices.Text in 'Invoice ID',
    SearchInvoices_status.Text in 'Invoice Status',
    SearchInvoices_po.Text in 'T-Purchase Order ID'.ID,
    IsBlank(SearchInvoices_Vendor1.Selected.Name) || 'Vendor Name' = SearchInvoices_Vendor1.Selected.Name
)

Let me know in case of any questions. 

---
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]

It worked like a charm, Hardit.  I am very impressed with your debug skills.  

Again, thanks for your support on my topic.

[PowerAddict]

Glad I could help! Good luck with the rest of the app! Feel free to reach out in case of any questions!

Thanks!

Hardit Bhatia

Microsoft Business Applications MVP

Microsoft Certified Trainer MCT

Blog | Twitter | LinkedIn | Facebook | YouTube  |  Email

[Anonymous]

Morning Hardit,

Yesterday, After I start my tests, I got another error on my subsequent screens and then I realized that when we added that PurchaseOrderGUID column, it actually replaced the guid column name.

After my initial screen, when I select the invoice, I save that gallery selection into a collection variable, that I use for my form:

Set(GInvoice,GalInvoices.Selected)

Then, on the saving process, I simple patch that collection back to the datasource.    (but now, powerapps is confused about the guid column, as the guid column names don’t match in both collection and datasource tables)

I tried unsuccessfully to rename.

What would you recommend in this case?

Cheers,

Leo

[PowerAddict · Accepted solution]

@Anonymous its not like the column got replaced. The problem is GalInvoices.Selected is no longer using an invoice record since we have modified the columns of the gallery's data source because of the AddColumns function. 
All you should need to do is: 

Set(GInvoice, LookUp('T-Invoices', 'T-Invoice' = GalInvoices.Selected.'T-Invoice')

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]

Understood, Hardit.

And actually make more sense to me to populate the collection direct from DS than the Gallery.

Thanks,

Leo

[calvinck00]

Hi, i have similiar problem that i have posted in this link:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Search-function-for-Lookup-Column/m-p/132801...

Can you please help me because im stuck for almost 1 week. I have already tried this solution but not working. Here's the question:

I have created 3 tables in Dataverse.

First table is "Location". (columns: IDLocation, Name)

Second table is "Fixed Assets" that has lookup column to the first table (Location Table). (columns: FixedAssetsNumber, Location)

Third table is "Opname" that has lookup column to the second table (Fixed Assets Table). (columns: IDOpname, Fixed Assets)

How can i use search function if i want to filter a gallery that use Opname Table as the datasource by value of TextInput1 that contains Location Name?

@PowerAddict @Anonymous @Anonymous 

[Anonymous]

Hi Calvin,

I think the best method would be to use Addcolumns to add the location field to your opname gallery and then use the filter on this field.

See the items property of that gallery below:

Hope that helps you.

* I added the L+ as a prefix on my tables, don't forget to edit those table names.

Cheers,

Leo Couto