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.
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.
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.
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.