cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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 🙂

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

View solution in original post

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.

View solution in original post

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 🙂

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

View solution in original post

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.

View solution in original post

dean1
Level: Powered On

Re: Delegation Issues with Filter() Function

Thank you both!

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

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