CDS Contacts search with Lookup : Delegation issue

[TanguyT]

Hi Community,

I'm trying to implement a CDS Contacts search feature and I'm fighting with delegation issue. The app should be able to search for contacts (easy), accounts (easy) AND contacts related to found accounts (this is where the issue stands).

Here is the formula I have so far:

If(!IsBlank(Trim(TextSearchBox1.Text)); 
Concurrent(
ClearCollect(ContactsFound;Filter(Contacts; Trim(TextSearchBox1.Text) in 'Full Name' ));
ClearCollect(AccountsFound;Filter(Accounts; Trim(TextSearchBox1.Text) in 'Account Name').Account)
);;
Collect(ContactsFound;Filter(Contacts; AsType('Company Name';[@Accounts]).Account in AccountsFound))
;Blank())

The last Collect is where delegation warning is reported.

I cannot find a way to avoid PowerApps to download all contacts to evaluate the Filter. Is there a way to do this? It seems there is no way to delegate a search against CDS Lookup field type...

Anyone would have a magical trick for me?

[jlindstrom]

See this post from @PowerAddict  https://powerusers.microsoft.com/t5/Building-Power-Apps/CDS-Contacts-search-with-Lookup-Delegation-i...

One thing that can make delegatable functions non delegatable is if. 

[TanguyT]

@jlindstrom the link your gave is the link of this current thread. Can you give the appropriate link?

Thanks

[jlindstrom]

Sorry https://thepoweraddict.com/filtering-using-multiple-filters-with-delegable-functions/

[TanguyT]

Thank you @jlindstrom  but I don't think it fits my problem.

What I'm after is to understand how to filter contacts based on parentcustomerid with multiple accounts unique identifier in a delegated way.

In the end, I would like the query performed to be something like the following if I retrieved 3 accounts $filter=_parentcustomerid_value eq xxxx or _parentcustomerid_value eq yyyy or _parentcustomerid_value eq zzz

[jlindstrom]

Ok, i misread your original query.  I think the delegation issue is with in. I've had to do this one time before and I can't find the example, but I think it was something like what is in this post: https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-filter-data-based-on-a-collection-in-...

Collect(anotherCollection, Lookup(CustomerList, Customer_ID = Result))

[TanguyT]

I tried the ForAll approach but the delegation warning is just moved from "=" to "AsType" code

If(!IsBlank(Trim(TextSearchBox1.Text)); 
Concurrent(
ClearCollect(ContactsFound;Filter(Contacts; Trim(TextSearchBox1.Text) in 'Full Name' ));
ClearCollect(AccountsFound;Filter(Accounts; Trim(TextSearchBox1.Text) in 'Account Name').Account)
);;
ForAll(AccountsFound;
Collect(ContactsFound;Filter(Contacts; AsType('Company Name';[@Accounts]).Account = Account))
)
;Blank())

[v-siky-msft · Accepted solution]

Hi @TanguyT ,

'Company Name' is polymorphic lookups  field, and the AsType() function is not delegable in Filter formula.

If you change to use a common LookUp field, then the ForAll() formula will work properly without any delegation warnning.

Hope this helps.

Sik

[TanguyT]

Thank you that helps to understand but not to resolve the issue. I have to use parentcustomerid customer field. 

But I got the point and will see if I can duplicate the info in a dedicated lookup field