Script Trigger for Next related Record
I have a Inventory Database that has material purchased from vendors, held in inventory, then signed out and charged to accounts by invoices.
They way it is set up is that every material on the Receipt Line table has a status of "Open" if it has quantity remaining on it. It also has a status of "Active" if it is the lowest ID number in the ReceiptLine to have an "Open" status. This is managed through a Material table with additional table occurrences of ReceiptLine table with matching open status, list of "Open" status, leftvalue1 of that list. When that value matches the record on the ReceiptLine then it has a status of "Active"
From the SignOut table, I have a selection of related records selecting only the "Active" status materials from the Receipt Line table. I have a script trigger that fires when I input a signout quantity on the Signout record then the trigger sets field on the "open" status and on the "active" status (on ReceiptLine table) to recalculate, which it appears to do on the related record and that is great but that is only part of what I want to do. If a sign-out quantity takes all of the material remaining, I want the script to be able to set field on the related signout record to close the current receipt and ALSO to activate the next receipt.
My question is how do I get the trigger to also go to the next record of the same type of material (bearing in mind that they are not the next record in sequence on the ReceiptLine table) and apply the "Activation" trigger on that record? Also, I am also considering the firing order for the triggers, so if the trigger I currently have is firing on the current related record that turns that from "Active" to "Inactive," should another trigger fire at on a separate instance (on the same field) to then find the next record (that has now moved to the top of the "Open" record list (on the Material table list) to then fire on it with the "Activation" Trigger?
I hope this is clear. I am new to Filemaker. Thanks in advance.