Hi Community,
I have noticed strange and unexpected behavior of the Write Records Smart Service in the current version of Appian.
I have a table with a primary key that is not automatically populated via auto-increment or a sequence; instead, the value is provided by the application. A sync-enabled record type is configured for this table. I am trying to create a process model that is responsible for cloning a record.
The process model consists of three steps:
queryRecordByIdentifier
pv!record
While executing the process model, I encountered the error:
The input "Records" cannot update a record's primary key value. The request to update the primary key value of CSR_RT_AVAILABILITY is not valid. Location of the error: ...
This is very strange because an update should not have occurred at all—only an insert of a new record. As I explained above, I assign a new value to the primary key attribute in the second step of the cloning process, and there is no existing record with that value in the record type or the database table.
I also tried an alternative approach: instead of directly modifying the attribute values in the process variable (pv!record), I instantiated a new record (of the same type) and stored it in a different variable. When I used this new variable with Write Records, the insert operation succeeded without any issues. Unfortunately, this approach is not feasible as I have many record types and this would require manual attribute mapping for each of those (and of course I would like to skip that).
It seems that Appian, despite stating in the documentation that it will perform an INSERT if no record exists with the given primary key, still attempts an UPDATE when using a process variable that was initially populated as a result of executing queryRecordByIdentifier() or queryRecordType().
INSERT
UPDATE
queryRecordByIdentifier()
queryRecordType()
I'm referring to: https://docs.appian.com/suite/help/24.4/Write_Records_Smart_Service.html#create-new-records
The source table is not configured to AUTO_INCREMENT or use a sequence and you provide a primary key value that does not exist.
AUTO_INCREMENT
Has anyone else observed similar behavior, and how did you resolve this issue?
It seems to me as a bug, and before I involve the Appian Support I would like to check with you if somebody may had encountered this as well.
Moreover, this appears to be related to the version of the Smart Service in the latest Appian release. When I use an older version of the service (by copying it from another process model—recognizable by the absence of the Setup tab, unlike the newer version), everything works fine, and Appian correctly recognizes that an INSERT should be performed.
Screenshot of the process model
Error message:
State of variables:
Thank You in advance.
Discussion posts and replies are publicly visible
I try to avoid manual PK assignment as much as possible. Do you have a specific reason to go that route? How do you avoid creating two records with the same ID?
I suggest to go with the support case. Please keep us posted.
It's legacy code. For whatever reason, original designer decided to model the database this way. Due to the complexity, a redesign is not feasible at this moment.
Regarding the duplicated IDs, there is some program logic that prevents it from occuring. We had no issues so far with this.
We will try our luck with the support.
Appian is changing the inner logic of how records writing and reading works. It might be that when your provide a OK value, that this logic expects this ID to exist. That would explain the error message.
It has always been this way for records AFAIIK. If you want to copy a Record and create a new PK, you need to create a new instance of the Record Type and copy the attributes 1 by 1 rather than using a!update.
Thank you for your feedback.
As I already said, Appian is behaving differently with the older version of the Smart Service (i.e. it is working as we expect - it inserts the record). Maybe this behavior was not expected/acceptable (by Appian Developers) and they changed it in the mean time (unfortunately for us).
Becoming more and more curious what Support will answer.
Which version was that? I've always known it to function this way.
The version before the Setup tab was introduced.
When I generate the docs for the process model, the new Smart Service is identified as 23r3 so any version before this is fine. If you know any way to get the precise version of the Smart Service used, please share it.
Unfortunately, there is no version indicator for the older version of the smart Service on the generated doc.