2 Replies Latest reply on Oct 13, 2016 11:05 AM by pcuezze

    Dr Time Entry under current Contract with a Facility


      I am new to databases and filemaker so forgive me in advance.  I am building an app whereby a Doctor can enter daily time spent working for a

      Facility.  The rate and maximum allowed hours are governed by the current (active) contract with the facility.  The interface should allow the Dr to select the facility from the drop down and then enter his time.


      What I can't get my head around is the relationship between the Contract, Facility, and the Billing Entry table.


      I have 4 tables - Dr., Contract, Facility and Billing entry.


      The Billing Entry is a join-table for Contract and Dr.


      But what do I do with the Facility Table?  If I join it with the Contract (there can be many contracts per facility but only one facility per contract).  I have it set up that way now, but can't figure out how to let the user select a Facility in the Billing Entry layout.


      Restated - I want the user to be able to select a facility and for the database to automatically utilize (or at least allow selection) the Contract associated with that facility.


      I spent the past couple weeks watching the Lynda beginner course and the relational course and thought I had a good grip on this basic step.  But then reality happened. 


      Many thanks in advance for your assistance and patience.  If I need to attach a screenshot, just let me know.


      Patrick Cuezze

        • 1. Re: Dr Time Entry under current Contract with a Facility

          I would recommend starting off with what is called an "Ancor Bouy" set up so that every layout is based on a primary table occurrence (on the left) and related data comes from the table occurrences on the right.  You can capture the doctor who signs in and store his ID so that you can automatically add them to the time entry.  If he then selects a facility (and there is only one contract per facility per doctor that is active at any point) then you can find the correct contract for them automatically via the relationship to "billingentries_Contracts~byFacility_Doctor" (see the image below) 

          Screenshot 2016-10-12 15.08.40.png

          1 of 1 people found this helpful
          • 2. Re: Dr Time Entry under current Contract with a Facility

            This is great.  Thank you.  This Anchor Buoy concept taught me a ton and solved my problem in the process.  Glad I decided to ask for help!