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?

Answer 1

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).