CDS for Apps: Updating a field that is a lookup to User entity

[ArleiSilva]

Ok, here is a simple thing I cannot get right:

1. I have a custom entity (ActionItems) with a field (AssignedTo) that is a lookup to User entity.
2. In a gallery, I created a Combo Box (cmbUsers) connected to User entity.
3. I want my app to replace AssignedTo with the user selected on the Combo box

For this, I'm doing:

Patch( ActionItems, ThisItem, { AssignedTo: cmbUsers.Selected } )

This is not working. Hovering the mouse over the formula above, I get 3 errors:

• The function 'Patch' has some invalid arguments
• Invalid argument type. Expecting a Record value, but of a different schema
• Imcompatible type. The 'Access Mode' column in the data source you are updating expects a 'OptionSetValue' type and you're using a 'OptionSetValue' type

What I'm doing wrong? I've been fighting with this for quite some time now.

Appreciate any help 🙂

[v-siky-msft]

Hi @ArleiSilva ,

Could you please share more details about the formula within the Items property of the ComboBox?

On your side, I think it is not necessary to connect the ComboBox to your Users Entity, instead, you could consider connect to the LookUp field in your Custom Entity.

Set the Items property of the ComboBox to following:

Choices(Entity.LookUpFiled)                  /*  Modify here on your side */

Best Regards,

Sik

[ArleiSilva]

Hi @v-siky-msft

First of all, thank you so much for your help with this. Here are the details:

Properties for the existing Combobox control (cmbUsers, connected to User entity):
Items: Users
DisplayFields: ["fullname","title"]
SearchFields: ["fullname","title"]
SelectMultiple: false
IsSearchable: true

I created a new Combobox control (MyNewComboBox) as suggested. Properties are below:
Items: Choices(ActionItems.AssignedTo)
DisplayFields: ["fullname","title"]
SearchFields: ["fullname","title"]
SelectMultiple: false
IsSearchable: true

When I interact with this new combobox, I can see some users listed. However, the list of users is not complete. Note that we have more than 100K users, however CountRows(Choices(ActionItems.AssignedTo)) returns only 500 records.

Searching for users available in the list of 500 records works as expected. Searching for users not available in the list renders no results.

I tried with the following formula for the Items property (in an attempt to force powerapps to 'requery' as I type), but I'm getting the same results as above:
Items: Filter(Choices(ActionItems.AssignedTo), MyNewComboBox.SearchText in 'Full Name').

Once again, thank you very much for your help with this 🙂

[PowerBrian]

Hello,

As shown in the image below, the limit of records that PowerApps can display from a CDS entity can be choosen in advenced params. However, it can not exceed 2000.

The only solution I have found for a similar case of mine consists in making an API which filters the 100k records according to a PowerApps trigger, all packed in a MS Flow.

[ArleiSilva]

Hi @PowerBrian

Thank you for your comments.

Yes, I'm aware of this limitation.

However, when using the Combobox connected to User entity, this limitation is not an issue since the connected data source gets requeried as I type. But the behavior is not the same when the combobox is connected to ActionItems.AssignedTo. I'm not sure why the behavior is different when consuming data directly from User entity versus consuming the same data through AssignedTo.

Is Flow the only option available? I would like to avoid this since it will certainly slow down execution thus negatively impacting the experience.

Thank you.

[SaWu]

Hi @ArleiSilva,

Maybe your problem isn't with the ComboBox and the Lookup but rather with ThisItem from the Gallery.
As far as I remember, ThisItem from Galleries also includes the controls that you put in your template.
This allows you to write formulas that, for example, collect values from all TextBoxes in your gallery.
But the ThisItem schema won't be an exact fit to the schema of ActionItems.
And thus, it might not the right record for the second argument of Patch.
I might easily be wrong.

I am now assuming your custom entity to have a guid field named Guid, that you'll have to replace with your actual guid field name.
Can you try if the following works?

Patch(ActionItems,
    { Guid: ThisItem.Guid },
    { AssignedTo: cmbUsers.Selected }
)

Alternatively maybe

Patch(ActionItems,
    { Guid: ThisItem.Guid },
    { AssignedTo: LookUp(Users, systemuserid=cmbUsers.Selected.systemuserid) }
)

helps, using the same pattern of replacing a record that might not have the right schema with something that will have the right schema for sure.

Best of luck,
    Sa.