Patch: Avoid creating duplicates and update the ones existing

[Mamacona]

Hello!

Thanks to @Nogueira1306 I could populate my Sharepoint List with Office 365 Users, however when I click again in the trigger button my Patch is creating doubles, I would like to avoid this, I need to create only the new ones and also to update the ones that already existed.

ForAll(
Filter(Office365Users.SearchUser(), AccountEnabled=true),
Patch(
'Org Chart Users',
Defaults('Org Chart Users'),
{
Title: DisplayName,
MailColumn: Mail,
JobTitleColumn: JobTitle,
DepartmentColumn: Department,
Manager: Office365Users.Manager(Id).DisplayName


}
)
)

Thank you!

 

[Nogueira1306]

So, If you use 

Patch(

DataSource,

Defaults(DataSource),

....

)

It will create a new item.

To update an item already created, do this:

Patch(

DataSource,

LookUp(DataSource, ID = ThisItem.ID),

....

)

On that lookup do a condition that match the item you want.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-patch

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,

Gonçalo Nogueira

Check my LinkedIn!

Buy me a coffee!

Check my User Group (pt-PT)!

Last Post on Community

My website!

[RandyHayes]

@Mamacona 

To start, your formula has the ForAll backward. You are trying to use it like a ForLoop in some development language - which PowerApps is not.  It is a function that returns a table of records based on your iteration table and record schema.

It is more efficient to use the function as intended and will provide better performance.

Your formula is missing the use of the primary key.  This is important for the Patch function.  If you include it, then patch will know to either create a record (if it is missing or blank) or update a record (if the key exists).  However, in your case, you are not working from an existing list of records, so a lookup will be needed.  Then, that returned record will provide the primary key (ID assumed - replace as needed).

The formula should be:

Patch('Org Chart Users',
    ForAll(Filter(Office365Users.SearchUser(), AccountEnabled=true),
        With({_rec: LookUp('Org Chart Users', MailColumn = Mail)}|,
            {
                ID: _rec.ID
                Title: DisplayName,
                MailColumn: Mail,
                JobTitleColumn: JobTitle,
                DepartmentColumn: Department,
                Manager: Office365Users.Manager(Id).DisplayName
            }
        )
    )
)

This formula will add new records that do not exist and will update records that do.

I hope this is helpful for you.

[Mamacona]

Thank you @RandyHayes and @Nogueira1306 

@RandyHayes , I see the Sharepoint List has already an internal column called ID as it does not allow me to create a new one. I was considering using the email as an ID as well.

I was thinking that I could keep the same structure as your example but there is an error with no clue to me.

Thanks for the acclaration regaring the ForAll!

[RandyHayes · Accepted solution]

@Mamacona 

There is a syntax error in the formula...sorry, my fault as it appears a random pipe character get in there.

The formula should be:

Patch('Org Chart Users',
    ForAll(Filter(Office365Users.SearchUser(), AccountEnabled=true),
        With({_rec: LookUp('Org Chart Users', MailColumn = Mail)},
            {
                ID: _rec.ID
                Title: DisplayName,
                MailColumn: Mail,
                JobTitleColumn: JobTitle,
                DepartmentColumn: Department,
                Manager: Office365Users.Manager(Id).DisplayName
            }
        )
    )
)

You are not creating the ID in these formulas (you can never create that), you are providing it in your table to the Patch function.  If patch sees an ID, it knows to look up that record and update it.  If it sees the ID as blank (which would be the case if the LookUp found no record), then it will create a record.

[Mamacona]

Thank you @RandyHayes ! Indeed this time worked!

[Anonymous]

Hi @RandyHayes,

Can you please help me with my code. It is inserting new record if record does not exists in Dataverse table but it is not updating if the record already exists. Instead, it is creating a new record with a duplicate primary key. I don't understand why it will create a duplicate entry for primary key column (StaffID)

Patch(TPStaffs,
    ForAll(
        Filter(Office365Users.SearchUser(),AccountEnabled=true, Department="Ancillary"),
        With({_rec: LookUp(TPStaffs, EmailAdd= Mail)},
            {
                    
                    StaffID: _rec.StaffID,
                    UserType: "UpdateThisRecord"
            }
        )
    )
)