3 Replies Latest reply on Jun 12, 2014 1:31 PM by philmodjunk

    Key Field Q: How to/ Multi part inventory number?

    longhairdontcare

      Title

      Key Field Q: How to/ Multi part inventory number?

      Post

           I'm creating a custom database for an art gallery and am stuck figuring out how to do the following:

           The gallery assigns the following format to each artwork's Inventory #: Artist Initials, Category, Number

           For example: SSPT001 (Simon Says, Painting, 001)

           For now, I have a three-digit serial number generated on creation as the key field. How do I add the first two parts of the inventory number (initials and category) ? Also important to note that there are several categories and the serial numbers need to reflect the next value for that category AND I will be importing data from excel files which already have inventory numbers. 

           How do I proceed?

            

           Thank you FM forum!cool

        • 1. Re: Key Field Q: How to/ Multi part inventory number?
          philmodjunk

               I would not use this as a "key" field. you'll need it to keep your users happy and to support a "legacy" system of labeling inventory, but don't use the field a  primary key for use in relationships. Just store this value in a field in the Artwork table so that it can be used for labels, searches and sorts.

               That said, this demo file shows how to generate different number sequences for different sub groups of records in a table: https://www.dropbox.com/s/ggjy88cgkescopy/CategorySerialNos.fp7

               It's in the older file format so users with FileMaker 12 or newer will need to use Open from FileMaker's File menu to produce and open a copy of this file converted to the newer file format.

          • 2. Re: Key Field Q: How to/ Multi part inventory number?
            longhairdontcare

                 Thanks @PhilModJunk! 

                 Very helpful. What do you recommend the key field be if not inventory number in order to relate to rolodex table, invoice table, etc?

                  

                 Thanks for the help!

                  

                 LHDCcool

            • 3. Re: Key Field Q: How to/ Multi part inventory number?
              philmodjunk

                   98% of the time, the best option is a number field set up to auto-enter a serial number each time a record is created in that table. Sometimes--especially if you use iOS devices each with a separate copy of the database, it makes more sense to use a text field with Get ( UUID ) as the auto-entered ID value.

                   Such a field is called a "Primary Key" and should be the most often used field to match to records in related tables.

                   The ideal primary key is:

                   1) always unique

                   2) never, ever changed once assigned to a new record

                   3) devoid of any additional "encoded meaning"

                   4) have values assigned via the most simple and bullet proof fashion possible.

                   The IDs like those you've mentioned in this thread fail criteria 3 and 4.