2 Replies Latest reply on Jun 12, 2011 4:48 PM by frankn

    Trial version ... question re "compound keys"



      Trial version ... question re "compound keys"


      I am using the trail version of FileMaker Pro Advanced to see if it can handle a specific requirement we have.  I have used MS Access before, and am also fairly conversant with DB concepts.  I want for several of the tables I want to create to assign compound keys, i.e., if I have a field called Customer and another called Effective Date I want the key to the file to be a combination of both fields.  Some cases it will be 2 fields, in some cases 3 or 4.  How do I do this? 


      Related question ... I will have some transaction tables where, for example with carrying on the same concept, I want the user to enter the customer and the transaction date and then have the entry screen pull the rate field from the customer table ... so for example, if I have customer ABC with two records ... one with a date of May 1 2009 and one with a date of June 1 2009 - if they enter a transaction if May it should use the rate from the May 1 table record and if after June 1 from the June 1 table record.


      Any help would be appreciated.  Could not find out about the compound keys by a forum search of the help / tutorial info, and the second question is just my way of avoiding searching and playing for 2 hours!





        • 1. Re: Trial version ... question re "compound keys"

          Compound keys used to be the only way to match multiple pairs of fields in a single relationship. You define a stored calculation field that returns text with an expression such as:

          customerIDfield & " " & EffectiveDateField. You'd match this to a simple text field in the daughter table if you plan to use the relationship to create the related records or a similar calculation field if you did not plan to use the relationship in this way.


          Now we have a way that is usually better. We simply match up multiple pairs of fields in the same relationship.

          Enter manage | Database | relationship

          Drag from the customer ID field to its matching ID field in a different table. Now drag from the effective date field to the matching date field in the other relationship.


          You've now based your relationship on multiple pairs of fields. To see more relationship details, double-click the relationship line linking the two tables.


          The only draw back to this approach is that sometimes you get very confusing relationship graphs if you have a number of these relationships with some key fields in common.

          • 2. Re: Trial version ... question re "compound keys"


            In a similar vein, maybe someone will be so helpful as to suggest a way to make a 'Compound Key' that is unique, The above suggestion would work, but, does not provide a uniqueness.

            in my case, the unique key should consist of :

            Year (really just a number 1 - 6)

            Param1 (number)

            Param2 (Text from lookup table)

            Other stuff : eg


            value ... etc