MS PowerApps: How to "Patch" a SQL Table with Composite Primary Key

Question

I am relatively new to MS PowerApps

I have a SQL Server Express installed on a onsite server with a Gateway for PowerApps

My SQL Server table has a composite primary key, it is defined as:

CREATE TABLE [GFX_Information].[BusinessParnterAccess]
(
    [BpAccesID] [int] IDENTITY(1,1) NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [UpdatedDate] [datetime] NOT NULL,
    [LastOperatorID] [int] NOT NULL,
    [CreateByID] [int] NOT NULL,
    [BPID] [int] NOT NULL,
    [AllowedOperatorID] [int] NOT NULL,
    [AccessFlag] [varchar](10) NULL,

    PRIMARY KEY CLUSTERED ([AllowedOperatorID] ASC, [BPID] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [GFX_Information].[BusinessParnterAccess] 
    ADD DEFAULT (GETDATE()) FOR [CreatedDate]
GO

ALTER TABLE [GFX_Information].[BusinessParnterAccess] 
    ADD DEFAULT (GETDATE()) FOR [UpdatedDate]
GO

I am trying to work out how to "Patch" a new record.

Currently, using the OnVisible event I create a variable to hold the last BpAccesID like this

UpdateContext ({varLastAccessID:First(SortByColumns('[GFX_Information].[BusinessParnterAccess]',"BpAccesID",Descending)).BpAccesID});

I am using a manual set of values for the Patch Command for testing purposes. The Patch command is

Patch('[GFX_Information].[BusinessParnterAccess]',Defaults('[GFX_Information].[BusinessParnterAccess]')
    ,{BpAccesID:varLastAccessID+1
    ,CreatedDate: Now()
     ,UpdatedDate:Now()
     ,LastOperatorID:4
    ,CreateByID:4
        ,BPID:342
  ,AllowedOperatorID:4
  ,AccessFlag:"RW" });

However, this does not throw an error I can detect nor can I see what I am missing

Can any one provide any ideas please?

I don't know what "PowerApps" is, but since `BpAccesID` column is defined as `IDENTITY`, you should not supply a value for it for the new row. The server would assign a new value for it automatically. I would try to remove this text: `BpAccesID:varLastAccessID+1 ,` from your code. — Vladimir Baranov, Oct 24 '20 at 11:26

seanb · Accepted Answer · 2020-10-24T00:49:24.190

I was reading this, and this is a suggestion is based on my knowledge of SQL Server and a quick read about Patch. It may help you, or may not (I'm sorry). And also just confirming: I'm guessing that the question is "this doesn't create a new row and I cannot see why?"

I would guess that your issue is with BPAccessId. You've set it as an identity: [BpAccesID] [int] IDENTITY(1,1) NOT NULL,

However, you explicitly insert a value into it

Patch('[GFX_Information].[BusinessParnterAccess]',Defaults('[GFX_Information].[BusinessParnterAccess]')
    ,{BpAccesID:varLastAccessID+1

Of course, you usually cannot insert into an IDENTITY column in SQL Server - you need to set IDENTIY_INSERT on (then off again after you finish). Also, as an aside, one of the reasons for IDENTITY PK columns is to always create a new row with a valid PK. How does the approach above work for concurrency e.g., two users trying to create a new row at the same time?

Anyway, some potential solutions off the top of my head. Once again, this is based off my knowledge of SQL Server only.

Alter the MS Powerapps statement to work with the IDENTITY (I'll leave this up to you) - whether the equivalent of SET IDENTITY_INSERT table ON; or otherwise
Remove the IDENTITY property from BPAccessID (e.g., leave it as a pure int)
Make the Primary Key a composite of all three columns e.g., AllowedOperatorID, BPID, BPAccessID
Make BPAccessID the Primary Key but non-clustered, and make a unique clustered index for AllowedOperatorID, BPID

For the bottom two, as BPAccessID is still an IDENTITY, you'll need to let SQL Server handle calculating the new value.

If you are not using foreign keys to this table, then the bottom two will have similar effects.

However, if there are foreign keys, then the bottom one (a non-clustered PK and clustered unique index on the other two) is probably the closest to your current setup (and is actually what I would typically do in a table structure like yours, regardless of PowerApps or other processing).

Hi thanks. ..funny I have been working on this afternoon. I switch ed to the bottom option (non clustered pk). However when not pressed for time I will test the second option of including the identity column — Ian W, Oct 24 '20 at 15:36
The fact you have a combination of other fields (in reverse order to the table) suggests to me that you know what you're talking about re the PK and indexing. At the moment, your structure enforces uniqueness on `AllowedOperatorID, BPID` (e.g., each combination of these can only appear once) which would be lost when you go to all three as PK. If you go there, you may also want to put a unique constraint on the two original fields (up to you). — seanb, Oct 24 '20 at 15:43

MS PowerApps: How to "Patch" a SQL Table with Composite Primary Key

1 Answers1