How-to copy data from entities referencing each other
Application Type

Hello everyone,

I have a table with surveys, a table with questions (there is a column referencing the survey: Survey_ForeignKey), and a table with the multiple choice options for each question (there is a column referencing the question: Question_ForeignKey).

In my app, there is the option to copy and paste an already made survey.

I have managed to copy the survey table record, and the questions table records, but how do I manage to also copy the multiple choice option records? Maybe a loop inside a loop? But I am not sure how to do it...

Here's my CopyPasteSurvey Client Action so far:

Hi Cute Bear,

(never tought I'd start a message like that on a professional forum šŸ˜„)

you don't need to do a aggregate inside a loop of questions for this, you can write an aggregate that transverses both foreign keys. 

That would be an aggregate with 3 sources simply joined on both foreign keys :

  • Response_choice with Question
  • Question with Survey
  • Where surveyId = to be copied survey id

But for this type of work, you might (depending on whether you feel ready for this) want to consider writing sql in the style of 

create .... as select ....


EDIT : oops, there's one little thing I wasn't thinking about : with this approach, you wouldn't know what foreign key to give the newly created response choice.

I think my approach would be to have that aggregate with 3 sources, sorted on id of question, have one single loop, check if there is a different question id, in that case create the question, and create every response choice you encounter, attaching it to the last created question.

Oh, and one more thing.  You'll want to wrap the whole operation inside a server action (as service studio is probably already asking you to do)

For 2 reasons

Performance : you don't want control of operation go back and forth between client and server

Database integrity : you want the whole thing to either succeed (preferably) or fail, at end of every server action, work is committed to the database, so if something goes wrong in a later server call, you are stuck with half a copied survey.


Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.