AnsweredAssumed Answered

Appointments and join tables, very newb to this

Question asked by dmajorjam on Jul 2, 2018
Latest reply on Jul 2, 2018 by dmajorjam

I am working to create a solution that allows for appointment creation as an agent between providers and requestors. Requestors create requests or call in requests, agent pools list and shows for providers to choose from. Guests are the end user getting the service on behalf of the requestors. Guests profiles need to be created to avoid errors on the part of schedulers within the agency.


I am new (three weeks) to Access, Filemaker (two weeks all School of YouTube learning), . Working to ensure I have distinctions between join tables, portals, and forms understood and am approaching storing, adding records and creating workable UIs for Schedulers, Requestors, and Service Providers correctly. Working to create tables with atomic data but still fuzzy on what qualifies (See organizations, campuses, buildings, floors Portion).


I want to store the information for the created appointment in an "encounter" table, that schedulers and requestors can create/update/duplicate and search for appointments from.


I want schedulers to be able to input the location of the appointment (connected to a specific organization) with:  time start, time end, total time and date. As well as guest info (tied to UUID for said guest) that can be pulled up from a guest table and/or saved to same table upon appointment creation of encounter. Maybe the only guest info in the Encounter form is Guest Number which is tied to guest table?


I am anticipating working with requestors who have multiple campuses, as well as multiple buildings on said campuses with multiple floors (Hospitals). What is the best way to visualize these relationships? Do you know of any resources to help solidify this thinking?


I have created an organizational profile table with foreign key attached to facilities table which includes ( Org name {foreign key}, Campus Name,  Facility/building Name, Street address, city, state, zip, Floor, department) Floor and department are on a different floor specific table with (Org name {foreign key, Facility Name foreign key,


I have created a guest table, separate from people table due to HIPPA possible appointments. (not sure what foreign key would be applicable)


I am not sure if there are join tables needed that bring the three sets of info together. I am not sure if I am asking this question correctly. Any help or direction to workable solutions is appreciated.


Guests cannot be organizations or Employees, if they somehow became employees they would still need separate id's as guests, and table specific information is very different, in regards to or guests and employees.