Unanswered

'in' function delegation issue with Lookup column type in Dataverse

(0) Share

Report

Posted on by DerekShalShan

Hi, I was surprised to find this issue with "in" function delegation with my Dataverse for Teams app.

My goal is to have multi-choice selections based on Lookup column relationships, using the "in" function to determine whether the Lookup value is in the relevant collection. In my example below, using "in" with the choice and text columns is OK, but with the Lookup column in the last line of filter criteria there is delegation warning that will be a problem:

Sort(
Filter(
Events,
eventDateStatus = [@eventDateStatus].'External Deadline' || !_vIsExternalDeadline,//Choice column with equality is OK
eventName in colEvTypes.evTypeName || _vAllEventTypes,//Text column in collection is OK
eventDateStatus in colDateStatus || _vAllDateStatus,// Choice column in collection is OK
evTypeLookup in colEvTypes// lookup column in collection -- Not delegable?!
),
eventDate
)

I definitely have the "Enhanced delegation" setting turned on:

I also tried GUID values as a potential workaround but still blue underlines.

Is there a workaround or am I missing some other solution?

I am thinking a workaround would be to have a text copy of the Lookup column, but this seems so inefficient I wanted to check here first for any better way, thanks! ;-D

Categories:

Microsoft Dataverse

I have the same question (0)

All responses (4)

Answers (0)

Prakash4691 1,332 on at

Like (1)

Report

Re: 'in' function delegation issue with Lookup column type in Dataverse

Hi @DerekShalShan ,

I think enhanced delegation works only for the same entity. In your case, lookup points to another table so "in" operator will be non-delegable(Even if you try with any text field).

I hope it answers your question, kindly give kudo and accept it as solution.

Regards,

Prakash

Was this reply helpful? Yes No
DerekShalShan 25 on at

Like (0)

Report

Re: 'in' function delegation issue with Lookup column type in Dataverse

Thanks, @Prakash4691 , kudos for your input, for sure! I am hoping some further exploration will result in a solution.

I have found a PowerFx calculated column in the child table can reference various fields of the Lookup record--Here is an example:

This is OK when the parent table is acting as a "Choices" table and the "evTypeName" in this case will be unique, but in other situations need to reference the unique record--But the PowerFx formula technique will not work with the GUID --See the red squiggles:

The Text function does not work in this context to convert the GUID into a text string:

But if an autonumber column is added, this can be used as a unique ID in place of the GUID:

Then, this Autonumber is delegable, no blue underlines:

HOWEVER, this requires some inefficient extra steps:
1) Creating an AUTONUMBER column in the Lookup table
2) Creating Lookup column in child table (needed regardless)
3) Creating a PowerFx calculated column in the child table that references the Lookup record's Autonumber
4) The "in" function leveraging the autonumber PowerFx column then appears delegable

Is there a better way?
Ie, perhaps to at least use the GUID instead of having to create an autonumber column?
Thanks!

Was this reply helpful? Yes No
DerekShalShan 25 on at

Like (0)

Report

Re: 'in' function delegation issue with Lookup column type in Dataverse

Also, I did find and appreciate this video from @Arslan ...
https://youtu.be/0c2dO9FPmDg

....but with Lookup column appears still not delegable:
Also, BEWARE: Using "AddColumns" to get the Lookup GUID and applying the "in" function there does not trigger a delegation warning, but there is clearly delegation in my instance when testing by setting the limit to 1.

Was this reply helpful? Yes No
Prakash4691 1,332 on at

Like (0)

Report

Re: 'in' function delegation issue with Lookup column type in Dataverse

@DerekShalShan ,

This approach will not work for you, two things here.

1. He is doing it for an option set and those values are static. In your case those are records from another table and it is dynamic.

2. He tried optionset from same table, in your case lookup points to a different table.

Regards,

Prakash

Was this reply helpful? Yes No