3 Replies Latest reply on Jul 23, 2012 10:04 AM by philmodjunk

    related to a related field, ad infinitum



      related to a related field, ad infinitum


      I have a company table, in which there is a company id field and a parent company id field (which is represented in another instance of the company table).

      I am able to display both the company and its parent company successfully, but there are cases in which a parent company will have a parent company, and this can occur multiple times.  What I would like is to display the company I'm dealing with, and whatever parent company is the top-level parent company (whether that's 1 or 4 steps away).  Does that make sense?

      Thanks very much for any help.

        • 1. Re: related to a related field, ad infinitum

          If you have this:


          Do you just want to show companies A and D? Or do you want to also list company B and C?

          Is "four levels" just a "for instance"? What if you get a chain of 5, 6 or more?

          Do you have FileMaker advanced? (a custom function might work to recursively parse back up the relationship)

          Not sure that will work, but we can definitely write a script that chases up the chain and either returns just the end company or a list of all that make up the chain.

          • 2. Re: related to a related field, ad infinitum


            In your example I would want to show companies A and D.  Four is just an example, there may be more or less.  I do have Filemaker advanced, yes.

            • 3. Re: related to a related field, ad infinitum

              Method 1 (has an upper limit but does not require a script or custom function)

              Define a chain of table occurrences like this:


              Company::ParentCompID = ParentCompany1::CompanyID
              ParentCompany1::ParentCompID = ParentCompany2::CompanyID
              And so forth...

              RightValues ( List ( ParentCompany1::CompanyName ; ParentCompany2::Company ; ..and so forth ; ParentComapny6::Company ) ; 1 )

              would then return the ultimate parent company, but you are limited to the number of table occurrences you create and then include in the List function.

              Method 2 (no upper limit, but requires a script)

              use this relationship that you already have:


              #Script starts from layout based on Company table occurrence
                 Exit Loop If [ IsEmpty ( ParentCompany::companyID ) ]
                 Go To Related Record [Show only related records; From table: ParentCompany; Using layout: "ParentCompany" (ParentCompany)]
              End Loop
              If [Get ( LayoutName ) = "ParentCompany" //there was a parent company ]
                 Set Variable [$UltimateParentID ; ParentCompany::CompanyID ]
                 Go to Layout [original layout]
                 Set Field [Company::UltimateParentID ; $UltimateParentID ]
              End If

              And I'd add one more occurrence that links by UltimateParentID to access data from the "UltimateParent" company.

              Please note that method 2 requires running this script on any "child" company records should there be a change in company ownership or the data will no longer be accurate.