1 Reply Latest reply on Feb 23, 2012 12:26 PM by philmodjunk

    New User - Serial # and related table issues - Inventory System



      New User - Serial # and related table issues - Inventory System




      I am new to databases and FileMaker, but things are progressing.  I am stumbling on one issue.  I am attempting to create a inventory system.  I want to be able to load all inventory types in my main table, but want to determine the serial number per category.  For example I have "buckles" and "jewelry" as two different categories and want to have each have its own increasing numbers.  I want J-1 and B-1.  I unerstand this needs to be two fields.  I thought about setting up seperate tables for each category and that is where the product serial numbers would be created.  Then it would filter back to the main table, but that is not working.  I cant get things to filter that way.

      I am used to Excel and that is where this data is being transferred from.  I have over 2,000 things inventoried this way so I need this to work.


      Any thoughts or links to tutorials is much appreciated.

        • 1. Re: New User - Serial # and related table issues - Inventory System

          First, I strongly recommend that you do not use this serial number in relationships to other tables. If you need it to support an existing system, use a field for storing this data, but use another standard serial number to uniquely identify all items regardless of type to use in relational links to other tables. You can then use the value in reports, for searches and sorts without risking problems with your data integrity.

          The separate tables approach is possible to set up, but not very practical unless you have only a few inventory types and the types aren't subject to frequent changes. You don't want to have to add several hundred tables just to manage the serial numbers and you don't want to be adding new tables on a regular basis just to adapt to changes in your product lines.

          If you decide you must use such a type based serial number, I suggest a self-join relationship based on type with an auto-enter calculation to enter the next number in the series for that type with a unique values validation rule to catch any cases where two users might create new inventory records of the same type at nearly the same time and then get identical serial numbers in two different records.

          Inventory::Type = InventorySameType::Type

          In Manage | Database | relationships, make a new table occurrence of Inventory by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box--such as InventorySameType like I've done here.

          We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

          Define separate fields for the full ID (J-1) and just the number (1). The full ID can be a calculation field such as:

          Left ( Type ; 1 ) & "-" & TypeBasedSerialNumber

          Then your auto-enter calculation for TypeBasedSerialNumber can be Max ( InventorySameType::TypeBasedSerialNumber ) + 1

          to set up an auto-entered calculation, find and double click the field in Manage | Database | Fields to bring up Field Options. Click the auto-enter tab. Select the calculation option.

          There are some additional picky details needed in order for this to work:

          On the same auto enter tab for this field:

             Enter 0 in the Data box.

            Clear the "do not replace existing value..." check box

            If you create these records inside a portal, add an OnObjectExit script trigger to the portal to run this one line script: Commit Record

            Inside the specify calculation dialog where you enter the calculation, clear the "do not replace if all referenced fields are empty" check box