1 Reply Latest reply on Oct 16, 2013 11:29 AM by philmodjunk

    Assets Inventory Table vs. Assets Used Table; One or Two Tables?

    illumine

      Title

      Assets Inventory Table vs. Assets Used Table; One or Two Tables?

      Post

           Thanks ahead of time for any guidance I may receive to this question.

      BACKGROUND: I have an inventory DB with a table of consumable commodities on-hand.  It contains nearly 1,000 records, some solitary and unique, others multiples of the same item.

      DESIRED OUTCOME: I want to start tracking events when the commodities are taken from storage and used up.  In my mind I would create a separate "consumables used" table and completely move a record from the "on-hand" table to the "used" table when that particular item is used, thereby reducing the number of items remaining in the "on-hand" table and increasing the number of items in the used table.

      PREVIOUS RESEARCH ON THE MATTER: I am aware that I could use a value-listed drop-down field to discriminate between "on-hand" and "used" and leaving all records in the same single table, but is that the best way to handle the problem?

           I have read several posts here and other places and see that an Import Records command is potentially useful in this regard. I also see that a script using multiple variables (1 per field to be moved from table 1 to table 2) is also possible but less elegant, perhaps.

      QUESTIONS:

           1. Should I employ 2 related tables to do the job?  (Or, would a Join Table be necessary, as well, making it three?)

           2.  If two tables (or three), what's the best way to physically move an entire record, basically, from one table to another?

           Many thanks!

        • 1. Re: Assets Inventory Table vs. Assets Used Table; One or Two Tables?
          philmodjunk

               Moving records like this from one table to another is not a good idea. There are much safer/simpler options that will do what you need.

               1) I don't recommend a single table. You may even end up with more than two in the final result, but you'll need at least two, but not as you have imagined.

               2) well moving entire records from one table to another is usually not the best solution and there are better options for what you want.

               Inventory-----<Transactions

               You have one record in Inventory for each item or group of identical items. Each time some event increases or decreases inventory, you record that event in a new record in Transactions. Summary totals that add up all transactions that increased inventory for a given item and that subtract out all transactions that decreased the count compute the current "on hand" value.

               The Inventory starter solution that comes with FileMaker 12 illustrates a simple version of this approach.

               This thread, which takes this forward into more complex applications of the same method, may also be of interest to you: Managing Inventory using a Transactions Ledger