cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mamacona
Helper I
Helper I

Patch: Avoid creating duplicates and update the ones existing

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

5 REPLIES 5
Nogueira1306
Super User
Super User

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
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Mamacona
Helper I
Helper I

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!

 

2022-03-23 16_10_58-Power Apps.png

RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Mamacona
Helper I
Helper I

Thank you @RandyHayes ! Indeed this time worked!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,420)