3 Replies Latest reply on Jan 19, 2014 8:16 PM by philmodjunk

    Using a repeating field as a key to a relation

    PieroF

      Title

      Using a repeating field as a key to a relation

      Post

           Hi, I have a question about the use of repeating fields as a key for a relation.

            

           Given a relation using a repeating field as a key (KeyA) in the source table (A), the evaluation of the contents of a second repeating field in A (NamesA - to be filled by the related field NameB in table B) seems to depend on the way NamesA is defined:

           a. If NamesA is a calculation repeating fieldonly its first repetition is evaluated (all others are left empty)

           b. If NamesA is a standard repeating field with calculated data entryall its repetitions are evaluated, but all equal to the first one

           c. Only if NamesA is a standard repeating field with table lookup data entry all its repetitions are evaluated as expected, i.e. each gets the value from the related field NameB in table B, based on the corresponding repetition in KeyA.

            

           My question is: is there a way to build calculation fields as in cases a. or b. in order to get the same results of case c. (lookup), which seems to me most consistent with the relation system in FMP ?

            

           I have an example ready if necessary.

            

           Thanks

            

           Piero

        • 1. Re: Using a repeating field as a key to a relation
          philmodjunk

               You should not use a repeating field for this purpose. Repeating fields were originally designed as a way to get things done when FileMaker (version 2.5) was a flat file system. Since the release of FileMaker 3, what you describe is much more simply implemented by replacing your set of repeating fields with a table of related records where each related record takes the place of one repetition in your repeating fields. Since the related table does not have multiple repetitions, the relationship to the look up table is now a simple, straight forward relationship between a single match field in the related table and a single match field in the look up table.

               They even added a special tool--still functional in today's versions that makes moving data from repeating fields into such a table very simple to do.

               If you want to see a working example of what I am describing, open the Invoices Starter solution in FileMaker 11, 12, or 13 and note how a table to a related table on the Invoices layout is used for recording each item purchased on that invoice, with a price that is looked up from a products table.

          • 2. Re: Using a repeating field as a key to a relation
            PieroF

                 Thanks for your answer.

                 I see what you mean and certainly I agree with you about the best way to handle that case. My data base is currently handling similar situations (many to many relations) in the right way, and I should convert also those repeating fields to a number of adequate tables, as you suggest. I was just wondering why the behavior is so different between 3 mechanisms that I expected to return the same result. Your answer is clear: don't bother finding a rationale and don't expect a fix, since they reflect the evolution of FileMaker; and when I started using twenty plus years ago it was in fact handling only flat files.

            • 3. Re: Using a repeating field as a key to a relation
              philmodjunk

                   Repeating fields and also a text field with multiple values entered, separated by returns both match values by a different set of rules than a single field with a single value. The values match by an "or" logic where any one of the listed values may match to the value in the match field of the related table.

                   If repetition 1 = "Apple" and Repetition 2 = "Orange". Your relationship will match to records with "Apple" OR "Orange" in the match field of the related table. If you put "Apple" and "Orange" in the same non-repeating text field, separated by a return, you get the same results.