2 Replies Latest reply on Oct 2, 2009 5:18 PM by davidel

    Inventory with serial number turnover



      Inventory with serial number turnover


      Dear All,


                        I am really confused how to manage incoming serial number (Receive part from supplier) and

      outcoming serial number (Ship parts to customer). The reason why I insisted to use serial number because 

      similar part number might have different cost depends on the date when the part was received from supplier

      (The cost depends on Yen/Euro conversion during arrival date).


                        I did setup suppliers, customers, parts, order, invoice, inventory and inventorylines tables.

      In the Order layout, I have the order tables and inventorylines tables (portal row), and I used this portal row to 

      enter incoming parts number, incoming serial number, input qty, price, sub total and grand total.


      Inside the Inventory layout, There are inventory tables (part number, part detail, number of stock in hand) and

      inventorylines tables (portal row). I want to make sure that when we enter part number in the inventory tables (find mode),

      and all detail stock in hand appear in the inventorylines tables/portal row inside inventory layout (serial number, qty, price).

      It means that once data was entered through the order layout (inventoryline tables), the inventoryline tables inside the 

      Inventory layout also show all data of stock in hand including the latest entered data. (serial number, qty, price)


      In the Invoice layout, I set the invoice tables and inventoryline tables (portal row). 

      I am going to the input outcoming parts number, outcoming serial number, output qty, price, sub total and grand total

      into the inventorylines table. When this data is entered (outcoming serial number, outcoming parts number and etc),

      the detail stock in hand inside the inventorylines table (portal row inside inventory layout) also get updated.

      Simple way to say it, As i entered the outcoming parts data inside the inventorylines tables of the invoice layout,

      the related parts data (based on the serial number) in inventorylines table of the inventory layout is deleted.


                     Would anyone be willing to tell me whether this kinda system would be possible to be built

      through filemaker ?  What is the easiest way to do it ?  Any advice would be highly appreciated.

      Thanks all.

        • 1. Re: Inventory with serial number turnover

          Is the supplier serial number marked on the item?


          If so, You need two fields in your inventory table: SupplierPartNumber and SalesPartNumber.


          Each time you receive an item, log it in by supplier part number and also enter the sales part number.

          When you sell an item, log both part numbers again.


          You can even define a self join relationship that links items by SupplierSerialNumber and use it to confirm which recieved items have also been sold.


          If not, then you'll need some kid of FIFO (first in first out) system to log outgoing parts by the "oldest" unsold supplier serial number.

          • 2. Re: Inventory with serial number turnover

            I apologized for late reply. 


            Not all item actually has serial number on it, but we want

            to use the serial number in the system so that we could distinguish

            between obsolete and new stock for the same part number,

            especially because it has different cost.

            I also setup the product age fields in the inventory table so that

            we could manage to ship the oldest parts.


            I will try to make some more adjustment with the system,

            and will give you the update ASAP.

            Really appreciate for your great input.