Delegation warning not going away

[ashokpershad]

I am using below code in powerapps

Value(Last(Filter(PAccs,LookUp(colRep, PTemail = EmailRep).EmailRep = PTemail)).PPlan.PNum)

Here, even though I am using ClearCollect function for collection "colRep" on onvisible property of screen but when i am using same in Lookup i am getting delegation warning in ".EmailRep" and "=" part of the formula. How to remove this warning ?

I am using Dataverse as my datasource for both PAccs and ColRep collection.

[iAm_ManCat]

Set the lookup as a variable before you filter by it and then reverse the order of your filtering, as you are looking for items in PAccs where PTEmail=

Set(gblEmailRep, LookUp(colRep, PTemail = EmailRep).EmailRep)

Value(
   Last(
      Filter(
         PAccs,
         PTemail = gblEmailRep
      )
   ).PPlan.PNum
)

[BCLS776]

My first recommendation is to move the LookUp() outside of the Filter() using a With(). Try this and let us know if it helps:

With({aLookup: LookUp(colRep, PTemail = EmailRep)},
    Value(
        Last(
            Filter(PAccs, aLookUp.EmailRep = PTemail)
        ).PPlan.PNum
    )
)

Hope that helps,

Bryan

[ashokpershad]

@iAm_ManCat 

@BCLS776 

First of all thank you so much for your replies.

I am sorry, I think I should have shared the Dataverse table along with my question above. There was reason why I was using Lookup inside Filter and you will understand once you look at below tables:

Ptest Table:

PAcc Table:

PPlan - "PN-1000" is lookup from Ptest

Repeated Table:

Hope now you understand why i was using Lookup inside filter because otherwise I won't be able to compare the EmailRep and PTemail since Email Rep is coming from colRep which is collection i am creating from "Repeated" table for onvisible property of screen.

So using below formula i want to show the number lookup to Ptest table which should be 4

Value(Last(Filter(PAccs,LookUp(colRep, PTemail = EmailRep).EmailRep = PTemail)).PPlan.PNum)

Hope I am clear this time.

[BCLS776]

For your LookUp() statement, what is PTemail? Is it a column within your collection or is it a variable within your app? If it is a column name from a different table, your LookUp() won't have the context to retrieve it.

[ashokpershad]

@BCLS776 

PTemail is column from PAcc table. 

Value(Last(Filter(PAccs,LookUp(colRep, EmailRep = PTemail).EmailRep = PTemail)).PPlan.PNum)

For me it is working fine and i am getting the answer as 4 but same time I am getting Delegation warning too.

[ashokpershad]

@BCLS776  Did you give it a Try ? I hope now its clear from images I added.

[BCLS776]

No, I haven't given this a try. I am not sure what you are trying to achieve with this Filter() within your code:

Filter(PAccs,
    LookUp(colRep, EmailRep = PTemail).EmailRep = PTemail
)

It appears to be functionally equivalent to:

Filter(PAccs,
    LookUp(colRep, EmailRep = PTemail, true)
)

Is that what you are trying to achieve?

Right now, with only a single record in PAcc, the value at the end of your code will either be 4 or blank

You said colRep was a copy of the PPlan table, and I don't understand why you need this collection rather than referencing PPlan itself? If not, then is this code not working for you?:

Value(
    Last(
        Filter(PAccs As aTable, // using an As operator to ensure scope is correct
            LookUp(PPlan, aTable.PTemail = EmailRep, true) // if the emails match, return true to include this record in the Filter()
        )
    ).PPlan.PNum // access the related value drilled down through PPlan and PNum
)

Or, am I missing the purpose you are trying to achieve?

[ashokpershad]

@BCLS776 

First of all thank you so much for coming so far on the issue I am facing. 

Just to correct you, I said colRep is copy of Repeated Table and not of PPlan table. Let's just forget colRep for time being.

Let me try to explain again:

Ptest, PAcc and Repeated are three dataverse tables. 

Ptest is table which, for example, have records of different plans P1, P2 etc..

PAcc is a customer table with details like email (PTemail), phone(Pphone) and one lookup column (PPlan) from Ptest table.

Repeated is table where new records are created as many times as needed. In this table there is one column named EmailRep. 

I want to count (PNum) the number of "EmailRep" column items from Repeated table matching "PTemail" in PAcc table which has lookup in "PPlan" column from Ptest table.

I am getting result what I want but there is delegation warning.

Value(Last(Filter(PAccs,LookUp(Repeateds, EmailRep = PTemail).EmailRep = PTemail)).PPlan.PNum)

I am going to use this number on visible property to hide one control

[BCLS776]

It looks like you are going to continue to have trouble with delegation on this one because the LookUp() inside the Filter() is referring to a column in the PAccs table, PTemail. Power Apps is forced to load the entire PAccs table to evaluate if EmailRep = PTemail for each row in the table. You have some options, though:

• If you are able, pre-filter PAccs using a delegable With() statement if you can apply other conditions that will reduce its size below the delegation limits
• Add and populate a column to your Repeateds table that includes the desired PNum value. This could be populated by a Power Automate flow triggered off of row creation, which has the added benefit of working with larger row counts

Hope that helps,

Bryan