how to get a calculation to check all related records
I have a database with a Companies Data table and a Contacts Data table with a one to many relationship from Companies to Contacts.
In the Contacts Data table, there are several contact types, each with a yes/no radio button.
In the Companies Data table, I have a field for each contact type (such as "main") with the calculation as follows:
Main = If ( Contact Data::Main Contact = "Yes" ; Contact Data::Contact Full Name ; "" )
On the Company Data Layout, I have a Contacts Portal that shows all related Contacts, but not their contact type. I also have a Company Profile Layout that has the individual Contact Type Fields where I need this calculation to place the contact name that goes with the contact type.
Apparently, the calculation only looks at the first record in contacts but I need it to look at all related contact records to return the correct contact name for each contact type.
I would appreciate any help I can get on this problem as I have tried everything I can think of to get it to work and so far...no go.