Jump to content
  • 0
DesiLogi

Trigger to update related records in same table as Insert

Question

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.. 

Share this post


Link to post
Share on other sites

10 answers to this question

Recommended Posts

  • 0

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- 

Screen Shot 2017-12-11 at 9.43.40 AM.png

Share this post


Link to post
Share on other sites
  • 0

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). 

Screen Shot 2017-12-12 at 3.55.40 PM.png

 

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...

Share this post


Link to post
Share on other sites
  • 0

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:

Screenshot.JPG

 

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

 

 

Share this post


Link to post
Share on other sites
  • 0

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. 

Share this post


Link to post
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.

Guest
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.

Loading...

×
×
  • Create New...