I have to ask you some things:
1) How many tables have you now ?
2) A patient can have, in a given date, more than one illness... isn't true ?
3) A patient can have, during his life, more than one illness... are you interested only to the last one ?
1) I have one table for this database. It happens to have 40 fields defined.
2) True, but I was just giving that as an example. For this situation, let's assume that a name of a patient in field 1 is always paired with a descriptive term in field 2.
3). same as answer 2) above.
I haven't really worked with "tables." Might that be a clue?
I tried exploring the interface for creating a script, but didn't get far.
Another thing I'm curious to figure out is relating the contents of a field to a subset of possibilities in another field. This would be as if one patient might have a specific number of possible conditions.
Welcome to the wonderful world of databases. You're going to have fun!
Before we get your specific request working, some preliminaries are necessary.
>Does it require a script? If so, what might I do?
No. Scripting is generally not the first thing to reach for, but more used for automating things once you have the basics working.
>I tried exploring the "relationships" menu but couldn't figure out how to use it or whether it was relevant.
It will be relevant, but first you need some tables to relate.
I haven't really worked with "tables." Might that be a clue?
Absolutely! Big clue. At the moment, you don't have a database, just one table. Fine for just storing notes, but not for setting up a complete 'solution' as you are trying to do. You are on the right track by wanting to relate one field to another, but we need those fields to be in different tables.
So, first thing to do is split up your data into separate tables. You have given a perfect example to begin with: you mentioned 'patients' and 'illnesses', so make those the names of 2 different tables in your database, then in each table only put in fields that are relevant to that table.
Once you have done that, come back to us, and we can give you the next step.
Thanks. You got me started.
I have done what you requested, and I gratefully await your further instructions!
Summary: -- what I have done so far:
I created two different tables.
One table has the Patients.
One table has the Illnesses.
By the way, each of these fields consists of a field label, as well as a Pop-up List, that contains a list of preset values that I have created (either the Patients or the Illnesses), as well as the ability to edit the list to allow adding additional items as they come.
I did not create any actual records yet.
Tell me if I am on the right track with what I then tried to do:
I went to the menu File > Define > Database.
I then clicked the "Relationships" tab, and tried to set up a relationship between Patients and Illnesses. Now I'm at a wall of the unknown. How can I set up a relationship between specific Patients and their accompanying Illnesses?
Good start. But now go take all those items OUT of the pop-up value lists, and put them in the appropriate tables where they belong! Then you can edit your value lists to display the field from the table instead of from a custom value list. And we don't want pop-up lists YET, so change the field control style back to 'Edit Box'.
Oh yeah, if you haven't done so, make sure that the patients have an ID or serial number that is unique to each patient. You could also number the illnesses, or just make each illness name unique, or maybe an abreviated illness title would be appropriate as an identifier? (VD, FLU, MONO?!?!?)
My next move would then be to create a third table, perhaps called Cases, or whatever works for you. This table will just need a minimum of 2 fields: Patient ID and Illness name. This is the table that will start to tie together a patient and an illness. This is the table that you can set each fields control style to pop-up list.
Does that all make sense?
I will try to follow your steps, one by one. It sort of makes sense. It will make more sense once I start in on it, which will have to wait until my head is clear enough, which may not be until tomorrow. I'll definitely give it a go and let you know where I am at.
As for the file, that is a great way to show me,
however, for some reason, I get an error message when I try and open the file. The message says that the file "...was not created by FileMaker or is severely damaged and cannot be opened." I get this same message with other files I saw to examine on the site. Could it be because I am using a Mac? Or because my program is Filemaker Developer? Any other ideas of how to pass an actual file my way? Another option would be for me to email you what I have so far. I already have an existing "patient" roster as well as "illness" roster. In fact, I have been creating my "database" (as I have been calling it) for about a year now. It has been used as a way for me to log notes for each session I have with a client. Of course, I would change everything so that patient confidentiality would be maintained.
I'm back. I created a file along the lines of your instructions so far. Take a look at this file:
If you are still with me:
Is this correct in any respects so far?
I already notice problems, perhaps due to the fact that I have kept my original table layout (to show you the entire context of this project) along with its fields and defined lists.
Sometimes the fields seem "ok," and sometimes the links between the field name and the field seem to disappear, and appear differently (with two colons :: before the field).
I'm willing to follow any instructions you may have to offer at this point.
If you are still unable to open Scott's file, here is some information that may help your understanding.
Many patients can have the same illness, and many illnesses can have the same patient. This is called a many-to-many relationship, so you will need to create a third table.
In your Patients table, create a new field "PatientID" (text result). In your Illnesses table, create a new field "IllnessID (text result). Now, create a third table, "Diagnosis" (or whatever makes sense to you) and create the following fields:
Date of visit (Date)
You may have some other fields, but this should get you started.
Now, click on the Relationships tab and draw a line from the Patients table "PatientID" field to the "PatientID" field in the "Diagnosis" table. You will notice both fields now pop up to the top of the graphical representation of the tables. Do the same for the "IllnessID" field from the Illnesses table to the "IllnessID" field of the "Diagnosis" table.
Once finished, exit out of the Database and go to Browse Mode.
Switch to the layout "Diagnosis", and you should see the four fields we just created.
Pull down the View menu and select Layout. Just below the oval tool on the left side of the screen is the portal tool. Click on the icon for the portal tool and draw a box on the layout. When FileMaker prompts you for the table, select the Patients table. Include the last name and first name only at this time. You can add fields later.
Create another portal, but this time select the Illnesses table and include only the type of illness field.
Go to browse, and add a new record. Enter a patient ID, and you should see the last name and first name appear in the Patients portal. Enter an illness ID, and you should see the type of illness in the Illnesses portal. Enter a date and any notes for this visit.
Now, switch to the Patients layout, and create a portal. Have it show information from the "Diagnosis" table (Date, Notes) AND the "Illnesses" table (type of illness). As more records are added to the "Diagnosis" table, you will have a history of visits for each patient.
I know there is a lot of information here, so take it slowly. If you need clarification for any of the above steps, please let me know.
I have your file and nearly have it staightened out. Once you see how I have set it up, I think you will get a much better idea of how to proceed.
It's really a matter of making a useful model of the real world. Your world has staff, clients, case files, session appointments, bills. So each of these gets its own table. Then you work out who 'owns' how many of what; these are the relationships. Each practitioner owns many cases, each case file may own many sessions, each client may own more than one problem, possibly seeing a different practioner for different problems.
A database really begins with a pen and paper, writing down everything that you can think of that you might want to keep track of, and putting all these things into logical groupings. Then you can draw links between the groups to show who owns what. Much easier to have it on paper in front of you before transfering these ideas to FileMaker. So if you can think of anything else, just let me know.
I'll get my attempt up ASAP.
Thank you for outlining the steps for me to take.
For my particular situation, each patient will only be assigned one illness.
But I will keep in mind the notion of the many-to-many relationship.
I will take my time and I'll give a holler if I need more help.