Trigger - what is wrong with the last part?

[biota]

Hi all

So the Insert block is triggered by any new record inserted to the Table.  It then inserts/copies around 18 rows from another table into this one. 

The Set block is supposed to insert the report_ID of the table row that triggered the action.

However I can't get the last section to work, which leaves all the inserted rows 'orphaned'. 

Can anyone see the issue?

Thanks in advance

 

[cheonsa]

1 hour ago, biota said:

Hi all

So the Insert block is triggered by any new record inserted to the Table.  It then inserts/copies around 18 rows from another table into this one. 

The Set block is supposed to insert the report_ID of the table row that triggered the action.

However I can't get the last section to work, which leaves all the inserted rows 'orphaned'. 

Can anyone see the issue?

 

Thanks in advance 

 

Hi @biota,

Based on what I understand, you would like to update the Report_ID of the inserted records. May you please try to use #inserted instead of recordsLfwInd_tbl_1?

For example:

 

Hope this helps!

[cheonsa]

To add to my previous comment, The #inserted table represents data that is being inserted or updated in the table.

You may check this video for more Trigger samples:

 

[biota]

Hi @cheonsa, thanks.

That is how I originally set it, but that does not work. In fact when that way it only allows me to select from the #inserted table on both sides of the update?

I am trying to update the same tables that the records are inserted into, in the block above this one. 

 

[biota]

8 minutes ago, biota said:

Hi @cheonsa, thanks.

That is how I originally set it, but that does not work. In fact when that way it only allows me to select from the #inserted table on both sides of the update?

I am trying to update the same tables that the records are inserted into, in the block above this one. 

This is a different table to the one that triggers the action.

As I understand it, #inserted relates to the original table into which data was inserted to trigger the action. 

[biota]

This is all part of a larger block, and I wonder whether this is conflicting? I know we can only trigger one action though.

It feels like maybe this update block should be part of the insert bloc

[cheonsa]

Hello @biota,

Sorry for the confusion. I thought that recordsLfwInd_tbl_1 is the same Table where you have the Triggered Actions. By the way, you are correct that #inserted relates to the original table into which data was inserted to trigger the action. 

 

In this Inner join, can you confirm that there matching records between the two tables? You are using the Report_ID field to join the Tables, and this is also the field that you are updating. 

 

[biota]

Hi @cheonsa, no problem at all.

The part below inserts 10 rows from the srcLfwInd_lkp table into the recordsLfwInd-tbl.

At that point there is no matching field between the two tables.

I need the trigger to enter Report_ID from the #inserted table into the recordsLfwInd-tbl, which is why I joined them on that field. This would be a M2M relationship I suppose. One Report_ID can have many entries in the recordsLfwInd-tbl.   

For the join, I have tried it all ways but still didn't seem to work. 

Thanks again!

[cheonsa]

6 hours ago, biota said:

Hi @cheonsa, no problem at all.

The part below inserts 10 rows from the srcLfwInd_lkp table into the recordsLfwInd-tbl.

At that point there is no matching field between the two tables.

I need the trigger to enter Report_ID from the #inserted table into the recordsLfwInd-tbl, which is why I joined them on that field. This would be a M2M relationship I suppose. One Report_ID can have many entries in the recordsLfwInd-tbl.   

For the join, I have tried it all ways but still didn't seem to work. 

Thanks again!

Hello @biota,

Joining them with Report_Id could be the reason why the trigger is not working, as you mentioned, it is blank initially. And the Report_ID value will be coming from the projectReports Table. Are there any fields that you can use from the inserted Table to match in the projectReports Table? Maybe you can use that in the inner join instead.

[biota]

28 minutes ago, cheonsa said:

Hello @biota,

Joining them with Report_Id could be the reason why the trigger is not working, as you mentioned, it is blank initially. And the Report_ID value will be coming from the projectReports Table. Are there any fields that you can use from the inserted Table to match in the projectReports Table? Maybe you can use that in the inner join instead.

Hi @cheonsa that one is the #inserted table. Quite the dilemma it seems!

[biota]

It is looking like I need a different solution.

I am going to make a new post asking for suggestions.

Thanks for your help @cheonsa!

[cheonsa]

Hi @biota,

You can also open a ticket for the support team to take a look at it further. 

https://tickets.caspio.com/

They are great with Triggers!