2 Replies Latest reply on Jul 2, 2012 8:25 AM by philmodjunk

    Linking database fields



      Linking database fields


      Hi everyone,

      New to this and am trying to create an invoice that can have a link between part numbers and items on invoice database to inventory database so when I type in the part number on the invoice it will reference the inventory database and also reduce the inventory amount reflecting the amount sold on invoice for stock control.


      I hope this is a clear enough description of what im trying to do but if not I will try again on later posts..


      I am using the trial version to make sure I can achive what I need it to do before I purchace it..


      Hope someone can help as it would be greatly appreciated.




        • 1. Re: Linking database fields

          OK I managed to work out the first part of my question so now just need to work out how to reduce "units on Hand" when a quantity is selected on a given item on an invoice..


          Im guessing I need to use a script for this to add the value of "qty" on the invoice to the "out" field on the inventory database..


          Any ideas?





          • 2. Re: Linking database fields

            It depends on how you set up your tables and relationships. One approach is to convert the lineitems table typically used in invoicing systems to function as an "inventory Ledger" where product added to inventory is treated like a "debit" and logged in one number field and items removed from inventory (sales, shrinkage, discontinue, store use, etc.) are logged in a "credit" field. The system can than compute a total of items added - items removed to compute the total on hand.

            A key added advantage to using such a method is that you not only can tell how many of each items should be on hand, but you can review how the qty on hand fluctuates over time to see if you need to adjust your re-order points to ensure no back orders and yet keep current inventory levels a small as possible so as not to tie up capital in unsold inventory.

            If you search this forum for the phrase "Inventory Ledger" you'll find a number of discussions of how to define the fields and relationships for such an approach.