cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leonunescouto
Helper II
Helper II

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

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.

leonunescouto_0-1611693912256.png

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.

leonunescouto_1-1611694815913.png

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

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
PowerAddict
Super User
Super User

@leonunescouto 

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

View solution in original post

PowerAddict
Super User
Super User

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

 

View solution in original post

24 REPLIES 24
Eelman
Super User
Super User

@leonunescouto 

What is your data source?

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

@leonunescouto 

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 

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

Thanks @Eelman for the tag!

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

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?

leonunescouto_0-1611763518548.png

Thanks again,

Leo

PowerAddict
Super User
Super User

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

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.

leonunescouto_0-1611767050519.png

 

 

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:

 

leonunescouto_1-1611767402513.png

 

Thanks,

Leo

 

 

 

PowerAddict
Super User
Super User

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

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,746)