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.
Discussion posts and replies are publicly visible
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?
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.
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:
I wonder if for some reason the increment counter is not getting updated in the database. Do you know what SQL is being executed for the insert statement? Is that insert statement using auto-generation for the PK or is it just inserting a row with an ID provided?
In MS SQL server IDENTITY(1,1) is the auto increment configuration.It is behaving differently for updating the rows inserted through Insert statement(Here DB generated the new PK value) we are not seeding the PK values.Example: I have inserted a row using insert statement and DB generated the PK ID as 1880.The user is able to see the new values for 1880 in the Appian UI screen and changed one of the column value A->B and then hits the save button. Here the system is creating a new row 1881 instead of updating 1880.The same process is working fine and updating the row successfully if the updating row is inserted through application.Example: User submitted a request from Appian application and new PK ID created in database say 1756.User edit the details using related action feature and changed one of the column value A->B and then hits the save button. Here the system is successfully updating row 1756.I have verified the same scenarios for the different table which has same PK configuration. Is updating the rows in all scenarios.
CREATE TABLE [dbo].[Mytable2]( [Per*****Id] [int] IDENTITY(1,1) NOT NULL,.
.
[BatchId] [int] NULL, CONSTRAINT [PK_Mytable2] PRIMARY KEY CLUSTERED ( [Per*****Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOStill, Mytable has issues in updating the row.Thanks,Venu.