Skip to main content
venusaikumarc
venusaikumarc
February 22, 2021
Question

Write datastore entity inserting new row instead of updating

  • February 22, 2021
  • 5 replies
  • 0 views

Hello,

I have a requirement that the support team can insert new rows into the table(Using SQL insert statement) as part of application support. After that end users can modify the newly inserted row values from the application.
Somehow write-to-datastore-entity(both smart service & smart function) is inserting again a new row instead of updating the current row.

This is happening when users are trying to update the values for the newly inserted row through SQL insert statement only.


I have tried by setting the valueToStore as rule!getdetailsById(ID:1234).data. No luck, still it is inserting a new row.

Appian V19.3 on-premise

Database: MS SQL server 

Thanks.
 

5 replies

mikes0011
mikes0011
Brainy
February 22, 2021

It sounds as if you don't have a Primary Key column declared in your DB table.  Can you confirm your table configuration for us?

The Expression Guru
venusaikumarc
venusaikumarcAuthor
February 22, 2021

Hi Mike,
Thanks for your response.

Appian CDT configured with primary key auto-generated.

Database table creation script:
CREATE TABLE [dbo].[Mytable](
[Act********Id] [int] IDENTITY(1,1) NOT NULL,
.
.
.
[******Name] [varchar](50) NULL,
CONSTRAINT [PK_Mytable] PRIMARY KEY CLUSTERED
(
[Act*******Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
GO

Thanks,
Venu.

mikes0011
mikes0011
Brainy
February 22, 2021

As far as I know the AUTO_INCREMENT property may also need to be set in your primary key column.  Here's an example table creation script for a simple recent table of mine:

The Expression Guru
Terms & ConditionsAccessibility statement