10 Replies Latest reply on Aug 8, 2011 12:18 PM by PaulLawrence

    Relationships in database



      Relationships in database


      I've created the following database with a join table.

      I wanted the join table to accumulate employee vs. date information.

      I wanted to be able to view the dates associated with individual employees. (Works fine.)

      I wanted to be able to view the employees associated with any date. (Works with some problems.)

      To  make it work, I chose to create the Date table with unique primary key that is the date.


      This relationship with the date table seems unwieldy. Am I making a rookie mistake?

      Do you have a suggestion for a better way to handle the dates in the join table without having this separate Date table for the relationship?


        • 1. Re: Relationships in database

          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?

          • 2. Re: Relationships in database

            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.

            • 3. Re: Relationships in database

              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.

              • 4. Re: Relationships in database

                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?

                • 5. Re: Relationships in database

                  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.

                  • 6. Re: Relationships in database

                    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.

                    • 7. Re: Relationships in database

                      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.

                      • 8. Re: Relationships in database

                        Dang! I even understand that! It just didn't occur to me to use another table that already had a relationship.

                        Thanks, Phil.

                        I'll get busy and try the different scenarios.

                        • 9. Re: Relationships in database

                          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.

                          • 10. Re: Relationships in database

                            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!