1 Reply Latest reply on Mar 5, 2013 6:18 AM by mikebeargie

    Formula/Function

    rogerslp1957

      Qty Location Part Number

      2 2506 80006710

      1 2506 324692

      3 JOB 45745

       

      I need a formula/function that will look in my WORK ORDER FORM for the number “2506” and only 2506 (which is my van stock location) and then subtract the Qty of that part from my QTY USED van inventory form when a new work order is created

       

       

      Part Number Qty Instock Qty Used Qty Needed

      80006710 12 2 2

      324692 6 1 1

       

      any help will be greatly appreciated

        • 1. Re: Formula/Function
          mikebeargie

          You can either script this (static values), or replace "qty used" with a calculation (realtime calculated sum).

           

          a quantity used calculation in FM12 (using ExecuteSQL, requires no relationship) would look like:

           

          ExecuteSQL(

          "

          SELECT SUM(Qty)

          FROM WorkOrders

          WHERE Location = ?

          " ; "" ; "" ; "2506" )

           

          You could also relate your work order and inventory tables based on Part Number, then do a plain calculation for quantity used in inventory like:

          Sum(WorkOrders::Qty)

           

          The disadvantage of realtime calculations is that the number will continue to increase (and thus calculate slower as time goes on), until you clear or archive your work orders table. the way around this is to create a script that uses "set field" to update values in your inventory table everytime a work order is changed.