Submit/PatchingAzure SQL table with Composite Key from canvas app

(1) Share

Report

Posted on by Moores35TFL

Hi everyone,

I have an Azure SQL Database with several tables, almost all of which have been set up with a Compound Primary Key. I was unable to submit a form or patch from my PowerApp.

I know that several years ago whilst looking to test using SQL I ended up creating an Identity column in the table as Primary Key which then worked.

As I may not have the option to do this on this occasion I wanted to understand if it was possible to patch to an azure SQL table that uses a Compound key, I am assuming that regardless, the submit function wont work and that it would have to be a patch.

Table Schema Example - Bold signifies Compound Primary Key

BusinessNameID - Nvarchar
EffectiveDateTime - DateTime
BusinessNameDescription
BusinessNameOwner
Approved
Active
InActiveDateTime

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (0)

Sort by

Suggested answer

Pstork1 69,556 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

You might get it to work with a composite key, but not if part of the key is Date/Time. The normal workaround is to either use a stored procedure, which you can call from a Power App, or call a Power Automate flow.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
Moores35TFL 26 on at

Like
a
(0)

Report
Copy link

Link copied!

I was able to get a patch statement to work by using a lookup to get the record, although I accidentally removed the line of code and have struggled to make it work again. I am considering having an IDENTITY integer Column added to the table, but stll wish to retain the 2 fields above (BusinessNameID - Nvarchar
EffectiveDateTime - DateTime) as the composite key - is it possible to utilise the IDENTITY Column without making it a Primary key or part of the composite key.

its main purpose is to just identify the specific record, to conduct the patch statement

Was this reply helpful? Yes No