Trigger to update related records in same table as Insert

[DesiLogi]

Hi,

I have a table that I need to update records related to the ID value when a new record is inserted. 

When a new record is created a field called 'InvoiceID' stores (via parameter passing) a value. That value might be used multiple times in the table (for example, this is a Payments table linked to an Invoice table. The InvoiceID (unique- one) from the Invoice table is stored (many) in the Payments table for each Payment record for the same invoice. So for 5 Payments records there might be InvoiceID=2 used 3 times and InvoiceID=4 used 2) .

When a new record is inserted to the Payments table I need the trigger to look up all other records in that same Payments table with the same InvoiceID and update them. I tried using the Where tabs with Field=InvoiceID Equals Field=InvoiceID but the trigger doesn't work. If I manually put in a number for the 2nd InvoiceID to test it the trigger does work. So the issue is the trigger being able to match existing record's InvoiceID value with the submitted/inserted record's InvoiceID value and update them. 

Does anyone know how/if this can be done? It seems like it should be simple so I must be missing something basic.. 

[Vitalikssssss]

Hi DesiLogi,

We screenshot of your existing Trigger will help us to find the reason of the issue.

cheers,

vitalikssssss

[DesiLogi]

Hi Vitalikssssss,

I've posted the screenshot. As I said, I need the trigger to look up records in the same table as the Insert table, that have the same InvoiceID and CompanyID value, and then update the field 'Use This' to be 'False' (it's a Yes/No field). Thanks for any help you can give- 

[Vitalikssssss]

Hi DesiLogi,

You should use Join with #inserted instead of Where condition in order to replicate the desired behavior. 

Hope this helps.

Regards,

vitalikssssss

[DesiLogi]

Hi Vitaliksssss,

I didn't realize one could do a join to the same table as the insert. I used your method and it works great- many thanks!

[DesiLogi]

One last question- I'm trying to set the field 'Use_This' to 'True' for the new, inserted record. I can't seem to get it to work using the Join method (the other part that sets Use_This to False works well). How can I set, still using this other part of the trigger, for the new record to have Use_This = True (I was thinking of Join Insert.InvoicePayRequest.RequestID = InvoicePayRequest.RequestID (the unique ID value) so that it updates only that specific record's Use_This to True. 

When I trie to do a simple 'Insert Into' I get a nested error message (attached). Surely there must be a way to make the new, inserted record's Use_This field always be True (while updating all the existing related records to False). 

 

Also, whenever a record is updated in that table it for some reason now runs the Use_This = False trigger action on itself. So if the user checks Use_This then the trigger runs and unchecks it, which it shouldn't because it should only run the trigger on records where RequestID <> RequestID (and therefor not itself). I really need some help with this...

[Vitalikssssss]

Hi DesiLogi,

You should use an Update block instead of Insert block if you would like to set the field 'Use_This' to 'True' for the new, inserted record.

Here is an example:

 

This Trigger should do the trick.

I am afraid I have no idea of such Trigger behavior for another issue which you have discovered.

I think it is better to ask Caspio Team about the reason for this odd behavior.

 

Regards,

vitalikssssss

 

 

[DesiLogi]

Hi Vitaliksssss,

Thanks for the help with the insert Update issue. That works well- I couldn't figure out how to get it to do that.

Now the last thing I need to do is create a trigger for when the table is Updated (meaning the user updates an existing record's 'Use_This' to 'True') to update all records in the same table, with the same CompanyID and InvoiceID but NOT the same RequestID (the unique ID value)- because that would be the record being updated manually.

Basically, so if a user marks one record's 'Use_This= True' then all other records in the same table (with same CompanyID and InvoiceID) are marked 'Use_This=False'. So there can be only one record with Use_This=True for any given InvoiceID.

I think it would be similar to the first part of the trigger but running on Update. Problem is I keep getting 'Nested' error messages when I try to update the same table I'm running an Update trigger on. I think once I get the fundamentals worked out like in this issue the Triggers will make a lot more sense. Thanks very much for your help on this, I really appreciate it. 

[Vitalikssssss]

Hi DesiLogi,

I am afraid that you cannot use Update block with the same table as the one on which you have built Trigger Action with event "update" because it will create an infinitive loop of Trigger execution.

Regards,

vitalikssssss

 

[DesiLogi]

That's what I'd thought. So to be clear, if a record is updated in a table it's impossible to update another field in that record OR other records in the same table? 

[Vitalikssssss]

23 hours ago, DesiLogi said:

That's what I'd thought. So to be clear, if a record is updated in a table it's impossible to update another field in that record OR other records in the same table? 

 

Hi DesiLogi,

I am afraid that you are correct. 

[RuisiHansamu]

Hi there, maybe this forum post might help and this is one of the latest post I presume. 

 

[DesiLogi]

Thanks for the tips--I ended up using a field as a signifyer to update the record and then, as the last part of the update clause, change the signifyer of the record so it wouldn't update again, thereby avoiding infinite loop. Also, I started this post back when a lot of my tables were locked because of back-end triggers created before front-end triggers were available. I've replaced almost all of them now and worked out how to avoid Nested limit errors for the most part. Hope this thread helps others trying to do the same type of thing-- 

Quote

 

 

[GoodBoy]

On 12/12/2017 at 12:41 PM, DesiLogi said:

One last question- I'm trying to set the field 'Use_This' to 'True' for the new, inserted record. I can't seem to get it to work using the Join method (the other part that sets Use_This to False works well). How can I set, still using this other part of the trigger, for the new record to have Use_This = True (I was thinking of Join Insert.InvoicePayRequest.RequestID = InvoicePayRequest.RequestID (the unique ID value) so that it updates only that specific record's Use_This to True. 

When I trie to do a simple 'Insert Into' I get a nested error message (attached). Surely there must be a way to make the new, inserted record's Use_This field always be True (while updating all the existing related records to False). 

 

Also, whenever a record is updated in that table it for some reason now runs the Use_This = False trigger action on itself. So if the user checks Use_This then the trigger runs and unchecks it, which it shouldn't because it should only run the trigger on records where RequestID <> RequestID (and therefor not itself). I really need some help with this...

Just to share an information about this error, it occurs when you reached the maximum number of nested level which is the number of triggered actions that can be initiated another triggered action executed by an event or a task. Also, this limit will be based on your account plan. More details about this can be found in their article: https://howto.caspio.com/tables-and-views/triggered-actions/#:~:text=Maximum number of nested levels

[IamNatoyThatLovesYou]

On 5/17/2022 at 8:51 AM, GoodBoy said:

Just to share an information about this error, it occurs when you reached the maximum number of nested level which is the number of triggered actions that can be initiated another triggered action executed by an event or a task. Also, this limit will be based on your account plan. More details about this can be found in their article: https://howto.caspio.com/tables-and-views/triggered-actions/#:~:text=Maximum number of nested levels

Hi Guys! Here is an updated documentation for the nested levels as the old one is no longer available. https://howto.caspio.com/triggered-actions/triggered-actions-2/#:~:text=Date%2C and Variables. -,Limits,-Depending on your