Office365Users to synch to a dataverse table

[Anonymous]

I have a customized user table in dataverse and I want to synch all the Office365Users on that table. It should add a new record if that user does not exist in my user dataverse table or update the record if it exists. How do I achieve this using PowerAutomate or PowerApp code.

[dpoggemann]

Hi @Anonymous ,

A virtual table already exists that brings in the Azure Active Directory users to the Dataverse environment.  Have you looked at this vs. trying to maintain a custom table?  

https://powerapps.microsoft.com/en-us/blog/announcing-the-aad-user-virtual-table-find-and-add-any-aad-user-to-your-records/ 

[Anonymous]

Thank for the reply @dpoggemann. I want to use my own created table so that I can have complete control. I can manually transfer the Office365Users that is showing in the gallery to my dataverse table one row at a time. This works but i am hoping to automate the whole process. Run the synch every month to update the user list.

[dpoggemann]

Hi @Anonymous 

I would expect you can do this with the https://learn.microsoft.com/en-us/connectors/office365users/ connector and build a Power Automate flow that could even look at the Office 365 users that have been updated in specific timeframe since the last time you ran.  You could also utilize Graph API as well which will provide you more options, here is a video... (https://youtu.be/cdq4BT-Gpw0).

We have utilized the Graph API successfully with a process where we were automatically creating a user in Azure Active Directory using Graph API and synching this with Dataverse.

[Anonymous]

Hi @dpoggemann,

Thank you for your suggestions.

I used the below code to perform the task, the code can add a new record if it does not exists on the table but also adding existing records instead of updating it. Can you please help to check what is wrong with this. 

Patch(Staffs,
    ForAll(
        Filter(Office365Users.SearchUser(),AccountEnabled=true, Department="Ancillary"),
        With({_rec: LookUp(Staffs, EmailAdd= Mail)},
            If(
                IsBlank(_rec),
                // Record does not exist, add new record
                {
                    StaffID: _rec.StaffID,
                    UserType: "Record no Exists"
                },
                // Record exists, update existing record
                {
                    StaffID: _rec.StaffID,
                    UserType: "Record Exists"
                }
            )
        )
    )
)

Primary Key for my table is StaffID 

[dpoggemann]

Hi @Anonymous ,

Are you trying to set the primary key or the primary name field?  You will not be able to set the primary key for the table as this will be a GUID that is automatically created.  You can set the primary name field on that table, maybe set this to the staffId and add it as an alternate key to the table (https://learn.microsoft.com/en-us/power-apps/maker/data-platform/define-alternate-keys-reference-records) 

[dpoggemann]

Hi @Anonymous ,

Did you ever get this resolved?

[Anonymous · Accepted solution]

Hi @dpoggemann ,

i resolved it using the below code. 

Patch(
    TPStaffs,
    ForAll(
        Filter(
            Office365Users.SearchUser({ top: 999}),
            AccountEnabled = true,
            Department = rgDepartment.Selected.Value
        ),
        With(
            {
                ExistingRecord: LookUp(TPStaffs, EmailAdd = Mail),
                UpdateRecord: {
                                FullName: DisplayName,
                                EmailAdd: Mail,
                                FName: GivenName,
                                LName: Surname
                                },
                
                NewRecord: {
                                FullName: DisplayName,
                                EmailAdd: Mail,
                                FName: GivenName,
                                LName: Surname,
                                UserType: "User"
                                }
                
            },
            If(IsBlank(ExistingRecord),
                NewRecord,
            // else
                Patch(TPStaffs,ExistingRecord, UpdateRecord)
            )
        )
    )//end of ForAll
);//End of Patch