Setting up and relating one table to each table in a group of 3 related tables

[BrainlessBlock]

Hello,

This is a question I'm not sure where to ask on this forum, and I'm unsure of how to approach the issue, being fairly new to FileMaker.

I have a table of Contacts and some of those contacts need to visit our office every few weeks on a specific day at a specific time. Say we have 200 Contacts and 5 need to visit us at 9-10 on Monday of Week 1, 5 at 10-11, 5 at 11-12, and the same for Tuesday etc. They will repeat this same appointment after all the appointments have been looped through...

On the layout for each contact I want to give an option to set both the Week, the Day, and the Time they will visit. I want the available weeks, days, and times to be changeable from their respective tables. So admin can decide to add a third week or remove Friday or whatever. My question is how can I setup the relationships between these to best do this?

 

I have the Weeks/Days/Times setup as such, and that works great. But when I attempt to relate the Contacts::week_id to the Week table, the Contacts::day_id, to the Day table, etc...it has to create a new instance of the Week/Day/Time tables.

My goal is to have all the people I need appointments with assigned, and be able to view how many are assigned to each appointment time, day, and week. And then later on be able to automatically assign someone an appointment time with the lowest number of appointments on that time.

I'm unsure how the best way to approach this would be--hopefully the above made even a grain of sense. I greatly appreciate the help.

[comment]

How many contacts can your office see at the same time?

 

[BrainlessBlock]

3 minutes ago, comment said:

How many contacts can your office see at the same time?

 

It could be anywhere from 0 - 50 at a time.

[comment]

52 minutes ago, BrainlessBlock said:

anywhere from 0 - 50 at a time.

Then you have a many-to-many relationship between contacts and time-slots (assuming that a contact can visit more than once) and you should resolve it using a join table of Appointments.

I am not sure why you need the Week and the Day tables; you don't seem to have any fields that would record some attributes that describe them. I believe they could be simply fields in the TimeSlots table - so eventually your core structure would be:

Contacts --<  Appointments >-- TimeSlots

 

1 hour ago, BrainlessBlock said:

But when I attempt to relate the Contacts::week_id to the Week table, the Contacts::day_id, to the Day table, etc...it has to create a new instance of the Week/Day/Time tables.

If you want to have conditional value lists (first select a week, then a day in the selected week, then a time slot in the selected day), you will have to define some auxiliary relationships in addition to the core ones described above - and these will necessitate creating additional occurrences of some of your tables on the relationships graph. This is perfectly normal and not to be avoided.

 

Edited September 30, 2020 by comment