2 Replies Latest reply on Jan 27, 2014 8:53 AM by tobiasj

    Unique ID across all tables, best practise?



      Unique ID across all tables, best practise?




           I'm very new to Filemaker Pro 13 (few days into trial), so please bear with me.


           The business rules I work under, dictate that all items in the inventory system must have unique IDs. That is, can't have a Furniture table and a Component table which both have tuples with similar IDs, and the question then is, how do I best accomplish this?


           I have already set up a script to create a 'BaseAsset' record, which autogenerates the ID, and serves as the base for all other tables, linked by the ID. I'm in doubt if this is the way to go, maybe it's prone to errors I can't recognise as a newbie?


           I would prefer to set up a single table for all types of items, but the problem is that they are very different and require different kinds of fields. Since the data validation is performed on basis of the fields in the tables (rather than the fields in the layout), it's very difficult to assert that the right kind of data has been entered (silly to require an entry for voltage for a piece of furniture). But perhaps there are ways in which to validate the data on a per-layout basis, rather than on a per-table basis?


           Hope this makes sense, and thanks for your time :-)



        • 1. Re: Unique ID across all tables, best practise?

               A single unified table should be used for inventory tracking. There'll come a point where you need to generate reports based on your entire inventory and this will become a very difficult to impossible thing to do if you have multiple inventory tables instead of a unified table. Your Base Asset table sounds like the correct way to handle this. Define an auto-entered serial number in it as well as fields that are common to all inventory items.

               Consider this method: I once worked for a company that manufactured or processed both wine corks and bottle caps. These are very different products with very different manufacturing, purchase and quality assurance specs. Yet we had a single unified table of all corks--natural and synthetic, screw caps and the materials used to produce those screw caps as well as other very different products. The DB system handled those differences by having a number of product specific tables for documenting those details that were linked to a central inventory table. If a record in that inventory table was a cork, the match field for cork manufacturing details matched to a record for those details. If it was a record for a screw cap, that match field was empty....

          • 2. Re: Unique ID across all tables, best practise?

                 Thank you PhilModJunk,

                 I can proceed with more confidence now :-)