7 Replies Latest reply on Jan 4, 2013 12:51 PM by philmodjunk

    Calculation Using Multiple Fields in Related Table

    JosefBalles

      Title

      Calculation Using Multiple Fields in Related Table

      Post

           I have table_1 related to table_2 in a one-to-many relationship. Table_1 is an employee profile and table_2 contains records pertaining to each employee. I'd like the field "status" in table_1 to be calculated from values in table_2.

           Each employee has a start date and an end date, recorded in table_2 as separate records with a "date" field and a "record_type" field (record types being either "start date" or "end date").

           I want the "status" field to be filled with "active" if there exists a related record in table_2 with the "record_type" field filled with "start date" and the "status" field to be filled with "terminated" if there exists a related record in table_2 with the "record_type" field filled with "end date" AND with the same record having the "date" field filled with a date that is before or on the current date.

           Right now, I have "status" as a calculated field using the following formula:

           Case ( table_2::record_type = "start date" ; "active" ; table_2::record_type = "end date" and table_2::date =< Get(CurrentDate) ; "terminated" )

           The first case works, but I can't figure out how to get the second one to, so the "status" field is filled with "active" for all records in table_1.

        • 1. Re: Calculation Using Multiple Fields in Related Table
          philmodjunk

               Table_1----<Table_2

               When you refer directly to a a field from a related table such as table_2::record_type you can only access the "first" such related record. The expression can't "see" any other related records nor any data stored in them.

               Are there just two such records (at most) for every employee or are their additional records in table 2?

               If just two, you can specify a sort order in the relationship that makes sure that the "start date" record is always the first related record and the "end date" record is always the last relaated record. This then makes it possible to use the Last function to access the data in the "end date" record in table 2.

               An alternative approach is to use two occurrences of table 2, both linked to table 1, but with an added match field that matches only to either the start or end date records. This second approach may be the better option if you have a lot of other related records in table 2.

               PS. what's the advantage to having two such related records? A single record with start and end date fields in the same record would make accessing this information much simpler.

          • 2. Re: Calculation Using Multiple Fields in Related Table
            JosefBalles

                 Yeah, after doing some searching and fooling around with it I figured out that if the first related record type was "end date" then the second case would be evaluated.

                 There are additional record types in table_2, so for every employee there can be many related records in table_2. I'll try using a second occurrence of table_2 and see how that works.

                 I simplified the actual situation and labels to make it easier to understand and for security reasons. Table_2 contains the significant events of employment for each employee, such as start date and end date, but there can be events before the start date (submission of application, for example) and after the end date (reemployment somewhere else, for example). The point is to be able to track the transactions of each employee in kind of a timeline form.

            • 3. Re: Calculation Using Multiple Fields in Related Table
              philmodjunk

                   YOu can define calculation fields in Table 1 that compute constant text values such as "End Date". You can then use these fields in relationships to Table 2 so that you match by Employee ID AND one of these constant value fields to match to just the start date or end date records.

              • 4. Re: Calculation Using Multiple Fields in Related Table
                JosefBalles

                     Okay, so I got it to work creating two new calculation fields in table_1 with constant text values of "Start Date" and "End Date" and then created two new occurrences of table_2, matched with the Employee ID and one with "End Date" and the other with "Start Date" to record_type. Since the related records are now only start or end dates, I modified the calculation to:

                     Case ( table_2 Start Date::Date ≤ Get (CurrentDate) and table_2 End Date::Date ≥ Get (CurrentDate) ; "Active" ; table_2 End Date::Date < Get (CurrentDate) ; "Terminated" )

                     This just seems really chunky. I'd like to be able to do this with more parts of the database, but I feel like it will clutter my relationship graph and tables with extraneous occurrences and fields. Is there a cleaner way to do this by chance?

                • 5. Re: Calculation Using Multiple Fields in Related Table
                  philmodjunk

                       If you are using FileMaker 12, you can use the ExecuteSQL function to access this data without needing either added relationship nor the const calculation fields.

                  • 6. Re: Calculation Using Multiple Fields in Related Table
                    JosefBalles

                         I only have Filemaker 11 and it doesn't look like that function is in there :/

                    • 7. Re: Calculation Using Multiple Fields in Related Table
                      philmodjunk

                           It's new with FileMaker 12. One of it's key advantages is the option to drastically reduce "Relationship clutter" in Manage | Database.