Filter wrapped around if statement giving delegation warning

[Anonymous]

Hi,

I want to filter my gallery dynamically. If the user is the manager they must see their region's records. If it's a local office user they must only see their office's records.

Datasource SP list.

On the items property of the gallery I wrote this function but I get a delegation warning:

Filter(Datasource, If(IsUserRE,Region =varRERegion, Office = varUserOffice))

The variables are initialized Onstart property of the app.

varRERegion returns a value from a choice column in another SP list based on a lookup of the logged in user.

How do I get rid of the delegation warning?

Thanks a lot!

Regards,

Daniel

[BCLS776]

@Anonymous,

If statements don't delegate, leading to a warning. Can you instead do something like?:

If(IsUserRE,
    Filter(Datasource, Region =varRERegion),
    Filter(Datasource, Office = varUserOffice)
)

[RandyHayes]

@Anonymous 

Please consider changing your Formula to the following:

Filter(Datasource, 
    (IsUserRE && Region = varRERegion) ||
    (!IsUserRE && office = varUserOffice)
)

Always avoid using If statements in your Items/Filter formulas as much as possible.  

Your Filter statements should only have 1 filter in them - again as much as possible.

I hope this is helpful for you.

[Anonymous]

Hi @RandyHayes ,

Many thanks once again!! I have implemented your formula and it works but I get this message:

It is is true the region is a number and the office is a text value. Is this a problem?

I did try wrapping the number in a text function but that didn't help.

Does it matter or can I ignore this warning?

Thanks again.

Regards,

Daniel

[Anonymous]

Hi @BCLS776 ,

Thanks for your help on this!

Regards,

[RandyHayes · Accepted solution]

@Anonymous 

The types between region and office should not make a difference as long as your variables you are comparing to are the same types (i.e. office and varUserOffice are numeric, etc.)

If they are you might try to change the formula slightly to force an order of operations:

Filter(Datasource, 
    (IsUserRE && (Region = varRERegion)) ||
    (!IsUserRE && (office = varUserOffice))
)

This just makes sure that the Boolean IsUserRE is not getting evaluated against Region or office.

If you still experience an error, post back a screenshot of the formula with any red or blue underlines shown.

[Anonymous]

Hi @RandyHayes ,

Thanks this is great. It narrowed down the problem to Region (number) being compared to varRERegion (text).

I wrapped the varRERegion variable in a Value() function and that seems to have solved it.

Thank you again for all your expert advice!

Regards,

Daniel

[RandyHayes]

@Anonymous 

Yes, those comparisons need to be the same types!  Glad you discovered that and it is all working now for you.

[Anonymous]

N/A