1 Reply Latest reply on Aug 22, 2014 6:32 AM by philmodjunk

    Concatenate Lookup

    EricWang

      Title

      Concatenate Lookup

      Post

           Hello,

           I am fairly new to filemaker pro so please excuse my ignorance on the matter. I'm making a mouse colony database and I was wondering how to concatenate a lookup.

           I have a table (Mouse Records), with the fields Allele 1, Allele 2, Allele 3, Allele 4. 

           I have another table (Breeding Records) with the field "Genotype".  

           I want to set up a lookup where I enter the Animal ID in the breeding records , and it takes information from the Mouse Records from Allele 1, Allele 2, Allele 3, Allele 4, and concatenates them into the Genotype field in the breeding records table. 

        • 1. Re: Concatenate Lookup
          philmodjunk

               This can be an unstored calculation field or a text field with an auto-enter calculation. Either way, these three options concatenate the contents of these fields in different ways:

               MouseRecords::allele 1 & ", " & MouseRecords::allele 2 & //and so forth...

               List (MouseRecords::allele 1 ; MouseRecords::allele 2 ; MouseRecords::allele 3 ;MouseRecords::allele 4 )

               Substitute (List (MouseRecords::allele 1 ; MouseRecords::allele 2 ; MouseRecords::allele 3 ;MouseRecords::allele 4 ) ; ¶ ; ", " )

               The second option puts a return between each value to list things vertically, the last is a better option than the first for listing with a comma space between each value if some of the fields may be blank in some records.

               But you can also just display the four fields as merge text on your Breeding Record layout and show them as concatenated with no added field needed.