Im getting the error when writing to data store: ... " with identifier value `1007` does not exist " . This is the value of my primary key I'm trying to insert into the data store. I already have the primary key from user input and I'm trying to write all of the info into an empty data store. (I would share screenshot but it's work information). This is occurring within my process model when I am using the write to data store smart service. Why is this happening?
Discussion posts and replies are publicly visible
You’re getting the error because system treats a write with a primary key value as an update, not an insert. Since no record with that key exists, it fails.To fix this, when inserting new records, leave the primary key field blank so Appian and the database can auto-generate it. Only provide the primary key when updating an existing record. Additionally, ensure your database table and data store entity are configured to auto-generate primary keys. Verify in your process model that the primary key field isn’t populated during insert operations to avoid this error.
I dont think my PK within my sql database is set to auto increment, but even when setting the primary key to null prior to writing to the data store entity causes this problem. The ' ' identifier value is typically what the user would input
Your error happens because your database table's primary key is not set to auto-increment. When inserting, if you leave the primary key blank or null but the DB does Not auto-generate it, the insert fails - Appian shows an error with an empty identifier.
Have you checked the log file for more details? Error might be because of some other columns (failing the constraints) in that record.
Is there a way to set the primary key to auto increment within appian? I have a blank database within SQL server and I use that as a data source within appian so it seems that Appian copies the information over.
Appian cannot set up auto-increment for a primary key; this must be configured directly in your SQL Server database.
My two cents here
The identifier should not be left with users to input. It should be primary, and auto generating values on inserting a new row. If user is entering this value make this column/field NOT the identifier but just another column . Create another column which does not depend on user as the primary key with auto increment enabled.
Since you already have a cdt and thus table in database i suggest you to alter the table & cdt structure following data structure design best practices. The current design you have does not follow standard design principles.
Okay I have changed my 3 cdt's to have their own primary keys which are not user input. I have the primary key as its own field that is auto incremented within sql. But now I get this error:I have also posted the CDT value at the time of failure and the write to data store entity node configuration
The Write to DSE node configuration needs to be corrected. In the input ‘Data Store Entity’ , the value seems fine. The save into should be blank here.
Then add a new Input here. You can name it after your cdt formRequest. The type should be the cdt type. Now in the value of this formRequest input put the pv!formRequest. Dont put any thing in saveinto for this input also.
Rather in the output tab you can save the output back in the pv!formRequest. Dont configure save intos in the input node. Try and let me know!
Try to follow the documentation here: https://docs.appian.com/suite/help/25.2/Write_to_Data_Store_Entity_Smart_Service.html#data-tab
Hello Harsha thank you for the reply. I have entered in the information as you said but now I get the error where my process model tells me that I can't enter in null values for the primary keys. I have a main CDT -> {main CDT fields, related CDT1, related CDT2}. I am trying to write individually to the related CDT's first and then save the output back into the process variable but this still occurs. And I made sure that appian had the primary keys be auto generated when writing new records to data store entity. I have attached the picture of the error which relates the related CDT1
I have fixed my problem, the main issue was that even though I updated my Appian table to auto-increment and verified the tables, my sql server was still outdated with the old properties(no auto increment). So after I deleted the tables, the new key settings were able to apply.