How do I create a formula that can both update and insert multiple records from a Power Apps collection into an SQL table?
I assume using the ForAll and Patch statements, however my attempts only updates one record in the SQL destination, and neither updates multiple or inserts any rows if they didn't already exist.
PowerApps Collection:
ID | Text | Updated Date: |
101 | "Hello everyone" | 16/11/2020 7:00AM |
666 | "How is your day" | 16/11/2020 7:00AM |
999 | "Please help!" | 16/11/2020 7:00AM |
SQL Table Before:
ID | Text | Updated Date: |
101 | "Hello everyone" | 13/11/2020 11:00AM |
123 | "This is a test" | 12/11/2020 2:00PM |
666 | "It is good to meet you" | 14/11/2020 5:00PM |
Expected SQL Table After Patch (red font is the new/updated records):
ID | Text | Updated Date: |
101 | "Hello everyone" | 16/11/2020 7:00AM |
123 | "This is a test" | 12/11/2020 2:00PM |
666 | "How is your day" | 16/11/2020 7:00AM |
999 | "Please help!" | 16/11/2020 7:00AM |
Below if what I am currently trying (an IF statement) with no luck...
ForAll(
[PowerAppCollection],
If(
IsBlank(
LookUp(
'[SQLTable]',
[SQLTable].ID = [PowerAppCollection].ID
)
),
Patch(
'[SQLTable]',
Defaults('[SQLTable]'),
{
[SQLTable].ID: [PowerAppCollection].ID,
[SQLTable].Text: [PowerAppCollection]."A new record and some text"
}
),
Patch(
'[SQLTable]',
LookUp(
'[SQLTable]',
[SQLTable].ID = [PowerAppCollection].ID
),
{
[SQLTable].ID: [PowerAppCollection].ID,
[SQLTable].Text: [PowerAppCollection]."Modified this record"
}
)
)
);
I have modified my question to included the logic that is currently unsuccessful in working.
In a collection containing multiple rows to both insert and update others, only one row in the SQL destination is actually updating. No rows are being inserted either.
As a work-around, I can probably use two separate ForAll statements, one to insert records and the other to update records, but this isn't the ideal solution and probably has speed impacts?
bump
You might want to try IsEmpty or IsBlankOrError instead of IsBlank. I think I would also specify a 3rd argument in your LookUp for ID (so just the ID column is returned instead of the whole record if it exists, this should improve performance and is more likely that the IsBlankOrError will work).
PowerApps is a bit odd in how it handles tests for Empties, Blanks and really gets in a muddle over nulls vs blank strings vs empty records/collections and I believe the behaviour can even change depending on whether you have Error Level Formula Management turned on.
Not sure that is what is causing your issues here, but worth a try...
Check out new user group experience and if you are a leader please create your group
Did you miss the call?? Check out the Power Apps Community Call here!
See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.
User | Count |
---|---|
271 | |
257 | |
87 | |
39 | |
34 |
User | Count |
---|---|
347 | |
255 | |
130 | |
68 | |
48 |