Question

write multiple rows in database in process modeling

Forum|Forum|4 years ago
January 29, 2022
17 replies
0 views

Hi everyone

i m struggling with the process modeling when i need to write multiple rows in a DB ,

i have a CDT of "Documents" which i put as an array in my form and a CDT of "creditRequest" which contains an array of the Documents CDT.

i need to put both of them in seperate databases , the first one is the documents which i want to store and then after to store the creditrequest which will contain the documents.

now when i debugging my process i can see that my documents don't have an ID after that it s passing the "write to db",

and then after i have problem with the creditrequest ,

how i need to do for write the multiple documents that i want to write in the db and then write them in the next Db of Creditrequest ???

gopalk2865

Participating Frequently

Hi, create two process variables, one for documents cdt and another for credit request cdt and using script task get the data for these two variables then using write to datastore entity or multiple datastore entities, you can write these data easily.

Y

yanivb0001Author

i used two process variables one for the documents cdt and the other for credit request cdt as you said . i wrote the data to document entity first and then wrote in the credit request entity .

after insertion of my data to the db. of documents, I can see that he did the relation between each document and the credit request db as i wanted , but i noticed in the debugger that for the process variable document it didn't wrote the ID for each one , but in my db each one got one .

because after when im looking to the id in the creditrequest entity i cant see the id

you know what is the problem ?

richardm900458

hi yaynivb,
that is because you are lookin here into the DB table of the nested CDT.
(check your stored values of the credit request)
But you try to store it a second time.
What is the purpose of it?

richardm900458

Hello yanivb,
PA_Documents is nested into PA_Creidtrequest. first of all you don't need to store nested CDT additionally to the "main" CDT. So i looks like you have it twice in. Once as part of the nested CDT construct, second completly sperated as own array.

Your decision should be: is there any other context, I need to call for this document?
If not, you can consider nested, if there is another posisble context don't do it and use foreign keys.
Personally i prefer working with DB views and foreign keys rather than nested CDT.
some input: Custom Data Type (CDT) Relationships - Appian 21.4

1.) the first question would be, if nested is your desired solution?
2.) why do you want to store them twice? Once as nested, once as seperat DB value?

on top: Did you declare "ID" of PA_documents as primary key and turned "auto increment" on?
additionally you didn't check "multiple" at your "Write to datastore" node. ;) ("stored value" output maintained?)

kind regards,

Richard

peter.lewis

Employee

In general I think the comments here are overcomplicating this. Since you have a nested 1:M relationship, you usually don't need to have multiple operations to write to the database. Instead, writing to the top level creditRequest should also write to your document table.

One thing just to check: if you go to your CDT for creditRequest and you click on the key icon next to the documents field. When you open that dialog, can you confirm - do you have this checkbox selected for "Updates to a parent value should also update associated child value(s)"? If so that means that a single write to data store will update both creditRequest and documents.

If that checkbox is selected, back your process just remove the write to the document entity and just perform the write to creditRequest. In addition, save the output for Stored Values back into the process variable for your credit request, and you should see all of the IDs populated.

Y

yanivb0001Author

okay i get you

i did what you said i changed the process to test , i also checked my CDT as you said i checked the key and it was already selected

i removed the write to the document , and at the end of the process it didn't give me an error at the end, i got the id s cool ,

but when i m looking in the db i see something strange i have null in the table of credit request

whereas in the table of document i can see them, we can see also in last coloumn of this table the id of the request

can you explain me please if you understand what is mean ?

peter.lewis

Employee

My guess is that column in creditRequest is a leftover from an earlier version of your CDTs. In this instance I would expect that the creditRequest table should NOT have any reference to the documents table at all because of the way relationships work: if you have a one-to-many relationship, the foreign key should be in the child table (document).

I would expect that you could remove that column padcments_idcreditrequiest_id and there would be no effect for any of your CDTs [emoticon:c4563cd7d5574777a71c318021cbbcc8]

Sign up

Login with SSO

Login to the community

Login with SSO