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

Patch existing records and add new records

I have a collection in Powerapps for timesheet.

I get a selected week's timesheet and update exisiting rows with new values and on top of this i add new rows.

How do I patch the new values in existing records and add the new records ?

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User III
Super User III

@rahulswimmer 

If your original collection is based off of your datasource (i.e. you did a ClearCollect(yourCollection, YourDataSource))

Then it is even easier...

If you want to write back changes and new items:

Collect(yourDataSource, yourCollection)

will take care of new and existing records and updates all in one.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

7 REPLIES 7
MasterB
Advocate I
Advocate I

Hi,

 

you can update a row in a collection via a combination of Patch and Lookup . The second parameter of patch is the record for update. Like in a real table you need some kind of predicate you can uniquely identify the record for update in the DB this would be usually the PrimaryKey
So something like this:

 

Patch(
// DataSource
        coll_YourCollection, 
// Record to update
        LookUp(
            coll_YourCollection,
            pk_filed = "whatever_id_that_fits"
        ),
// Values for the cells to update 
        {
            input: "NewValue"
        }
    )

 

 

To add a new record to the Collection you will just use the Collect function to append a new row...

 

Best

Jan ala MasterB

R2Power
New Member

Something like this should do the trick, probably a little bit different if your Collection is not the same format as your table:

ForAll(YourCollection,
    If(IsBlank(LookUp(YourTable, ID=ThisRecord.ID)), Patch(ThisRecord, {....}), Patch(Defaults(YourTable), {...}))
)

 You probably need to put 

@rahulswimmer 

Like @MasterB, but also to include new records if is the case:

ForAll(your collection,

Patch(datasource, Coalesce(

LookUp(datasource, column_id = collection_column_id), 

Defaults(datasource)),{column_name1:value, column_name2:value}))

 

Hope it helps !

rahulswimmer
Helper I
Helper I

@gabibalaban  @R2Power  @MasterB  I will definitely try  that? 


How do I find out the difference between existing collection and the new collection where new rows have been added, because I need to add the new records using defaults. So how do I detect if a record in collection is newly added ?

RandyHayes
Super User III
Super User III

@rahulswimmer 

If your original collection is based off of your datasource (i.e. you did a ClearCollect(yourCollection, YourDataSource))

Then it is even easier...

If you want to write back changes and new items:

Collect(yourDataSource, yourCollection)

will take care of new and existing records and updates all in one.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

rahulswimmer
Helper I
Helper I

I was able to find a solution. It goes as follows:

// Update existing items in SharePoint
ForAll(
    Filter(Col_PatchBack, Not(IsBlank(pbID))),
    UpdateIf(
        SharePointList,
        ID = pbID,
        {
            Hours: If(IsBlank(pbHours),0,pbHours),
            Date: pbDate,
        }
    )
);

// Finally add new items to SharePoint
ForAll(
    Filter(Col_PatchBack, IsBlank(pbID)),
    Patch(
        SharePointList,
        Defaults(SharePointList),
        {
            Title: pbTitle,
            Hours: If(IsBlank(pbHours),0,pbHours),
            Date: pbDate,
        }
    )
);

You basically retrieve the ID of row for exisiting data and Patch it using UpdateIf, if ID exists it means you are updating existing item, if not its a new item.

RandyHayes
Super User III
Super User III

@rahulswimmer 

Since ForAll returns a Table, you are kind of wasting the Output of the table with that formula.

Consider the following that will execute quicker and will utilize ForAll for what it was intended for:

// Update existing items in SharePoint
Patch(SharePointList,
    ForAll(
        Filter(Col_PatchBack, !IsBlank(pbID)),
            {
             ID: pbID,                
             Hours: Coalesce(pbHours, 0),
             Date: pbDate,
            }
        )
    )
);

// Finally add new items to SharePoint
Collect(SharePointList,
    ForAll(
        Filter(Col_PatchBack, IsBlank(pbID)),
        {
            Title: pbTitle,
            Hours: Coalesce(pbHours, 0),
            Date: pbDate,
        }
    )
);
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

PA Community Call

Power Apps Community Call

Next call is happening on April 21st at 8a PST.

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors
Users online (41,372)