Cannot combine entities from Dynamics CRM within a Gallery

[dean1]

I'm running into an issue where I cannot combine entities from Dynamics CRM within a Gallery.

Repro steps:

1. I am first aiming to show all Opportunities, and wish to use a delegatable function such as Search()to display Opportunities. Something like this works well and allows my text input “AccountSearchBox” to search on opportunity_name.

Search(Opportunities, AccountSearchBox.Text, "opportunity_name”,“sort_value")

2. Next, I would like to allow the text input “AccountSearchBox” to search account_name, not opportunity_name. Since Account and Opportunity have an N:1 relationship with account_id as the constraint, a simple lookup like so would give me its field:

LookUp(Accounts,accountid=ThisItem._parentaccountid,account_name)

Given that the third parameter of the Search() function must be a static column and cannot be calculated with a formula, my suspicion is I will need to:

1. Modify the data model (either in Dynamics or PowerApps) to load the actual Account Name on the Opportunity entity, or:
2. Identify a different function or workaround that will allow me to pass a lookup.

I would prefer to implement (b), but any advice or suggestions you have would be greatly appreciated!

[v-xida-msft]

Hi @dean1 ,

Could you please share a bit more about your scenario?

Do you want to filter your Gallery items based on account name typed within AccountSearchBox?

Based on the needs that you mentioned, I think the AddColumns function could achieve your needs. I have made a test on my side, please take a try with the following workaround:

Set the Items property of the Gallery to following:

Search(
       AddColumns(
                  Opportunities,
                  "AccountName",
                  LookUp(Accounts, accountid = _parentaccountid, account_name)
       ),
       AccountSearchBox.Text, 
       "opportunity_name”, “sort_value", "AccountName"
)

More details about AddColumns function in PowerApps, please check the following article:

AddColumns function

Best regards,

[dean1]

Thank you for the quick reply! Interestingly, while that lookup works elsewhere, I get an error:

Server Response: The right side of the Equal operator must be a constant value.

The lookup itself is used elsewhere. Is it possible I don't fully understand the functionity?

As for my scenario, yes, my goal is to have the search box (AccountSearchBox) search by account name (a lookup from Opportunity) instead of a field in the Opportunity entity.

I appreciate your help!

[v-xida-msft]

Hi @dean1 ,

Could you please share a screnshot about the error message with your formula?

Does the  _parentaccountid column represent Relationship between your Opportunities entity and Accounts Entity?

I have made a test on my sie, and don't have the issue that you mentioned. Please consider take a try to modify your formula as below:

Set the Items property of the Gallery to following:

Search(
       AddColumns(
                  Opportunities,
                  "AccountName",
                  LookUp(Accounts, accountid = Opportunities[@_parentaccountid], account_name)
       ),
       AccountSearchBox.Text, 
       "opportunity_name”, “sort_value", "AccountName"
)  

Please make sure the _parentaccountid column represent Relationship between your Opportunities entity and Accounts Entity.

The standard formula as below:

Search(
       AddColumns(
                  Opportunities,
                  "AccountName",
                  LookUp(Accounts, Account = RelationShipLookUpField.Accounts, account_name)
       ),
       AccountSearchBox.Text, 
       "opportunity_name”, “sort_value", "AccountName"
)     

Note: The RelationsShipLookUpField represents the LookUp field generated within your Opportunities entity or Accounts Entity via the relationship (N:1 or 1:N) between your Opportunities entity and Accounts Entity. The Account represents the Primary ID in your Accounts Entity.

Best regards,

[dean1]

Hello!

A screenshot of the error is below:

Your proposed solution does, in fact, solve the error! However, it introduces a delegation error based on your proposed syntax (see below).

Do you have any additional suggestions?

Thank you again for the great feedback! 

[v-xida-msft]

Hi @dean1 ,

The Delegation warning issue is not an error. It just told that the data process could not be delegated to your CDS Entity, you could only process your data locally within your app.

More details about the Delegation in PowerApps, please check the following article:

Delegation

If the amount of your CDS Entity records is not more than 2000, you could ignore this warning issue.

Note: You could change the data row limits for Non-delegable limits to 2000 (maximum value).

If you have solved your problem, please go ahead to click "Accept as Solution" to identify this thread has been solved.

Best regards,

[dean1]

Hello again,

The issue is there are well over 2000 opportunities present, and the documentation on delegation for Dynamics seems to indicate that I can offload these sorts of operations to the server.

Are you able to assist with how that would be possible?

Best,

Dean

[dean1]

Hello again!

Do you have any suggestion for avoiding the delegation error here? Since we are referencing a large list of opportunities, it would be preferred to handle this server-side. It appears the syntax should be delegatable as per the documentation.

Best,

Dean