8 Replies Latest reply on Aug 11, 2010 8:51 AM by Shraddha

    Primary key with more than one fields



      Primary key with more than one fields


      I am new to the file maker community .

      I wish to make combination of two fields in a table as primary key ( unique ) .

      I have SalesTable (Header) and SalesLines (Lines) .

      SalesTable has SalesId as primary key (unique) .

      SalesLines table has SalesId as a foriegn key and another field LineNum (Line number) .

       In SalesLines, I  wish to

      1. Make the LineNum field auto entered from number 1 for a SalesId e.g.

      SalesId           LineNum

      SO001                 1

      SO001                 2

      SO002                 1

      SO002                 2

      SO003                 3

      Line number once consumed e.g. in case a user deletes a sales line will not be used again for the SalesLines of that sales order i.e. if in above example if a user deletes the record for SalesId SO002  and lineNum 2 then LineNum 2 will not be created again for SalesId SO002

      2. Make combination of SalesId and LineNum  as unique for SalesLines.

      Pls help.


        • 1. Re: Primary key with more than one fields

          While there are other uses for such a numbering method, using it as a primary key for your LineNum table is not a good idea. A separate serial number field defined in the LineNum table will be much less troublesome to implement and use.

          The main purpose of a primary key is to label each record with a unique identifier and this does not require an approach like you describe to accomplish.

          What problem would such a numbering approach solve for you?

          • 2. Re: Primary key with more than one fields


            Thanks for picking this post to help.

            The way  i have described my table structure above has following purposes

            a. For referencing purposes : I can then find a record with a combination of SalesId and the LineNum. This would be of help particularly when there are multiple lines of an Item in SalesLines for a SalesId.

            b. I have another planned structure to populate another Table "SalesLinesDelete" If a user deletes a record in a SalesLines. This way I can quickly identify which line numbers were deleted by a user.

            Pls tell me which other uses you were mentioning in your last reply for this post. May be there is something more which i have not thought and would be of help.



            • 3. Re: Primary key with more than one fields

              Neither a nor b require this numbering strategy to be a "primary key"

              You can also meet both requirements with an auto-entered serial number in your LineNum table.

              a) since a serial number field will increase in value with each new record and is unique, you don't need sequenctial numbering to refer to a specific item in your list of items. If you want the display to look better, you can use the @@ layout text in a portal or list view of the records to number them in sequential order strictly for display purposes.

              b) instead of deleting such a record, I'd set a flag in other field that omitted it from any relationships that linked it to a specific SalesTable invoice while preserving all information--including the SalesID for auditing purposes. This even enables you to "undo" a deletion just by returning the flag field to it's original value.

              Here's an example of what I mean:

              Define a calculation field, cValidFlag, in SalesTable that returns the value 1 in all cases. Define a number field, ValidFlag in SalesLines. Set up your relationship like this:

              SalesTable::SalesID = SalesLines::SalesID AND
              SalesTable::cValidFlag = SalesLines::ValidFlag

              If you enable "Allow creation of related records via this relationship" for SalesLines in the above relationship and use a portal to SalesLines on your SalesTable layout to record each item sold, a 1 will be automatically entered into ValidFlag with each new line entered in the portal. If you use a different layout design, you'll need to auto-enter the 1 most likely.

              In either case, "deleting" a sales line may be done simply by changing the value of ValidFlag to some value other than one and this preserves the entire record for review or to undo the delete if necessary.

              • 4. Re: Primary key with more than one fields


                I am a newbie and have a slightly similar situation though not exactly same. I need to have a Unique Key (not primary in the table).  I have a packet table  (id, name, comments etc) ) and a packet parts table ( that has the id from the packet table  and the part_name, quantity etc).  I need to make sure that the part_name is not duplicated in the table. I need to make Packet_id and Part_name combination to be unique.  I created a calculated field (thanks to another firum post)  that stores packet_Id&"|"&part_name  and marked it as unique.  When creating the layout however it forces me to expose this field. It works.  Is there any way to prevent exposing this field?


                • 5. Re: Primary key with more than one fields

                  @ PhilModJunk

                  Thanx for your suggestion but i have a following query, what is the difference between

                  I am using filemaker v10.0

                  a) making a primary key between the combination of 2 fields (If it is possible at all in filemaker)

                  b) making the combination of 2 fields unique and how to achieve this in filemaker.

                  • 6. Re: Primary key with more than one fields

                    Primary keys are used to uniquely identify a record and link it to the foreign key field of any related records in the same or a different table. You can set things up like you describe, but you aren't using them as a "key" to link a record to other records and this is a good thing as best practice is to keep primary keys meaningless and hidden from the user. This avoids situations where some one wants to change the "meaning" attached to the primary key by modifying the format--which in turn can break the connection to any related records.

                    Any number of unique identifiers can be set up in a table and then used to sort and/or search the data or for placement on reports where they serve to help users work with the data--just don't use them as the primary key in a relationship.

                    What you want can be set up, I advise against it as it can be difficult to impossible to make sure you don't get a duplicate value when two users interact with the same records at the same time. If you still want to implement such a numbering system, you can use the following approach:

                    This method assumes the use of a portal with "allow creation of records..." enabled for the portal records in your relationshp graph.

                    Define a calculation field in SalesTable, cNextLineNumber as Max ( SalesLines::LineNumberField ) + 1

                    Set an OnObjectSave or OnObjectExit script trigger on every data field in the portal where you can edit data to perform the following script:

                    If [ not IsEmpty ( LineItems::InvoiceID ) ] 
                        If [ IsEmpty (LineItems::LineNumber ) ] 
                            Set Variable [ $Row; Value:Get ( ActivePortalRowNumber ) ] 
                            Commit Records/Requests 
                            Go to Portal Row [ $Row ] [ No dialog ] 
                            Set Field [ LineItems::LineNumber; Invoices::cNextLineNumber ] 
                        End If
                    End If

                    • 7. Re: Primary key with more than one fields


                      Probably the simplest approach is to hide the field. You can use Inspector | Data tab | Behavior to keep users from accidentally entering the field by clearing the find and browse mode check boxes.

                      One "Hide" trick is to use this conditional format expression on the field:

                      Get ( WindowMode ) < 4

                      and select a text color that matches the layout background for the conditional format.

                      The field is then invisible in browse, preview and print modes but is visible in layout mode so you can see it's there when working with the layout.

                      • 8. Re: Primary key with more than one fields

                        Hi Phil

                        Thanks.  This certainly is an elegant solution.