cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tomirvine
Frequent Visitor

How to insert or update records in a SQL table

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:

IDTextUpdated 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:

IDTextUpdated 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):

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

 

 

4 REPLIES 4
Tomirvine
Frequent Visitor

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
Frequent Visitor

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
Frequent Visitor

bump

PaulD1
Super User
Super User

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

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 (27,166)