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.
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.
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.
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?
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.
I only have Filemaker 11 and it doesn't look like that function is in there :/
It's new with FileMaker 12. One of it's key advantages is the option to drastically reduce "Relationship clutter" in Manage | Database.