Repeating an auto-entered serial number in records
I am creating a medical database. I have a Patients Table and a Visits Table. I will enter many visits on separate dates for each patient. The two tables are related by the Patient ID using an “equal to” function.
The Visits table with several fields is my main layout and holds the 3-4 fields from the Patient Table too. Records are created by Visit, not by Patient. So every time I type in a Patient ID (lets say 121), in my main layout (Visits table), if a visit record has been previously entered for Patient 121 in the database, then the few remaining fields from the Patient Table like Date of Birth, Sex, Ethnicity fill up automatically. Then, I continue filling data unique to that visit like the date of visit, chief complaint, Name of attending, medication etc.
1) Based on previous advice on this forum (thanks Phil!), I want to create an auto-entered serial number for Patient ID (using filemaker pro 11) I already have a Visit ID being created by an auto-entered serial number, validated always, field never empty, always unique. If PatientID is auto –entered, I will have difficulties in my main layout. Eg- I may want to type 121 as an ID several times to enter a new visit record and to have certain fields fill automatically.
2) The Patients table (which exists but which I do not use) shows several blank records with only the Patient ID filled in. I assume this is because of the multiple records associated with one Patient ID in the main layout- Visits table. Is there any way around this? It does not really get in my way because when I convert my main layout (Visits table) to Excel, all the data appears properly. This is the ultimate purpose of my database- to use collected data it for statistical work in Excel.