PowerApps Patch O365Users to CDS Lookup Users

[LongRiders971]

I have a combox in PowerApps that is connected to my O365Users so i can search through our users and select a name and in my CDS, i have a lookup to users field.  I am trying to patch the PowerApps O365User to CDS but i get getting an error with my formula.  

In the past, I have done the same thing with SharePoint and i had to use the below string for the patch to name.  Can someone please assist me with how i can patch the O365 user in PowerApps to the Lookup User field in CDS?  This is driving me crazy.

{Physician:'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",ODataType:Blank(),Claims:Concatenate("i:0#.f|membership|",ComboBox2.Selected.Mail),Department:"",DisplayName:ComboBox2.Selected.DisplayName,Email:ComboBox2.Selected.Mail,JobTitle:"",Picture:""})

Physician is the name of the field in CDS and ComboBox2 is my field in PowerApps.

Thanks in advance!

[EricRegnier]

Hi @LongRiders971, Are you trying to update/path the out-of-the-box user (systemuser) entity in CDS or are you trying to update another record that has a CDS lookup field to user (systemuser)? Here are some example:

//update User entity
Patch(Users, First( Filter( Users, 'User Name' = "john@contonso.onmicrosoft.com" ) ) , { 'Primary Email' : "john@contoso.com" })

//update User lookup
Patch(Accounts, Gallery1.Selected, { 'User' : First( Filter( Users, 'User Name' = "john" ) ) })

[v-siky-msft]

Hi @LongRiders971 ,

Seemly this is a reduplication of this thread. 

The data structure of lookup field in CDS is not the same with Person Column in Sharepoint list.

The lookup field in CDS can be taken as the record reference from the parent entity, so you need to patch a record of parent entity to the lookup field. Please try this:

Patch('Entity', Defaults('Entity'),{Physician:LookUp(Users, 'Primary Email'=ComboBox2.Selected.Mail)})

Hope this helps.

Sik

[LongRiders971]

Ahhh, gotcha.  

So i cannot use the O365 Data source and i have to use our 'User' entity instead.  The reason that i was trying to use the O365 data source is because when i set this to a lookup field in PowerApps, the names are alphabetically.  When i connect my 'User' entity from CDS to a lookup field in PowerApps, it is not sorted so when you click the field, random B and S names appear at the top and it doesn't look very good.

As i mentioned, i am new using CDS.. With that said, do i just need to create a view in CDS that sorts by Full Name and then on my lookup field in Powerapps, use that view on this lookup?  I will also check out the link that you included.

Thanks and my apologies for the re-post.  I looked around some and i could not find a similar post prior to submitting this.

[LongRiders971]

Ahhh, gotcha.... Thanks @v-siky-msft 

So i cannot use the O365 Data source and i have to use our 'User' entity instead.  The reason that i was trying to use the O365 data source is because when i set this to a lookup field in PowerApps, the names are alphabetically.  When i connect my 'User' entity from CDS to a lookup field in PowerApps, it is not sorted so when you click the field, random B and S names appear at the top and it doesn't look very good.

As i mentioned, i am new using CDS.. With that said, do i just need to create a view in CDS that sorts by Full Name and then on my lookup field in Powerapps, use that view on this lookup?  I will also check out the link that you included.

Thanks and my apologies for the re-post.  I looked around some and i could not find a similar post prior to submitting this.

[LongRiders971]

Hi @EricRegnier.,

I am trying to patch a record in another entity, not the system user entity.  In my PowerApp, my ComboBox is using O365 because the User Entity has the names listed out of order when i connect it to my PowerApp lookup ComboBox field.  My App is working just fine patching the User.FullName from systemuser but i don't like how it is not listed in order when someone clicks on the field.  User O365 user on the other hand has the users listed in order.

[v-siky-msft · Accepted solution]

Hi @LongRiders971 ,

It's OK to take Office365 users as the source of combo box, what I mentioned above is to save the record from User Entity to Physician field based on the mail selected from combo box by using LookUp formula as follows.

{Physician:LookUp(Users, 'Primary Email'=ComboBox2.Selected.Mail)}

If you are using Patch function to save the Physician field, Please follow this formula syntax: 

Patch('Entity', Defaults('Entity'),{Physician:LookUp(Users, 'Primary Email'=ComboBox2.Selected.Mail)})

If you are using EditForm to submit the value, please modify the Update property of Physician Data Card as follows:

LookUp(Users, 'Primary Email'=ComboBox2.Selected.Mail)

Hope this helps.

Sik

[EricRegnier · Accepted solution]

Yeah, exactly you need to update the User entity in CDS. CDS most likely orders by created date in CDS (roughly the time that the User got a license assigned). You can create a CDS custom ordered view and then pick that view in the data source or in your expression: https://powerapps.microsoft.com/en-us/blog/improved-data-source-selection-and-common-data-service-vi...

You can also filter the results afterwards with the sort function in an expression: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-sort#examples

Hope this helps....

[LongRiders971]

Thank you very much @v-siky-msft  and @EricRegnier.  Both of your answers helped me very much and i wish i could accept both as solutions.  Your help is appreciated!