Mutliple Options

[Annette Marie]

Ok....so I'm not sure if this could be done or if so how to even begin. Basically I have a database with client info on it that I need to record a diagnosis for each client. A client CAN have up to three diagnosis'. What is the best way to enter them? Having seperate fields for each diagnosis ie. Diagnosis 1, Diagnosis2, etc? Where the complication comes in, I need to be able to search for any given combination of diagnosis' so I don't know where to begin to get it to do that as well. I'm very new to filemaker, this is the first database I have ever tried to make with it, and only my second database ever (The first wasn't too great )

Thanks in advance for any help anyone can offer.

[comment]

Use two tables, Clients and Diagnoses, related by ClientID.

[Annette Marie]

Ok...so would I still use three individual fields? Diagnosis 1, Diagnosis 2, etc?

And if a client has lets say ADHD and ASD, if I needed to do a search of all clients who have a diagnosis of ADHD AND ASD how would I get those results, regardless of which field 1, 2, or 3 they entered each into?

[comment]

No, you would have a separate record for each diagnosis.

[Annette Marie]

So you're saying that I do have to do what I was going to, which is create three separate fields, diagnosis 1, diagnosis 2, and diagnosis 3. Which is fine. I'll make a table for Diagnosis', but, if I needed to search for all clients who have ADHD and ASD how would I go about doing so?

[comment]

I am afraid I wasn't successful at getting my point across: there will be only one diagnosis FIELD. There will be a RECORD for each diagnosis, with fields for ClientID and Diagnosis (and perhaps other fields describing the diagnosis, such as date, diagnostician, etc.).

if I needed to search for all clients who have ADHD and ASD how would I go about doing so?

Do you mean "clients who have ADHD or ASD" or clients that have both?

[Annette Marie]

Clients that have both. A client can have more than one issue/diagnosis, so that's why I'll need to record multiple for each client and be able to search for any combination of those diagnosis'

[comment]

Here's one way: enter Find mode, type ADHD into the related Diagnosis field* and perform the find. Enter Find mode again, type ASD into the same field - then select Constrain Found Set from the Requests menu.

---

(*) This field would be shown in a portal to the Diagnoses table on the Clients layout.

[Annette Marie]

OK.....sorry for the delay in getting back to you. Basically, this is what I've done.

I created a table that has the Client ID on it and Diagnosis. I added the relationship from this table to the Clients table by the client ID.

I also have a value list of diagnosis'.

I then put a portal on my layout, with the diagnosis table fields on it. I have given myself three rows.

I can then enter more than one diagnosis for a client.

Now.........when I go to find, I did what was said above, hit find, selected one diagnosis, hit new request, selected the other diagnosis, requests, constrain. It gives me the results that are diagnosis 1 OR diagnosis 2. How do I get results for diagnosis 1 AND diagnosis 2. I'm looking for clients that have BOTH diagnosis' I'm searching for, not one or the other along with other things not reuested.

[comment]

I did what was said above, hit find, selected one diagnosis, hit new request, selected the other diagnosis, requests, constrain.

That's not what was suggested above. You should enter Find mode, selected one diagnosis and perform the find. Then enter Find mode again, select the other diagnosis and constrain the found set.

It's a bit complicated for a manual search, but it could be scripted.

Alternatively, you could add a calculation field to the Clients table =

List ( Diagnoses::Diagnosis )

Then enter both diagnoses as the search criteria into this field (one request, two words). This may be simpler for the user searching manually, but it will also be slower.