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
Please try to share a screenshot of your node configuration anyway. Plus a bit more of the whole error message you're getting. Leave out any work-related data, and use screenshot redacting tools to blur/hide/obscure any sensitive information, object names, branding, etc, that you don't want to put here publicly. The windows standard screenshotting tool has at least some of this functionality, and alternatively I use a tool called "greenshot" (freeware) that has built-in obfuscation capabilities...
Just some guesswork though based on the info you've provided - is your DB table set to "auto increment"? I haven't tried in a bit, but when a table is expecting to generate the next PK ID, and you try to provide one within the WTDS node, it might sometimes cause problems. Is the PK ID (or any other table column) used as a Foreign Key ID in some other table?
Hi adamn696785,
If you are you trying to insert a record with PK to the Table with auto increment in SQL Server DB, you may need to check on the IDENTITY INSERT Constraint for that table. As Mike mentioned can you please share the DB Server you are inserting into, and you can find additional details of the error in log files.
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.