4 Replies Latest reply on Dec 17, 2010 10:16 AM by pdoak_1

    Retrieving information from a related table

    pdoak_1

      Title

      Retrieving information from a related table

      Post

      Background Info: I am using the following

      1. Filemaker pro Advanced 11
      2. Mac OS X 10.6.5
      3. Single user mode
      4. I have been using Filemaker Pro for a couple of months

      I have the following three tables:

      • Accountstatus - shows client account balances by date
      • ClientAccounts - shows client accounts, client IDs
      • ContactTable - shows client information by Client IDs i.e. name and address

      A client ID can have multiple accounts associated with that ID

      I have set up the following relaitonships:

      Accountstatus:ClientAccount = ClientAccounts:ClientAccount

      ClientAccounts:ClientID = ContactTable:ClientID

      I have produced a report from the AccountStatus table and wish to retrieve the corresponding information regarding a client account from the ContactTable.  The only information that I am trying to retrieve is the client name for that account but although it the layout produces some names it does not show all the names although I know them to be in the ContactTable.  Why would that be?

        • 1. Re: Retrieving information from a related table
          philmodjunk

          Is there one and only one contactTable record that links to any given ClientAccounts Record or are there multiple contacts? Assuming multiple, do you want all of them in your report? (That requires either a portal, or a calculation field that uses the List function to pull all the data from ContactTable into a single field.)

          If that's not the case, keep in mind that no data from ContactTable can appear on your AccountStatus layout unless there is also a record in ClientAccounts that matches both a record in your found set for Accountstatus which also matches a record in ContactTable. If that record is missing, or there's something not quite right in any of the match fields that link Accountstatus to clientaccounts to contactTable, you won't see any data.

          • 2. Re: Retrieving information from a related table
            pdoak_1

            There is one and only one contactTable record that links to any given ClientAccount record.  For the Client names which are missing, I have checked that there is indeed a record in ClientAccounts which matches a record in the AccountStatus table and the ContactTable.  I have checked this by showing the client account information from the ClientAccount table on the layout.  Is there anything else which could be causing this?

            • 3. Re: Retrieving information from a related table
              philmodjunk

              Not that I can see from what you've posted so far.

              Putting fields from ClientAccount on your accountStatus based layout only checks half of the linkage From Client Account to ContactTable.

              If you take a layout based on ClientAccount, put fields from ContactTable on it, then find the same records in ClientAccount that you are referencing from AccountStatus, do you see all of the data?

              If that seems to work, you might click Edit Post on your original message and use the upload button to upload a screen shot of your relationship graph for us to take a look at.

              • 4. Re: Retrieving information from a related table
                pdoak_1

                Thanks for the advice.  There was a problem with the data in the ContactTable.  I have now rectified it and it works.