6 Replies Latest reply on Aug 22, 2013 9:24 AM by philmodjunk

    how to get a calculation to check all related records

    PamelaKenley

      Title

      how to get a calculation to check all related records

      Post

           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.

      Thank you, 

           Pamela 

        • 1. Re: how to get a calculation to check all related records
          philmodjunk

               Yes, when you directly reference a field from a related table--either on a layout or in a calculation, you get the value from the "first" related record.

               To get what you want requires a different approach.

               Here's one option:

               Put a portal to contacts on your company layout. Add a portal filter expression: Contact Data::Main Contact = "Yes" and now this portal will list all related contacts where Main Contact = "yes". You can then duplicate this portal object and edit the portal filter expression for each portal to specify different criteria for what related contacts are permitted to appear in the portal. The portal can be reduced to a single portal row and the appearance of the portal can be made invisible by selecting appropriate fill and border settings to make this look like just a single field on your table.

          • 2. Re: how to get a calculation to check all related records
            PamelaKenley

                 Looks like that is only in filemaker 11 and up. I am using FP10, guess I should have mentioned that. Any other way to get my main contact's' name from contact data to the profile layout? And all of the other contact types and names?

            • 3. Re: how to get a calculation to check all related records
              philmodjunk

                   Yes. If you don't specify the version of FileMaker that you are using, you risk getting solutions that won't work for you--wasting your time and that of the person suggesting a solution.

                   There are a number of ways to get what you need. But since you are using an older version of FileMaker, they require creating additional relationships in the relationships graph for this. I am assuming that one and only one contact is supposed to be selected as the "main contact" or for any of the other cases that you have here. If there could be multiple contacts with the same dsignation for a given company, then the following method is not the right one to use:

                   Define a field in company as: _fkMainContactID

                   Link it to a new Tutorial: What are Table Occurrences? of contacts like this:

                   Contacts|MainContact::__pkContactID = Companies::_fkMainContactID

                   Format _fkMainContactID with a value list of Contact ID's and names from Contacts (it can be a conditional value list of just those contacts linked to the current company) and use it to select the contact that should be designated as the main contact.

                   Now you can add fields from Contacts|MainContact to your layout to show the contact info for the specified record from Contacts.

              • 4. Re: how to get a calculation to check all related records
                philmodjunk

                     Another option is to us a portal to contacts to list all of your contacts for that company and use a single field with a single drop down list to designate each contact as "Main Contact" or some other designation as needed.

                • 5. Re: how to get a calculation to check all related records
                  PamelaKenley

                       Ouch! Sorry, but it has been a while since I have had to ask a question and it slipped my mind to put version in. 

                       On the other hand, I downloaded the trial version of 12 so that I could test the first answer, and am very happy to report that it worked very nicely. So, thank you, I appreciate your assistance. The other two options you gave, I tried, but they just werent working...probably me...not your instruction. So, again, thank you for trying. 

                  • 6. Re: how to get a calculation to check all related records
                    philmodjunk

                         IF you want to get either of the other options to work for you, feel free to post a follow up question with an exact description of what you set up and where it went wrong for you.