PowerApps: How to Patch multiple SQL tables with foreign keys?

Question

I get a SQL INSERT error when trying to use the Patch function in PowerApps on a table, which has a foreign key that relies on the primary key of a second table that hasn't been Patched yet.

Which makes sense. How could I be allowed Patch a table with a blank dependency? So how can it be done?

Here are the FK/PK dependencies of all 5 tables:

So far I've tried:

Allow NULL on the FK column
Removed CASCADE from FK UPDATE and DELETE

Any further ideas? I specifically need example Functions. Thank you

score 2 · Accepted Answer · answered Oct 12 '18 at 13:06

2

The Patch function will return the updated (or inserted) object with any fields from the server filled out, so you can use store it and use later to retrieve the server-generated id. Using Last will work for most of the time, but may fail if you have two users in the app at the same time, or if the table starts getting too big (and not all of it will be cached locally at once).

Set(
    patchResult,
    Patch(
        '[dbo].[dateTable]',
        Defaults('[dbo].[dateTable]'),
        {
            siteId: varSiteID,
            readingDate: Now()
        }));

//Patch values into readingTable

Patch(
    '[dbo].[readingTable]',
    Defaults('[dbo].[readingTable]'),
    {
        dateId: patchResult.dateId,
        unitNum: 1, 
        xzyName: 1,
        avgJJk: 1,
        prevLLk: 1,
        readingNotes: "This is awesome"
    }
);

answered Oct 12 '18 at 13:06

carlosfigueira

85,035
14
131
171

Thanks @carlosfigueira for the great insights. I hadn't thought of the effects when two users are in the app at once. When you say "...or if the table gets too big." are you referring to delegation issues when using Last()? – SeaDude Oct 21 '18 at 22:42
Would this pattern continue for more than 1 "cascading" Patch? Example: If I had another table relying on the PK of readingTable, would I save the readingTable Patch as a variable and call it in a similar function? – SeaDude Oct 21 '18 at 23:00
1

For the first question - yes, `Last` is not delegated yet, so you may hit into issues with that. For the second one - yes again, if you have a third table with a FK to `readingTable`, then you can store the result of the insertion into it to use it later. – carlosfigueira Oct 22 '18 at 13:38

score -1 · Answer 2 · answered Oct 12 '18 at 06:14

Just figured this one out:

You have to Patch these in order so that the PK's are Patched first, then grabbed via the Last() function and inserted (as FK's) into following Patches.

Hope this helps someone else out.

Example:

//Patch values into dateTable

Patch('[dbo].[dateTable]',
    Defaults('[dbo].[dateTable]'),
        {
            siteId: varSiteID,
            readingDate: Now()
        }
);

//Patch values into readingTable

Patch('[dbo].[readingTable]',
    Defaults(
        '[dbo].[readingTable]'),
        {
            dateId: Last('[dbo].[dateTable]').dateId, <--BINGO
            unitNum: 1, 
            xzyName: 1,
            zyxNum: 1,
            xkdFactor: 1, 
            supplyXya: 1, 
            supplyUio: 1, 
            sortNum: 1,
            currentUys: 1,
            avgJJk: 1,
            prevLLk: 1,
            readingNotes: "This is awesome"
        }
);

//Patch values into the imageTable

ForAll(
    colImageGallery,
    Patch(
        '[dbo].[imageTable]',
            Defaults('[dbo].[imageTable]'),
        {
            readingId: Last('[dbo].[readingTable]').readingId, <--BINGO
            photo: image,
            photoNotes: " "
        }
    )
);

Please do not use Last(), see the post below by [carlosfiguera](https://stackoverflow.com/users/751090/carlosfigueira) — Meneghino, Oct 18 '18 at 06:32
Duly noted, thank you @Meneghino. I'll leave my incorrect reply up there so people can compare. Take care! — SeaDude, Oct 21 '18 at 22:49

PowerApps: How to Patch multiple SQL tables with foreign keys?

2 Answers2