11 Replies Latest reply on Mar 28, 2014 1:42 AM by EgbertRoos

    How to get a value from one record of related records in a calculated field?

    EgbertRoos

      Title

      How to get a value from one record of related records in a calculated field?

      Post

           Hi All,

           I have a few questions how to get a specific value from one record in a set of related records I want to use in a calculation in another table.

           Either it is really simple and I am thinking far too complicated or I have set up my database in a way it complicates this issue.

           Set up of the database:

           Departments >--------- Professions -------------< Growth rates

           People of only one profession work at a department. Several departments (of different companies) may inhabit people with the same profession.

           A profession has a certain growth rate (within a region).

           Regions -----< Companies -------< Departments

           A department belongs to a company. Companies are grouped in regions.

           Regions TO ---------< Growth rates

           The growth rate table inhabits both regional growth rates (for each profession) and growth rates that apply nation wide (for each profession). The distinction between these two growth rates is made in a separate field.

           1. What I want in the Department table is a calculated field that presents the region growth rate for a specific profession within a company. Which would give the same growth rate for all departments (with the same profession) of companies in a certain region, but which will vary among a certain profession between various regions.

           2. And in another field I would like to have the growth rates that apply nation wide. These would only vary between different professions and not between regions.

           Any help would be so gladly appreciated.

            

           Egbert Roos

            

            

        • 1. Re: How to get a value from one record of related records in a calculated field?
          philmodjunk

               Sounds like you need some more relationships rather than a complex calculation. The relationships can match by specific region/profession values to access the single correct record in Growth Rates. This can be an explicit relationship specified in Manage | Database | Relationships or, if using FileMaker 12 or newer, the relationship can be specified in an ExecuteSQL functin's SQL query.

               1. You need to match a specific record in Professions to a specific record in Growth Rates to access the single record for that Professions record's region and profession.

               2. You need the same, but for that Profession record's nation, rather than it's region. Question, do you have growth rates for more than one nation or just all those for a single nation? (It can make a difference in how you set up the relationship or SQL query.)

               The trick is to set this up for 1. from the context of Departments, not professions as it appears that a record in department is specific to a region while a record in professions is not.

               Departments-------Growth Rates|ByRegion

               Departments::_fkProfessionID = Growth Rates|byRegion::_fkProfessionID AND
               Departments;:cRegionID = Growth Rates|byRegion::_fkRegionID

               cRegionID would be defined as an unstored calculation field with this expression:

               Regions::__pkRegionID

               to copy the value of this ID from the parent Regions record to which that Department record in linked.

          • 2. Re: How to get a value from one record of related records in a calculated field?
            EgbertRoos

                 I have been experimenting with ExecuteSQL. But I haven't found proper documentation on this function and its possibilities, soo that didn't work out so far.

                 On the single or multiple nations question: I only have a single nation.

                 You're right that a record in Departments is specific to a region while a profession is not.

                 I tried to understand your solution but not sure if I quite follow you.

                 I have about 100 Companies, multiplied with 15 professions gives 1500 departments (max, not all combinations apply). On the other hand I have 13 regions. Combined with the 15 professions this gives 195 regional-professional growth rates. Furthermore 15 (professions) with one national growth crate gives 15 additional growth rates.

                 Wouldn't a one-to-one relation relation Departments------Growth rates|ByRegion create 1500 records for only 210 unique values?

            • 3. Re: How to get a value from one record of related records in a calculated field?
              philmodjunk
                   

                        I have been experimenting with ExecuteSQL. But I haven't found proper documentation on this function and its possibilities,

                   See this document: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf

                   You can find and open this document on FileMaker's web site by selecting "more documentation" from the Product Documentation submenu in FileMaker's Help menu. For the ExecuteSQL function, only the sections that document SELECT queries apply as the other types are not supported in this function.

                   SeedCode also offers a helpful free widget called SQL Explorer.

                   

                        Wouldn't a one-to-one relation relation Departments------Growth rates|ByRegion...

                   Good Catch! You are correct that this is not a one to one relationship it's one growth rate|ByRegion record to many records in Departments (all with the same region and profession Id's). That does not change my recommendation, only my notation.

                   Since you have just one national record in growth rates for each profession, what value in what field identifies a growth rate record as a national record? We can set up a calculation field in either departments or Professions that always returns the same value and then we can set up the same type of relationship with two pairs of match fields so that a given record in either professions or departments matches only to the national growth rate record for that profession.

              • 4. Re: How to get a value from one record of related records in a calculated field?
                EgbertRoos

                     Thanks for pointing me to the SQL documentation.

                     In the growth rate table I have a field which states the validity of the value: either regional or national using these same texts.

                     To complicate things a little further I actually do have 2 sets of national growth rates for each profession (all for the one nation). 1 set defines demographic growth rates (as do BTW the regional values which differ among regions), the other set is what experts estimate growth to be for each profession. To distinguish between demographic and experts growth rates a second field is set up with text values 'demographic' and 'experts'.

                     Furthermore I guess that the way you introduce Departments::cRegionID in a calculated field it doesn't matter that the Company table is in between the Departments and Region tables?

                      

                • 5. Re: How to get a value from one record of related records in a calculated field?
                  philmodjunk
                       

                            ...it doesn't matter that the Company table is in between the Departments and Region tables?

                       What matters is that the relationship from department to company to region is many to one in both cases. That means that referring to the field in Regions from the context of  a record in department matches to only a single record in Regions. With different relationships, or missing records in Companies, this trick will fail to work.

                       Here's a relationship example that matches to only a demographic, national growth rates record for a given department or profession:

                       departments::_fkProfessionID = Growth Rates|DemoNational::_fkProfessionID AND
                       departments::constNational = RegionalType AND
                       departments::constDemo = Growth Rates|DemoNational::NationalType

                       constNational would be a calculation field defined with "National" as it's sole expression and with "Text" selected as the result type. constDemo is similar but with "Demographic" as its expression.

                       regionalType and NationalType are my names for the fields where you distinguish between regional and national as well as demographic and experts records in the Growth Rates table.

                       To set this up using the Professions table would mean that you define the calculation fields in the Professions table and use __pkProfessionID instead of _fkProvessionID as a match field.

                  • 6. Re: How to get a value from one record of related records in a calculated field?
                    EgbertRoos

                         I set up the relationships the way you suggested in order to solve the first question in my original post:

                         

                              Departments-------Growth Rates|ByRegion

                         

                              Departments::_fkProfessionID = Growth Rates|byRegion::_fkProfessionID AND
                              Departments;:cRegionID = Growth Rates|byRegion::_fkRegionID

                         

                              cRegionID would be defined as an unstored calculation field with this expression:

                         

                              Regions::__pkRegionID

                         That indeed provided me with the right values: a field that presents the regional growth rate for a specific profession. Which is great!! 

                         However I didn't succeed in getting the global fields demographic growth rate and experts estimated growth rate for each profession.

                         I find it kinda hard to follow how and why that should be set up in addition to the first solution.

                         I did try the following ExecuteSQL statement in a calculated field in the department table, but that returns only ?'s

                         ExecuteSQL ( "SELECT growth rate 
                         FROM growth rates table
                         WHERE demo-experts-field = Experts " ; "" ; "" )
                          
                         Any idea how to solve this one?
                    • 7. Re: How to get a value from one record of related records in a calculated field?
                      EgbertRoos

                           Update to my previous answer:

                           I finally understood your last suggestion. I made a new TO of the growth rate table to which I defined the relations with Departments the way you suggested. I already had the RegionalType and NationalType fields in the table (albeit under other names) to which I could easily refer.

                           The constNational field and constDemo are currently text fields with a value list with values "regional" and "national" and a list with values "demographic" and "experts". I guess you use calculation fields here in order to get the national and the regional values as well as the demographic and the experts values at he same time in different fields.

                           My solution produces either of these 4 values in one field where I use a Case function to distinguish between the four combinations of the two const fields.

                           BTW the ExecuteSQL function mostly is still mysterious to me. Would like to know how I could have used it here.

                            

                           But I'm most grateful to your help provided so far. 

                           Thanks

                      • 8. Re: How to get a value from one record of related records in a calculated field?
                        philmodjunk
                             There may be other issues also but your field and table names need to be enclosed in double quotes while the literal text should be enclosed in single quotes:
                              
                             ExecuteSQL ( "SELECT \"growth rate\"
                             FROM \"growth rates table\"
                             WHERE \"demo-experts-field\" = 'Experts' " ; "" ; "" )
                              
                             And it doesn't look like you have enough criteria specified in the WHERE clause for ExecuteSQL to return a single value as this will return all records from growth rates table where demo-experts-field = "Experts"--there's nothing to limit it for a particular profession.
                              
                             PS. I don't recommend that you use hyphens in field names. That forces Filemaker to enclose them in {} in your FileMaker calculations--making for hard to read expressions.
                        • 9. Re: How to get a value from one record of related records in a calculated field?
                          EgbertRoos

                               Think I'm almost there. But I didn't succeed in producing the 3 existing values (demo - regional, demo - national, experts - national; the combination experts - regional doesn't exist) in 3 separate fields. Only the first one succeeded by pointing tot the Growth Rates|byRegion table.

                               The second and third one should point to the Growth Rates|DemoNational TO as far as I understand, shouldn't they? But here I have the same problems how to distinguish between either demo or experts values that apply nation wide.

                               In my solution where the results are in one field based on the selection on the 2 const fields this works fine. But using 4 separate const fields for either "regional", "national", "demographic" end "experts" I don't know of a way to exchange two out of these four values between the two tables.

                          • 10. Re: How to get a value from one record of related records in a calculated field?
                            philmodjunk

                                 I assume that this is the "non SQL" method that you are describing.

                                 You need three table occurrences of Growth Rates, one for each different relationship.

                                 departments::constDemo = Growth Rates|DemoNational::NationalType

                                 was my example for limiting the link to demographic growth Rates records. with additional match fields to further limit the matching down to just one record.

                                 departments::constExperts = Growth Rates|DemoNational::NationalType

                                 Would be the pair to include with other match field pairs to match to the "experts" record instead of the "demographics" record.

                                 The fields that start with const, by the way, are calculation fields that have a constant value in all records of the table where it is defined. You could use a global text field if you were sure to always populate it with the correct value for a given relationship.

                            • 11. Re: How to get a value from one record of related records in a calculated field?
                              EgbertRoos

                                   Thanks Phil. I get it now! I wouldn't have been anywhere without your help.

                                   Thank you so much!