I have a table containing names of cat caretakers called CARETAKERS. I have a table called CLINICS which contains calendar dates for surgery clinics. Each caretaker selects a date to take their cats to a clinic. As time goes on, a caretaker may select a second or third... Clinic. When I look at the caretaker form, I want to see the clinics this caretaker has used. When I view the clinic form for each date, I want to see which caretakers signed on for each clinic. Somehow can't get the linking to make this happen. Any clues from you tech helpers.
If I understand you correctly, you need a third table.
1 - CARETAKERS (Caretaker info)
2 - CLINICS (Clinic info)
3 - e.g. VISITS
with relationships to CARETAKERS and CLINICS and other fields you need e.g. date, cost, outcome etc.
Create your records to VISITS table and select Caretaker and a Clinic.
I hope it helps
Just to add to what Nick has said, you may find my recent blog post (Relating to Relational Databases - Part 2), which examines many to many relationships, useful:
Thosbryan - the data model you want to build will contain three tables - two parents and a child. The Parent (or Master) tables are Clinics and Caretakers. The third table is a child table called Appointments which is a child to Caretakers. The reason for the Parent/Child relationship is that without caretakers there would be no appointments. Also, this relationship will make it very easy for you to see all Appointments per Caretaker on a caretaker-by-caretaker basis. You can create this Parent/Child relationship by creating the Caretaker table first, then the Appts table. When creating Appts, add a link to Caretakers and turn the Component option to YES. This tells Ninox that the appointments are a sub-table (Child Table) to the Parent Caretakers. And finally, add a link to Clinics so, for each appointment, you can select the clinic where the appointment will happen.
Once you've done this, then add a date and time field to the Appointments table so that you can keep track of not only which clinic the appointment is at, but the date and time.
Now, with this structure in place, you can open up a record in Clinics and you will see all appointments that took place there. If you use the [Edit Fields] button in the linked table view, you will be able to also add in the name of the caretaker who made the appointment. This will not require any code as the Caretaker and Appointment are already connected through the Parent/Child relationship.
And finally, anytime you view a record in the Caretaker table in form view, you'll see the embedded child table listing all appointments, including the date, time, and name of clinic automatically. The schema I've described is show below.
To learn more about the Parent/Child relationship as well as the Cross-Reference table function (the Appointments Table in the above schema cross-references Caretakers and Clinics), refer to the users manual at www.ninoxus.com and check out the section on "Working with Tables" and the sub-section therein "Tables & Uses"
That worked great. Thank you for the tip.
Glad it workd Thos. Make sure to check out www.ninoxus.com for more Ninox tools, tips and templates.