2 Replies Latest reply on Aug 30, 2012 12:13 PM by DavidBradow

    How to change the "negative" result of a calculation to "0"



      How to change the "negative" result of a calculation to "0"




      This may seem like a beginners question (although I'm sort of a beginner), but I need to find out how to change the result of a calculation to "0" if the result is a negative integer.

      Currently, the calculation field is set up as follows:


      M Qty Left =

      Qty - Sum ( Molding Data::Quantity )


      The field (M Qty Left) is adding the total quantity (Qty) from a separate table (Molding Data) from multiple entries per order number and then subtracting that from the original order qty in the original table (Table).  That part of the formula is working flawlessly, but sometimes we produce extra qty and that sends my calculation into a negative number and I need the field to show "0" for any negative integers.


      Any help would be greatly appreciated.

        • 1. Re: How to change the "negative" result of a calculation to "0"

          Let ( v = Qty - Sum ( Molding Data::Quantity ) ;
                   If ( v < 0 ; 0 ; v )

          But this strikes me as a "brute force" solution that may cause problems for how your business uses this info.

          I recommend investigating using a "transactions" or "ledger" table to record each inventory change. Such a method allows you to both increase and decrease the "Qty on hand" totals for each item in inventory by adding new records to this "ledger" table.

          An example of this table can be found in the FIleMaker 12 Inventory Starter Solution. If you don't have fmp12, you'd set up a table like this:

          TransDate (Date field auto-enters creation date in most cases)
          ItemID (Unique ID of each iventory item)
          Description (text to describe inventory change, Product Received, manufactured, sold, shrinkage, Return to Vendor, etc.)
          In  (number)
          Out   (number)
          cBal (In - Out)
          sBalance (Summary, total of cBal)

          To increase inventory, you create a new record, specify the ItemID and enter the quantity in the "In" field. To decrease inventory, you do the same, but put the quantity removed from inventory in the out field.

          Not only does this allow you to compute quantities on hand for every item in your inventory--both items you manufacture and items you consume during the manufacture process, You can generate reports from this table that show how inventory levels for each item are changing over time--which can you help you better manage your inventory levels.

          • 2. Re: How to change the "negative" result of a calculation to "0"

            This solution worked well.

            We keep track of our "stock" orders differently than our custom orders.  This database is essentially just to monitor the custom orders and make sure that the product is manufactured in it's entirety and that no orders get missed or short shipped.  For the purposes of our immediate needs, your solution is absolutely perfect.

            As we continue to expand the database to include more information and functionality, I'm sure that I'll need to consider alternative solutions and tables. I'm also sure that there are much better ways of handling it, however, in a pinch, this will suffice until I can devote more time to truly understanding FileMaker.

            Thank you for helping me on both of the issues that I have posted in the forum!