3 Replies Latest reply on May 8, 2015 7:34 AM by ColinCampbell_2

    Script Trigger for Next related Record

    ColinCampbell_2

      Title

      Script Trigger for Next related Record

      Post

      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.

        • 1. Re: Script Trigger for Next related Record
          philmodjunk

          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? 

          Use a different relationship, one that includes matching by material ID so that only records of the same material are related.

          This is likely to require setting up more than one Tutorial: What are Table Occurrences? for the same table in your relationships.

          • 2. Re: Script Trigger for Next related Record
            ColinCampbell_2

            I have a new table occurrence that matches the ShopMaterialID for the ReceiptLine Table to the ShopMaterialID on the Signout Table, but I am not sure what the script should look like.  Currently I have the following scripts:

            Run Receipt Status: Go to Field (receiptlines::Receiptstatus), Set Field (ReceiptLines::ReceiptStatus; If (ReceiptsLines::RemainingQuantity > 0; "Open"; "Closed")

            That seems to work well for getting the current receipt to go from a closed to open status.  All following receipts have an open status already, so I am only using this script on the current related record.

            Run Activation Status: Set Field (ReceiptLines::ReceiptIDIfActive; If (ReceiptsLines::ActiveStatus ≠ "Active" ; "" ;ReceiptsLines::_ReceiptID_pk )

            I am thinking that these will work on the related records from the new matched table occurrence but I do not understand the navigation script steps needed to get that to work.

            Advice?

             

             

             

            • 3. Re: Script Trigger for Next related Record
              ColinCampbell_2

              For anyone interested.  I found the solution to the problem.  It was not the new table occurrence although I understand that thinking.

              I had the ReceiptLineID number as a middlevalue from a list of the related values from a receiptline table occurrence to the Materials table, that would tell me what the next value of ReceiptLinenumber would be for that material type, all values having open status.

              Then I wrote a script that fires from the Signouts table that, first set that variable, as $NextMatchedID, then goes to the related record on Receipt line, then enters a Loop of Go to Next Record, with an Exit condition of the ReceiptID = $NextMatchID, then when it exits the Loop it performs the evaluation script that I already had working, and poof. Problem solved. then just for fun, I had the script Go to Laoyut Signouts and Go to Last, so that it appears that it all happened by magic.

              Hope this helps other!