4 Replies Latest reply on Jan 14, 2014 8:50 AM by evanscl

    Concatenate Fields from within a Related Record

    evanscl

      Title

      Concatenate Fields from within a Related Record

      Post

           I have a relationship with a parent, child, and grandchild record.

           The parent record, "Collection Site", has a field with a 'county code'.

           The grandchild record has two fields, 'family code' and 'pool number'.

           I want to concatenate 'county code' & 'family code' & 'pool number' in a field called 'SpecimenIDNumber'.

           The calculation below works, but only for the first grandchild record. After the first grandchild record, only 'family code' and 'pool number' fields will concatenate.

      SpecimenIDNumber = CollectionSite::SC County Code & FamilyCode & PoolNumber

           How do I make the calculation work for all of the records in the grandchild table?

      FMscreenshot.jpg

        • 1. Re: Concatenate Fields from within a Related Record
          philmodjunk
               

                    After the first grandchild record, only 'family code' and 'pool number' fields will concatenate.

               

          SpecimenIDNumber = CollectionSite::SC County Code & FamilyCode & PoolNumber

               If TestInfo is your grandchild table, something is wrong with your relationships as this should then work correctly for every grandchild record. If TestInfo is not the grandchild table, that's the problem. This calculation needs to be defined in the grand child table.

               But in many cases, such a calculation field is not actually needed. There are other ways to combine the value of these three fields on a layout that do not require a calculation field so you really only need it if you have to export that value as a single value in the file of exported data.

          • 2. Re: Concatenate Fields from within a Related Record
            evanscl

                 A screenshot shows the relationships I have between the parent, child, and grandchild tables.

                 The relationship is as follows:

                 Location_ID_pkey > Location_ID_fkey

                                               > TrapInfo_ID_pkey > TrapInfo_ID_fkey

                 Basically, these are all auto-enter serial numbers that join Parent Key to Child Foreign Key and Child Parent Key to Grandchild Foreign Key. If this relationship is incorrect, please let me know how to correct the relationship.

                 Thank you!!

            • 3. Re: Concatenate Fields from within a Related Record
              philmodjunk

                   Then if this is working only for the "first grandchild record" then the subsequent records in TestInfo are missing a value in the fkey field to link back to the needed Parent record. I would inspect the TrapInfo_ID_fkey field in the records where this calculation is not working.

              • 4. Re: Concatenate Fields from within a Related Record
                evanscl

                     You're correct. A colleague adjusted the auto serial numbers yesterday when we were trying to get something else to work. The fkeys are no longer matching the parent keys. Thank you.