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"
}
)
)
);
