cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MrDannyHarry
Resolver I
Resolver I

Patch Error Specified record was not found.

Hey!

 

I am having real trouble with this and I can't see why!

I am trying to patch through some data into a SQL database.

I've done this hundreds of times but I just cant see what I'm doing wrong this time.

This is the error I am getting when I press the button:

SQL_Error.PNG

 

 

 

 

 

 

 

 

Any help is much appreciated, this is my code I am using:

 

Set(
    ExistingQuote,
    LookUp(
        '[dbo].[quotes]',
        Q_Quote_ID.Text in quote_id,
        client_id
    )
);
If(
    IsBlank(ExistingQuote),
    ClearCollect(
        QuotePatch,
        ShowColumns(
            RenameColumns(
                AddColumns(
                    Q_QuoteItems.AllItems,
                    "price",
                    Q_QI_Price.Text,
                    "qty",
                    Q_QI_Qty.Text,
                    "discount",
                    Q_QI_Discount.Text,
                    "quote_id",
                    Q_Quote_ID.Text
                ),
                "ID",
                "item_id"
            ),
            "item_id",
            "price",
            "qty",
            "discount",
            "quote_id"
        )
    );
    Patch(
        '[dbo].[quotes]',
        Defaults('[dbo].[quotes]'),
            {
            quote_id: Q_Quote_ID.Text,
            client_id: C_ID,
            enq_id: Q_Enq_ID.Text,
            sales_personnel: Office365Users.MyProfile().UserPrincipalName
            }
    );
    ForAll(
        QuotePatch,
        Patch(
            '[dbo].[quote_items]',
            Defaults('[dbo].[quote_items]'),
            {
                item_id: item_id,
                price: price,
                qty: qty,
                discount: discount,
                quote_id: quote_id
            }
        )
    );
    Navigate(Quote_List,Fade)
);

 

 

Here are the 2 tables I am trying to patch into: 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

@Pstork1 

 

Managed to get it working! Just by reloading the APP 😤

Not sure what happened but it must of glitched somewhere.

 

Cheers,
Danny

View solution in original post

8 REPLIES 8
MrDannyHarry
Resolver I
Resolver I

The 2 Tables:

 

SQL_Quote_Items.PNG

 

SQL_Quotes.PNG

 

Pstork1
Dual Super User III
Dual Super User III

I would try switching your use of QuoteId in the ForAll Patch to Q_Quote_ID.Text.  It may be a timing issue and QuoteId may not finish populating when you try to do the ForAll Patch.  In that case it wouldn't find the parent record.  I would also suggest re-arranging your code to do the first Patch() earlier and then the ForAll Patch() later to get a little processing time on the SQL server before you try to patch.  Like this

Set(
    ExistingQuote,
    LookUp(
        '[dbo].[quotes]',
        Q_Quote_ID.Text in quote_id,
        client_id
    )
);
   Patch(
        '[dbo].[quotes]',
        Defaults('[dbo].[quotes]'),
            {
            quote_id: Q_Quote_ID.Text,
            client_id: C_ID,
            enq_id: Q_Enq_ID.Text,
            sales_personnel: Office365Users.MyProfile().UserPrincipalName
            }
    );
If(
    IsBlank(ExistingQuote),
    ClearCollect(
        QuotePatch,
        ShowColumns(
            RenameColumns(
                AddColumns(
                    Q_QuoteItems.AllItems,
                    "price",
                    Q_QI_Price.Text,
                    "qty",
                    Q_QI_Qty.Text,
                    "discount",
                    Q_QI_Discount.Text,
                    "quote_id",
                    Q_Quote_ID.Text
                ),
                "ID",
                "item_id"
            ),
            "item_id",
            "price",
            "qty",
            "discount",
            "quote_id"
        )
    );
     ForAll(
        QuotePatch,
        Patch(
            '[dbo].[quote_items]',
            Defaults('[dbo].[quote_items]'),
            {
                item_id: item_id,
                price: price,
                qty: qty,
                discount: discount,
                quote_id: quote_id
            }
        )
    );
    Navigate(Quote_List,Fade)
);


-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

@Pstork1 

 

Hey thanks for the quick reply!

I have moved the first patch above the collect as it does make sense to do that first!

 

Even if I do the Simple Patch on its own without the Collect and ForAll Patch it still has the same errors!

Really scratching my head with this one..

Pstork1
Dual Super User III
Dual Super User III

Looking more at your tables.  I noticed the Primary Key is just an Int.  I'm pretty sure that to write to a SQL database using Power Apps or Power Automate the primary key for the table has to be a SQL automatic Identity field.  I don't think you can use a simple Int there.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

@Pstork1 

 

Managed to get it working! Just by reloading the APP 😤

Not sure what happened but it must of glitched somewhere.

 

Cheers,
Danny

View solution in original post

Pstork1
Dual Super User III
Dual Super User III

Glad you got it working.  Not sure what could have been wrong.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Funny. The same happened to me, then I started troubleshooting, found a webpage where they told me Patch doesn't work with SQL, started panicking, but then I found this thread and re-started the App, now everything is working.

enicholsrrtqinc
Regular Visitor

I am having the same issue. I've got a fairly complex app and I'm writing to many tables in SQL. For one table in particular, I am getting this error. I have rebuilt the table as a completely different object in SQL and connected that, same result. I then opened a new blank app, added this table to it and it patches with no issue. For my testing, simply trying to write a GUID primary key value to a table in SQL. 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (897)