cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dean1
Level: Powered On

Delegation Issues with Filter() Function

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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Delegation Issues with Filter() Function

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 Smiley Happy

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

Community Support Team
Community Support Team

Re: Delegation Issues with Filter() Function

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Super User
Super User

Re: Delegation Issues with Filter() Function

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 Smiley Happy

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

Community Support Team
Community Support Team

Re: Delegation Issues with Filter() Function

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
dean1
Level: Powered On

Re: Delegation Issues with Filter() Function

Thank you both!

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 391 members 5,720 guests
Please welcome our newest community members: