upsert data to SQL database

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi, I am working on different work-around to do upsert to database.

Here are some I found:

Update data:

ForAll(
 Gallery2.AllItems,
 Patch(
 POWERAPP_REASON_DEMO,
 LookUp(
 POWERAPP_REASON_DEMO,
 ID = BI_ID
 ),
 {ID: BI_ID},
 {Reason: Dropdown_reason.Selected.Value}
 )
)

Insert data(which is not working because it shows error when the records already exist in database)

 ForAll(
 Gallery2.AllItems,
 Patch(
 POWERAPP_REASON_DEMO,
 Defaults(POWERAPP_REASON_DEMO),
 {ID: BI_ID},
 {Reason: Dropdown_reason.Selected.Value}
 )
 )

Please let me know if any idea to do the upsert

(if the records already exist --> update,

if the records are not there --> insert)

Categories:

Building Power Apps

All responses (4)

Answers (0)

Sort by

Akser 1,546 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

If you provide an ID, PowerApps will attempt to update the record. It's not intelligent enough to make the assumption 'hey there is no record with that ID, therefore I need to create one'.
Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Any workaround for this case? thanks
poweractivate 11,078 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@Anonymous

Does Collect work for you?
Collect - docs.microsoft.com
@Anonymous wrote:
Any workaround for this case? thanks
Creates and clears collections and adds records to any data source.
Description
Collect
The Collect function adds records to a data source. The items to be added can be:
A single value: The value is placed in the Value field of a new record. All other properties are left blank.
A record: Each named property is placed in the corresponding property of a new record. All other properties are left blank.
A table: Each record of the table is added as a separate record of the data source as described above. The table isn't added as a nested table to a record. To do this, wrap the table in a record first.
When used with a collection, additional columns will be created as needed. The columns for other data sources are fixed by the data source and new columns can't be added.

Collect - Syntax - docs.microsoft.com

Collect( DataSource, Item, ... )
DataSource – Required. The data source that you want to add data to. If it doesn't already exist, a new collection is created.
Item(s) - Required. One or more records or tables to add to the data source.

Check if it helps @Anonymous
poweractivate 11,078 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@Anonymous You can try to Collect if the ID is not there, Patch if it is there.
You'll need to check if the ID is there first, such as with an If function. If it exists then use the Patch function - and otherwise, use the Collect function.

//pseudocode If ( Record_Exists, Patch(...), Collect(...) )

See if it helps @Anonymous