For a many to many relationship between employee and Date, you've implemented what looks like the classic, textbook implementation of a join table.
If you place a portal to WorkDetail on your Date layout, you can add fields from Employee to the this portal to display a list of the employee records linked to that date record.
What problems are you encountering?
Thanks for your response, Phil.
Not really having a problem, but it just seems wierd that my Date table is basically a calendar without ALL of the dates available to choose from a drop down or value list.
I suppose I could pre-load the Date table with every date for the conceivable future ... ;) That just seems weird.
What other fields do you need to define in the date table?
If you only need the date and ID fields, you can dispense with this table and replace it with a field of type date defined in the join table.
At this time, I don't have any other fields for the Date table in the specs.
I do have a Date field in the join table who's calculated value is = to the Date from the date table.
I just couldn't figure out how to show Employee records in a portal on a layout with the context of the Date table.
I have to be able to list employees linked to any date in the join table.
I suppose I would have to create a Found Set of employees based on the Date, all within the join table?
Yes, a found set of join table records with a specified date is one option. A portal to the join table that filters by a selected date is another.
That's where my skill falls off ...
What would be the context of the "... portal to the join table that filters by a selected date ..."? Employee table?
That sounds like something I might be able to do.
The context can be from any table you want. The one you select could be part of the process of filtering out records or not depending on how you set it up.
From the gate layout, you can add a filter to AllWorkDetail and use this Portal Filter expression in Portal Setup... to filter the list of records in WorkDetail to show only those for a specific date:
AllWorkDetail::Date = gate::Date
You'd need an additional relationship for the portal:
AllWorkDetail::anyfield X Gate::anyField (with x operator, you can select any field you want from both tables and you get the same result.)
AllWorkDetail is a new occurrence of WorkDetail created by clicking it and then clicking the button with two green plus signs. You can add fields from Empolyee to this portal to list the employee name and other data from that field.
To specify a date, you enter or select a date in the Gate::Date field and use a script trigger to perform this script:
Refresh Window [Flush Cached Join Results]
To force the portal to update with the new date.
A field such as Gate::Date is often set up as a global field so that different users can use the same field at the same time to filter by a different date.
This requires FileMaker 11, but you can also use this relationship: AllWorkDetail::Date = gate::Date in any recent version of FileMaker, get the same list of records and not need a script to update the portal. It will be a much more limited "match" however. Given the number of other tables here, you may want to use a portal filter with a more complex expression to restrict the records listed by more than just the date.
Dang! I even understand that! It just didn't occur to me to use another table that already had a relationship.
I'll get busy and try the different scenarios.
Actually, I created a NEW relationship between existing tables using a new table occurrence to keep it from changing any design elements based on the current relationship.
Yes, I got that and thought of it as very clever.
I was typing "out loud" how I could actually change relationships to approach this while including your suggestions. I'm still at a very early stage. (As if you couldn't tell! <GRIN> )
Good stuff! Thanks!