Jump to content
  • 0

Trigger that creates multiple records



Hi! New to triggers in Caspio. Thank you ahead of time for any assistance you can provide. 

I have a table of PARTICIPANTS (patients) and I am enrolling them into research STUDIES. Each study has EVENTS associated with it.

Table 1: Participant

  • Field 1: Participant ID
  • Field 2: Participant Name

Table 2: Study

  • Field 1: Study ID
  • Field 2: Study Name

Table 3: PEventFinal (Events associated with each study)

  • Field 1: PEventFinal ID
  • Field 2: Study ID
  • Field 3: Event Name

I enroll patients onto a study through a datapage to a 4th table.

Table 4: PartStudy

  • Field 1: PartStudy ID
  • Field 2: Participant ID
  • Field 3: Study ID

The problem I have is when I try to associate the study EVENTS with the PARTICIPANT enrolled in the STUDY. The number of events for each study varies. Some studies can have 2 events, some studies can have 30. I would like to create a trigger on the PartStudy Table that creates a record in a 5th table that list of of the study EVENTS for that PARTICPANT on that PARTICULAR study.

Talbe 5: PartSTudyEvents

  • Field 1: PartStudyEvent ID
  • Field 2: PartStudy ID (as text)
  • Field 3: Participant ID (as text)
  • Field 4: Study ID (as text)
  • Field 5: Event Name

I have created a trigger (see below) that inserts the correct number of records into table 5, but inserts the first EVENT only multiple times.

IN the example of Study A, there is a total of 3 events. When I enroll Particpant #1 on Study A, I want the following records to be created in Table 5. 

  1. Participant #1, Study A, Tissue Sample
  2. Participant #1, Study A, Blood Draw
  3. Participant #1, Study A, Xray

But what I get is this: 

  1.  Participant #1, Study A, Tissue Sample
  2. Participant #1, Study A, Tissue Sample
  3. Participant #1, Study A, Tissue Sample

I've read every post I can find on looping and If/Then and inserting multiple records. I'm not a coder so I'm stumped. I'm sure the answer is right in front of my nose, I just haven't quite gotten there. 


Any help would be greatly appreciated!





Screenshot 2020-12-21 120418.png

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

The reason why you are having the same record per is because even if  you are using a constant select block per each field, so no matter how many recurrence your select block provides for your insert block, it will keep using the same select block you assigned on it.

By the way, there is no need to use 'if then' block here, just put the condition statement you are using on the where clause of your main select blockimage.png.f8692247f1cbb605872d2c9d45a417bf.png

Try using something like this instead:

Try contacting Support as well, if your trigger is not that complicated, they may assist you on it. 


Link to comment
Share on other sites

  • 0

FWIW - I was really struggling with a similar situation and trying to follow this example.  It turned out, the issue wasn't my trigger code at all.  What  was preventing my trigger from running was that I had referential integrity turned on in the child table, and was trying to insert a record in the child with the foreign key of the (not yet committed, apparently) parent.  Turned of Ref Integrity and worked like a charm.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...