8 Replies Latest reply on Jul 3, 2012 9:33 AM by philmodjunk

    Auto-Generated Composite Key? Not sure.

    MichaelLawrence

      Title

      Auto-Generated Composite Key? Not sure.

      Post

      My project is a very simple customer/transaction database. I have 3 tables (Customer, Transactions, ItemFromTransaction). I've autogenerated a serial/primary key for the first two tables (CustomerNumber & TransactionNumber) and set the relationships up appropriately.

      There are some transactions that will have a single item, and some that will have upwards of 20 items per transaction. Instead of creating my Transactions table with 30 blank item lines, I created the third table ItemFromTransaction. This seemed the best way to design the table. The issue I'm having is that I'm used to other programs and I'm not sure if there is an option to designate a composite key for my third table. The auto-generated field for the ItemFromTransaction table is the ItemNumber field, but it will not be unique because there will always be an ItemNumber value of '1' (or more) for each TransactionNumber.

      I know there has to be a step I'm missing to make the count for ItemNumber reset for each transaction, but I'm not sure what it is. Is there a calculation I'm supposed to use instead? Any help would be appreciated....

      database.jpg

        • 1. Re: Auto-Generated Composite Key? Not sure.
          philmodjunk

          If you had such a composite key--which is possible, how would you use it?

          Such a key is rarely needed in a FileMaker database.

          Presumably you want to combine the customerNumber with the TransactionNumber.

          If you defined a text field in ItemFromTransaction with an auto entered calculation such as:

          TransactionTable::CustomerNumber & "|" & TransactionNumber

          You'd get such a composite key, but note that you can refer back to data in Transaction and Customer from a layout based on ItemFromTransaction without using such a key.

          • 2. Re: Auto-Generated Composite Key? Not sure.
            MichaelLawrence

            You know I think I might have been mistaken about the composite key being necessary now that I've looked back over my project. I'm just so used to having to set some form of a primary key in every table. My larger issue is still reseting the auto-generating field with each new transaction...

            • 3. Re: Auto-Generated Composite Key? Not sure.
              philmodjunk

              If you need a primary key for the third table, it can also be an auto-entered serial number. It's fairly easy to add such to an existing table in FileMaker, should the need arise:

              1) Add the field and specify the auto-entered serial number field option.

              2) Put the field on a layout based on the same table, select Show All Records, put the cursor in the field and use Replace Field Contents with the serial numbers option to put a serial number into every existing record. Be sure to specify the "update entry options" option so that your next serial value automatically updates to be one larger than the largest serial number assigned by this operation.

              My larger issue is still reseting the auto-generating field with each new transaction

              That shouldn't be necessary. Can you describe in more detail what you mean by that?

              • 4. Re: Auto-Generated Composite Key? Not sure.
                MichaelLawrence

                This database is tracking customers and their transactions. Each transaction can have more than one item sold, so the ItemNumber field in the ItemFromTransaction table is meant to track that. I want it so that ItemNumber '1' from TransactionNumber '1' is the first item of that transaction, ItemNumber '2' is the second item of that transaction, and so on.

                I'm entering sample data to test my database and I've entered 3 items for the first transaction. I've moved onto entering the second sample transaction, and instead of the first item listed in the second transaction having an ItemNumber of '1' it's listed as ItemNumber '4'.

                I realize this will make the ItemNumber field not unique but I think this is the best way to tackle my problem, as each transaction could be a single item or upwards of twenty.

                • 5. Re: Auto-Generated Composite Key? Not sure.

                  IF you are using Filemaker 12 consider using get(uuid) which generates a very long string that 'probably' will never be duplicated and is quite useful when you have remote, non-connected users. Serial numbers don't cut it in these situations.

                  Your Mother file would have its own uuid for each record. Each related child record would have its own uuid and store the mother's uuid for linking. The same with the grand child which has its own uuid for identifcation and can have a linking field for both the Mother file and the child file.

                  Your use of transaction is often called an Invoice with the other file being the Line Items. Transaction would then refer to the action of creating the invoice and line items or a purchase order and line items, etc. 

                  I prefer to forget what the tables are being used for since no matter what someone calls them the rules and design are the same.

                  An invoice, purchase order, library checkout, etc. all have the same table structures and links and rules. Learn one and you've learned them all.

                  • 6. Re: Auto-Generated Composite Key? Not sure.
                    philmodjunk

                    That 'probably' makes me nervous. I want my primary keys to be absolutely 100% unique, no "probablies" allowed! When I read that description of Get (UUID), I stopped recommending it as an option for primary keys.

                    Why do you need the numbering to start over with 1 for each order? This is not a primary key as it cannot uniquely identify records in the table.

                    If you must have the number, here's one way to get it. Define a field, constOne and define it to auto-enter a 1. You can do this by entering 1 in the data box on the auto-enter tab of field options for this field.

                    Define a second field sCounter as a summary field and define it to compute a running total of constOne. It won't make any difference for your portal, but if you use the options for restarting the running total and specify TransactionNumber as your "group by" field, you can sort your records by transaction number and get a list of ItemsFromTransaction that number sequentially, restarting with each change in transaction number.

                    • 7. Re: Auto-Generated Composite Key? Not sure.
                      MichaelLawrence

                      I'd just like each transaction to have a list of items per transaction is all.

                      • 8. Re: Auto-Generated Composite Key? Not sure.
                        philmodjunk

                        Which you have in the ItemsFromTransaction table.

                        You may want to look at this demo file that illustrates the basic tables for invoicing: http://fmforums.com/forum/showpost.php?post/309136/

                        It uses this table/relationship design:

                        Customers----<Invoices-----<LineItems>-----Products

                        Note the similarities to your design. You have all but the final table, products.