5 Replies Latest reply on Feb 23, 2009 3:39 PM by Jade

    Adding a field to another one, depending on its status

    PatrickHolzer

      Title

      Adding a field to another one, depending on its status

      Post

      Hi,

       

      I have a field called current_stock which displays the quantity of a specific product that we currently have on stock.

       

      I have a table called "Inbound Orders" (which handles what we order from the manufacturer in order to sell it to customers); this table contains - among others - two fields: inbound_order_quantity and inbound_order_status.

       

      Question:

       

      How do I add inbound_order_quantity to current_stock if the status of inbound_order_status changes from "pending" to "completed" - because if it is "completed", that means we have received the goods and have taken them on stock - ?

       

      Thank you!

      - Patrick

        • 1. Re: Adding a field to another one, depending on its status
          Orlando
            

          Hi Patrick

           

          Which version of FileMaker are you using?

           

          If you are using FileMaker 10, you could setup a Script Trigger on you inbound_order_status field that will perform a script doing the following

           

          If [ inbound_order_status = "Completed" ]
          Set Field [ current_stock current_stock + inbound_order_quantity ]
          End If 
           

          This will then add the two quantities together for you only if the status is "Completed"

           

          To set a Script Trigger, in Layout Mode select your field and go: 'Format > Set Script Trigger...' and OnObjectModify is probably the best one for the job, or OnObjectExit, and I would also suggest the field be a pop-up menu of radio button field, if not already.

           

          Hope this helps 

          • 2. Re: Adding a field to another one, depending on its status
            PatrickHolzer
              

            Hi Orlando,

             

            Thanks very much - works perfectly!

            And yes, I'm using FM 10

             

            - Patrick

            • 3. Re: Adding a field to another one, depending on its status
              Jade
                 …And what happens if the user accidentally selects status "Completed" a second time?
              • 4. Re: Adding a field to another one, depending on its status
                Orlando
                  

                Hi Jade,

                 

                I suppose that would all depend on how the record is treated after it has been marked as "Completed" and whether or not it is locked down and made read-only.

                 

                Which can be done any number of ways. Using Account & Privileges to lock the record.

                 

                An OnObjectEnter Trigger applied to the field that runs a script to stop the user being able to enter into the 'inbound_order_status' field if it is marked as complete.

                 

                Or the user views completed records on a different layout that does not allow data entry into that or any field for the record.

                 

                Let me know if you want more detail on any of the options. 

                • 5. Re: Adding a field to another one, depending on its status
                  Jade
                    

                  Hi Orlando,

                   

                  Thanks for your analysis.

                   

                  Having worked with OE systems for some time (decades), I have learned that a lot can happen here.  (User completes the wrong order-item; customer cancels the order; customer changes the ordered quantity; order is shorted; portion of ordered quantity is cross-shipped to a higher priority customer; etc., etc.)

                   

                  Locking down the record is probably too restrictive depending on how robust the design requirements are.

                   

                  I suggest updating a new field (quantity completed) when the status is changed to completed so that it can be backed out (reversed) if need be.

                   

                  Jade