cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ngct
Helper III
Helper III

Upsert records to SQL database

Hi, I am trying create a button to upsert bulk records to SQL database("POWERAPP_REASON_DEMO").

However, I still cannot find a way to do that.

Reference to this article: https://arpitmscrmhunt.blogspot.com/2020/03/powerapps-patch-upsert-record-in.html

 

I am writing a script like this but it is fail. Could you please help advise on this? Much appreciated

ngct_1-1663037912408.png

 

 

If(
    IsBlank(
        LookUp(
            POWERAPP_REASON_DEMO,
            ID = BI_ID
        )
    ),
   ForAll(
        Gallery2.AllItems,
        Patch(
            POWERAPP_REASON_DEMO,
            Defaults(POWERAPP_REASON_DEMO),
            {ID: BI_ID},
            {Reason: Dropdown_reason.Selected.Value}
        )
    ),
    ForAll(
        Gallery2.AllItems,
        Patch(
            POWERAPP_REASON_DEMO,
            {ID: BI_ID},
            {Reason: Dropdown_reason.Selected.Value}
        )
    )
)

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
EricBLott
Resolver III
Resolver III

There's actually some new functionality that simplifies upsert formulas. Try this

 

   ForAll(
        Gallery2.AllItems,
        Patch(
            POWERAPP_REASON_DEMO,
            Coalesce(LookUp(POWERAPP_REASON_DEMO,ID = BI_ID),Defaults(POWERAPP_REASON_DEMO)),
            {ID: BI_ID, Reason: Dropdown_reason.Selected.Value}
        )
    )

 

Coalesce() checks if the Lookup() function returns a blank response, if o, it uses the Defaults() function.

View solution in original post

6 REPLIES 6
EricBLott
Resolver III
Resolver III

There's actually some new functionality that simplifies upsert formulas. Try this

 

   ForAll(
        Gallery2.AllItems,
        Patch(
            POWERAPP_REASON_DEMO,
            Coalesce(LookUp(POWERAPP_REASON_DEMO,ID = BI_ID),Defaults(POWERAPP_REASON_DEMO)),
            {ID: BI_ID, Reason: Dropdown_reason.Selected.Value}
        )
    )

 

Coalesce() checks if the Lookup() function returns a blank response, if o, it uses the Defaults() function.

EricBLott
Resolver III
Resolver III

You can also use the ThisRecord record to reference gallery item values.

 

ex. ThisRecord.ID would reference the ID of the current gallery item in the ForAll() loop

Sweet tip. I just did an IF statement for this today. Gonna go try this out :). 

@EricBLott @  thanks and your script works quite well! 

However, during testing, I found that sometimes, there are error as "Field Reason is required" then it stops running

 

For my Reason dropdown column, there are default values as below, so there should always be value "--"  on this drop down list. But I believe this issue is due to the dropdown value since it works fine if the script is "Reason: "1234".

 

Do you have any ideas what induces this kind of error? thanks in advance.

ngct_1-1663054594187.png

ngct_2-1663054787711.png

 

 

 

@ngct Is the Default property of your dropdown set to "--"?

Yes, the default value of the dropdown will be "--". 

Weird though, I solve the issue using 

{
 ID: BI_ID,
 Reason: Coalesce(Dropdown_reason.Selected.Value,"-")
}

 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (1,893)