How to insert or update records in a SQL table

[Tomirvine]

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

[Tomirvine]

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.

[Tomirvine]

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?

[Tomirvine]

bump

[PaulD1]

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