Trigger that creates multiple records

[kcastagnaro]

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!

 

 

 

 

[NiceDuck]

Uhm... You dont need to re-querry the values for each field, just use a field block on the field values itself


just use something like this:




You already used a select block as a source for insert, no need to repeat it for every field.

[NiceDuck]

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 block

Try using something like this instead:


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

 

[kcastagnaro]

Thank you, NiceDuck! Every day I'm getting a little bit better!

[DaveS]

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.