1 Reply Latest reply on Oct 11, 2011 10:49 AM by philmodjunk

    Job Deduction Script?



      Job Deduction Script?


      I am new to FMP and my current employer has the 8.5 version.

      We are trying to determine if/how we can keep a log of the date and qty's that are deducted from our jobs on a daily/weekly/monthly basis...sort of like a ledger entry on the ID # for each particular item.

      I am sure it needs a script I just am not sure how to begin.

      This Job Deduct field is used when inventory is used on a job and it obviously deducts the qty from the qty on hand. Because human error is common...we would like to write some sort of script that would enable us to track the date and the qtys that are being deducted.  There is a field that we have for current monthly usage...but that doesn't show us if we have deducted too many pieces until we do a cycle count of the item or place an order when the plant has plenty of stock....

      ANY advise would be helpful!!


        • 1. Re: Job Deduction Script?

          Before you look at scripts, you should consider the structure of tables and relationships to support this. You would appear to have series of jobs and need to log the consumption of items used in the completion of each job with the need to track not only when and on what job the material was consumed but then be able to track how much material you currently have on hand--possibly with re-order levels.

          This would appear to need these tables:


          Jobs::JobID = MaterialsUsed::JobID
          Materials::MaterialID = MaterialsUsed::MaterialID

          This is much the same as a typical invoicing system with "Jobs" replacing the "invoices" table. A portal to MaterialsUsed can be placed on a Jobs layout and a drop down or pop up menu field inside the portal can be used to select a material from the Materials table. Other Materials Used Fields would record the date and quantity.

          Reports based on the MaerialsUsed layout can then list all transactions where material was consumed broken down by job or you can get a list of all materials used for a specific job.

          With the addition of the right fields to MaterialsUsed table, you can set it up as a "ledger" where the consumption and reception of material is logged so that a running balance will show you how much of each item in materials you have on hand. This "ledger" approach lets you see how inventory levels have changed over time which can be useful when you choose to evaluate possible changes to your reorder levels.

          If you search this forum for "inventory ledger" you should be able to find several threads where this inventory management method is described in greater detail.