What does one record in Clinical represent? I thought maybe one record for each day until you indicated that you wanted to see different dates for a given patient.
The date is in the CheckIn/Out table. Then, I set a script to create a new record in Clinical table. It means that One patient record has many records in CheckIn/Out and CheckIn/Out has one record in Clinical.
I'm sorry but that makes no sense to me when I read it.
Why do you need the clinical table?
And your relationship does not match what you have just posted. It indicates that for one record in clinical, there could be many records in checkIn/Out. If there is only one record in clinical for any one record in checkin/out, there is no need for the clinical table as far as I can tell from what you have posted.
The table structure goes like this:
Patient::pkpatientid = CheckIn/Out::fkpatientid CheckIn/Out::fkclinicalid = Clinical::pkclinicalid
WHich is a classic many to many relationship between patient and Clinical but is not consistent with "CheckIn/Out has one record in Clinical" and does not answer the key question:
What is the purpose of the Clinical table?
In the clinical table, there are treatments, charting and other clinical things. It's for managing the patient's treatment detail for one appointment. The CheckInOut table stores the patient's check in and appointments history. I've attached the relationship screenshot.
You may find that this relationship has advantages:
PatientCheckInOut::__pkInOutID = PatientClinical::_fkInOutID so that you can use multiple records to document the clinical details of a given patient visit.
Since your relationship between clinical and checkin/out currently is one to one there is one and only one record from that table that you can display unless you define a new relationship that matches by different data. (This would also be true of the other relationship that I am suggesting may be useful.)
PatientClinical::anyField X AllPatientCheckInOut::anyfield
With a portal filter that filters to one specific _fkPatientID
PatientClinical::_fkPatientID = AllPatientCheckInOut::_fkPatientID
Either makes it possible to list all PatientCheckInOut records for one patient in a portal to AllPatientCheckInOut on the PatientClinical layout. Both require adding an new ocurrence of PatientCheckInOut to your relationship graph.
Which table should I assign this layout? And which field so I need to filter?
you indicated that you needed this on a layout based on PatientClinical.
A portal filter on example 1 would filter for a specific patientId in the AllPatientcheckInOut table occurrence.