Delegation Issues with Filter() Function

[dean1]

In Dynamics 365 CRM, there is a relationship 1:N between Opportunity and Score Card. 

My goal is to provide a Gallery that lists the full list of Score Cards based on the selected Opportunity

I have tried using Filter and Lookup, but this yield a delegation error with incorrect results: 

Filter('Score Cards',LookUp(Opportunities,opportunityid = ThisItem.Opportunity,name) = Opp.Text)

In the above example, 'Opp' refers to a field on the page, and the lookup is referencing the related Opportunity ID and returning its name.

I have also tried to filter on the GUID for the opportunity, which removes delegation errors but yields inaccurate and sometimes blank results.

Filter('Score Cards', ThisItem._parentaccountid_value = GUID(OppGuid.Text))

In the above example, OppGuid refers to the Opportunity ID. 

Any suggestions on the best way to do this?

Thank you!

[RusselThomas · Accepted solution]

Hi @dean1 

I'm a little confused as to why you have ThisItem references in your filter.  Are you running a Gallery inside a Gallery?

The ThisItem reference in your first formula seems to imply you're selecting something out of a Gallery that is currently displaying Score Card information....which is where my confusion comes in.  I assumed you would be applying a Score Card filter to another gallery based on something selected in an opportunity gallery....

Maybe a screenshot to clarify 🙂

In the meantime, let's make some assumptions.

Gallery1 Items: would be your Opportunities list

Opportunities

Gallery2 Items: would be a drilldown of all ScoreCards based on whatever Opportunity is selected in Gallery1;

If there is a many-to-one relationship defined between Scorecard and Opportunity then it should be something like;

Filter('Score Cards', ForeignKeyColumn=Gallery1.Selected.PrimaryKeyColumn)

ForeignKeyColumn is a lookup column on the Score Card side that defines this relationship.

PrimaryKeyColumn is a Unique indexed column (usually automatic) on the Opportunities that ForeignKeyColumn references.

On my side, the primary key column for opportunity is 'Opportunity id', so;

Filter('Score Cards', ForeignKeyColumn=Gallery1.Selected.'Opportunity id')

In your first example, the PrimaryKeyColumn of the Opportunity source is opportunityid, and the ForeignKeyColumn of the 'Score Cards' source is Opportunity - is this correct?  If that was the same for me, then my final formula on Gallery2 Items: would look like this;

Filter('Score Cards', Opportunity=Gallery1.Selected.'Opportunity id')

GUID's are normally globally unique references for the record which, while it might refer to a record, is not necessarily used as referencial row lookups for relationships - I could be wrong though and I can't confirm my schema as I don't have a Score Card entity I can reference.

I think if you can clarify the ThisItem reference and confirm the Primary Key and Foreign Key lookup columns we might be a bit closer to it.  Unless you're asking "What are the primary and foreign key columns" in which case I can't answer for the Score Card entity....

Kind regards,

RT

[v-xida-msft · Accepted solution]

Hi @dean1 ,

Could you please share a bit more about your scenario?

Do you add two separated Gallerys within your app to list Opportunity and Score Card records?

Based on the needs that you mentioned, I think you want to filter your Score Card records based on the selected Opportunity through the 1:N Relationship.

I agree with @RusselThomas 's thought almost. On your side, please consider modify your formula as below:

Filter('Score Cards', _parentaccountid = GUID(OppGuid.Text))  /* <-- _parentaccountid represents the column in your 'Score Cards' Entity */

Or

Filter(
       'Score Cards', 
        Opportunity = Gallery1.Selected.Opportunity
)

Note: I assume that you use Gallery1 to list all available Opportunity records within your app. The Opportunity represents the LookUp field in your 'Score Cards' Entity, which generated via the 1:N Relationship. The Opportunity represents the Primary ID within your Opportunity Entity.

Best regards,

[dean1]

Thank you both!