Hi All,
I have had good success using the Write to Data Store Entity Smart Service to INSERT rows into database tables using CDTs. I thought there will be an Update Database Smart Service just like there is a Delete from Data Store Entity but I read on Appian documentation that the Write to Data Entity is the same smart service used to UPDATE database rows. I also tried to find examples without success.
The Write to Data Store Entity accepts a Data Store Entity node input but I am having a hard time finding the parameter where I can specify the id for the primary key of the table and also I cannot see the parameter where I can specify which column in the database I want to update, and lastly the new value.
As an example, my CDT looks like this:
id,department,type,currentStatus
For the above CDT, I am trying to set the value of currentStatus to "Initiator Rework" for the row with id coming from pv!requestData.requestId. How can I configure the Write To Data Store Entity to achieve this?
Thanks
Discussion posts and replies are publicly visible
Hi Susane,
To specify Primary Key of the CDT:
Open "my CDT" you have created above, and in the fields, you can find columns like Name, Type, Length, Array, Key and some others. Click on the Key column for the field for which you want to set primary key. Say "id" in the "my CDT". In the dialog box that opens up, click on "Primary Key" as Key Constraint and check the check box below to make it Auto generated. Save your changes.
But then it should not be a problem, if you are creating a CDT, through create CDT from Database Table or View option, for Appian pre-selects the field which is marked as primary key in the table to be the primary key in Appian as well. Which version of Appian are you using?
"also I cannot see the parameter where I can specify which column in the database I want to update, and lastly the new value."
Unlike Database, you need not mention which field needs to be updated. You just pass the entire cdt with new values and Appian does the update for you.
For Ex,
So for the first time you write to database with values like below,
pv!myCDT.id:null,
pv!myCDT.department:Finance,
pv!myCDT.type:LoanRequest,
pv!myCDT.currentStatus: Requested
and in the corresponding DB table, it is stored as below.
Id Department Type Current Status
1 Finance LoanRequest Requested
Now when you want to update, you send in the id number, (unlike null when you insert), so that Appian knows which rows to update in the DB. Also, unlike an update query in Database, you need to send all values for the update of a particular row, both changed and unchanged, otherwise other fields will be nullified.
pv!myCDT.id:1,
pv!myCDT.currentStatus: InitiatorRework
And in the DB
1 Finance LoanRequest InitiatorRework
Sending only id and the field to be updated, results in loss of data as below
1 null null Initiator Rework
And what is the type of pv!requestData? The myCDT isn't having a requestId field?