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

Answer 1

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