2 Replies Latest reply on Mar 29, 2016 1:54 PM by Mike_Mitchell

    Unique Compound Key

    TKnTexas

      I receive data from a system.  It comes in the form of a spreadsheet.  There are two key columns of Data:  Client and Agent.

       

      There might be multiple records for Client "A".  But Client "A" can only have one agent type.  Adding a new client is not too difficult.  That gets flagged to me separate from the report.  However, a Client may add a new agent type.  So before I import the report to the activity table, I would like to update the Parent Table, to pick these new agents up. 

       

      Should I concatenate the information in Excel first to create the key?  Or can FileMaker Pro determine uniqueness from a compound key?

        • 1. Re: Unique Compound Key
          erolst

          TKnTexas wrote:

          Or can FileMaker Pro determine uniqueness from a compound key?

           

          You cannot validate a calc field, but you can create a text field with the appropriate auto-enter option, say, 

           

          field1 & field2

           

          If you validate this field as unique, records whose concatenated field contents constitute a duplicate as per this calculation will not be imported.

          • 2. Re: Unique Compound Key
            Mike_Mitchell

            Another option is to import the data as is, and then use a self-joining relationship with both fields as join predicates. In this scenario, if field X (which can be any field you know to be unique) is not equal to selfJoin::field X, then you have a duplicate. (Or, alternatively, if Count ( selfJoin::field X ) > 1.)