Automatic "Pop-up" menu list

[drzagar]

Can anyone tell me how to do the following:

 

I am creating a medical records database. The first Field contains all the medical tests that can be ordered (about 25). East test is associated with separate diagnoses that are contained in separate field containing Value Lists of those diagnoses that I have created. In other words, each specific test has its own set of diagnoses related to it and it alone.

 

The doctor has asked that when a specific "Test" is selected in the first Field (from the "Pop up Menu), that the associated "Diagnosis" Value List (formatted in another "Pop up" menu) automatically open for selection. Any suggestions. Thanks so much.

 

Dr. Louis Alan Zagar

[LingoJango]

IF I'm following your approach correctly, you need a Diagnoses table, a Tests table (since different tests will probably have different types of output you'll want to record) and a DiagnosisTests join table (since presumably you would run a blood panel for a number of possible diagnoses). Your join table only needs to have three fields: kprime_DiagnosisTestsID, kf_DiagnosisID, kf_TestID. Everything else can be calculated, unless you want specific cut-off points to be highlighted for specific diagnoses.

 

Link those three tables to each other the obvious way. This created a many-to-many relationship that is reliable.

 

Once you've set that up, read this thread and I think you'll get it.

 

---

 

Tell me to mind my own business, but I'm not sure that's the way I would tackle it. I've probably been watching too much "House"! smiley-smile Or maybe I've misunderstood your approach completely.

[drzagar]

HI there:

 

Well, I think I may not have asked the correct question. Let me try again:

 

In this database, there are just two data fields, the first one being a field that contains a value list of about 25 standard medical tests. No problem in setting that up with a standard pop-up Value list for selecting the appropriate medical test (only one is selected in this field).

 

In the second field, the value list must contain a separate group of diagnoses for EACH test selected in the first field (25 Tests and each Test has is own set of diagnoses from which only one is to be selected)

 

Here is the problem:Once the above test is selected, in a second field, I need to have a pop up list with a value list that has all the diagnoses (formatted in a simple "check box) for the specific test selected in the first field.

 

In other words, for example, a test called a CBC is selected. In the next field, a pop up list should popup showing ONLY the diagnoses for the CBC test (one of which will be selected). Of course, there are about 25 different diagnoses lists (one list for each test). Am I being any clearer, here?

 

If this helps please ANYONE let me know what to do here. I just need to know how to set up the fields to accomplish this hopefully simple task. MANY THANKS.

 

Dr. Louis Alan Zagar

[LingoJango]

One more question. Will you be needing to assign values to the diagnoses, or is the diagnosis itself a value?

 

I'm having a hard time envisaging what is needed, because I can't imagine a CBC giving just one of 20 or even one of 200 possible diagnoses.

 

What you want here is called a filtered value list; you can search this forum for the term and you will find plenty of explanations of how it's done. The crucial point here is getting the table structure right for your needs; I could easily lead you on a wild goose chase with assumptions.

 

Do you have a working database that you're trying to improve or are you working from scratch? If the former, it might help to know how it's structured. If the latter, you want to think hard about what you want to build before you start building it. You will get lots of help from this forum, but nothing can replace your knowledge of how your business works and what the possibilities are.

[drzagar]

Hi again:

 

MANY thanks for your response. I think your term "filtered value list" is honing in our my needs. This office deals with Rheumatological diseases (arthritis and other auto-immune situations so a CBC (complete blood count) points to only 6 actual diagnoses that are directly related to a Rheumatological problem.

 

If I follow your logical suggestion, a filtered value list would contain a complete listing of ALL diagnoses used in this office, but once a specific Test is selected in the first field, that selection automatically filters out all diagnoses that do not associate themselves with the specific test selected. Right so far?

 

I will search the forum to see how this is done unless you have a down and dirty, quicker suggestion. I am all ears!!!

 

Dr. Zagar

[LingoJango]

Actually, the second list would give you no options whatsoever until you picked a value in the first list.

 

Look at the value list dialog, where you can "use values from field". If you click on the Specify button you will see that you can either "include all values" or just include "related values starting from". This is why you need a relationship, and the relationship has to be properly defined. Once that's in place, the value list is easy-peasy.

 

So I think you need a table called Diagnoses, each with its own ID (use an auto-enter serial number)

rheumatoid arthritis

systemic lupus erythematosus

polymyositis

...

 

a table of tests, each with its own ID (auto-enter serial number, again)

CBC

ANA

ANCA

PTT

...

 

and a join table, in which the fields are JoinID, kf_DiagnosisID and kf_TestID. You may need more fields there eventually, for anything that pertains specifically to how the diagnosis relates to the test (e.g. a threshold value), but we'll ignore that for now.

 

In the relationship diagram, drag kf_DiagnosisID to the DiagnosisID field in the Diagnosis table. Drag the kf_TestID field over to the TestID field in the Tests table. If you get it wrong, click on the equal sign, click on the relationship, select the right pair of fields and click on "change". Allow creation of records in the join table, not in the other two tables.

 

In your Diagnosis layout, have a portal displaying records from the join table. Use unfiltered value lists to enable the user to select which tests go with which diagnoses. Take advantage of the possibility of displaying a second field while actually entering data from the first: you want your value list to be based on the ID field but to display the name field, which makes much more sense. Every time the user clicks in the last row they will be able to add a new test to the current diagnosis.

 

Then, wherever else you're planning to use the value list, make sure you have a relationship to the join table, so you can do the filtering.

 

More tomorrow, if no one else pitches in and my day job doesn't encroach too much. smiley-smile

[drzagar]

HI there, again:

 

I recently asked about setting up a database that does the following:

 

1. In one field ("Medical Test"), a single selection is made from a pop up value list of about 25 medical tests.

2. In another field ("Diagnosis"), I need to have a specific value list pop up that is only related to the medical test selected in the first field (I believe a term called "filtered value list" is in play here). (It should be noted that each medical test from the first field has a specific value list of diagnoses).

 

That is all I need at this point:

 

1. How do I set up the above database.

2. How do I create a "relationship" to satisfy the above requirments?

3. How do I set up a "filtered value list" that can function as I mentioned above.

 

If someone can provide be with a simple, "first do this, then do this, etc." it would be most appreciated. Many thanks.

 

Dr. Zagar

[doughemi]

Sorry, my example didn't work. I will correct it and repost.

 

--Doug

[doughemi]

OK, now I got my mind right:

 

Assuming you want to relate this information to a patient.

 

Create a table named patients. Create fields required for patient data (name, address, etc) and be sure to define a field patientID which has an auto-entered serial number (click options on the field definition screen, click the auto-enter tab, click serial number). It will also need fields named Test and Diagnosis (both text fields that we will modify later)

 

Create a table named Tests. It needs fields testID (autoentered serial number), test name, and whatever additional information you want to have about each test. You will create one record for each test.

 

Create a table named Diagnosis. It will contain a diagnosisID (autoentered serial number), a diagTestID, and a diagnosisDescription. You will create one record for each diagnosis and test combination (if diagnosis A can be indicated by test 1 and test 2, you will have one record for Diagnosis = A and diagTestID = 1, and another for Diagnosis = A and diagTestID = 2)

 

Now create the relationships. Go to File->Define->Database and click the Relationships tab. You will see a relationship diagram with boxes for each of the tables.

 

Drag from Test in the Patients table to TestName in the Tests table. You will see a line with a small box with an = inside. This creates a relationship between patients::test and all the tests that match in the Tests table

 

Drag from the TestID field in the test table to the diagTestID field in the diagnosis table. Leave the operator as = . This creates a relationship between the value the TestID field in Tests and all records with a matching diagTestID value. Click OK.

 

Go to File->Define->Value Lists. Create a new value list named Tests and select "Use values from Field". Click Select Field and choose the Test name field in the Tests table. Make sure that "Use all values" is selected.

 

Create another value list "Diagnoses" using values from field diagTestID. Select "Use only related values starting from" and select Patients.

 

Go to layout mode and select your patients layout. Double click the Test field and select "Display as Dropdown List" and "Display values from Tests"

 

Select the Diagnosis field. Format it as a dropdown list displaying values from Diagnoses.

 

And there you are.

 

To have more than one test per patient, additional fields and tables would be necessary (you would then need a many-to-many relationship between patients and tests, which would require a join table), but this gets you to the basic function.

 

If you were to upgrade your membership, I could post my sample.

 

--Doug

[drzagar]

Hello Doug:

 

Many thanks for your response, but I think I have added to my own confusion. From your response, I think I need to clarify what I need, which is much less complicated than I may have indicated.

 

First, the doctors in the office only need a simple form to add to a patient's file that shows 1) what medical test was ordered and 2) the diagnosis that is associated with that specific test. Nothing else.

 

What they want is a field ("Medical Tests) that opens to a menu list that shows all the 30 or so tests that can be ordered. Only one test is selected. Once selected, they would like to be able to go to the other field ("Diagnoses") that, when clicked, will show a popup menu that shows only those diagnoses that are associated with the test previously selected. Can't get much simpler than that.

 

I have been told that what I need is something called a "filtered value list".

 

That being said, perhaps you can instruct me in what part of your earlier response would yield this result. Also, you use the term "table" which needs clarification. Is a table a file or a simple field? HELP. Many thanks.

 

Dr. Louis Alan Zagar

[doughemi]

It appears that what I offered is exactly what you need. The diagnosis value list is indeed a filtered value list; it shows values based on the contents of the test field. The form will need the patient's name, at least, so the clerks can file it in the proper chart. While it appears complex, its beauty lies in the fact that when (and it WILL be "when", not "if") tests or diagnoses are added or deleted, no change in structure is required; just add or delete records in the table.

 

I didn't notice that you are using FM5. In FM7 and above, it is possible to put more than one "database" into a file. Each one is called a table. Previously, each one had to be a separate file. Give me a couple hours to translate my previous checklist into "five-ese". (it's been a while since I did anything in that older format. Smart guys like AHunter3 can jump back and forth between formats nearly instantly but I'm much more pedestrian.)

 

--Doug

[drzagar]

Doug:

 

Well, now I don't feel just dumb, but really STUPID! Yes, I am in FM 5, but the office will be buying the current version once I can show them how this thing is done. I sincerely appreciate your taking the time to hand hold me through this exercise since it is my WIFE who is one of the doctors (I just hold a lowly Ph.D. , but ya wouldn't know it!). I look forward to seeing the FM5 translation at your convenience. I feel I should be charged a fee for you kind help!!!. Let me know.

 

Dr. Z

[doughemi]

OK, here's how to do it in FM6. I never used FM5, so I am not 100% sure that all the dialogs, etc will be the same, but they should be:

 

Create a file named Diagnosis. It will contain a diagnosisID (autoentered serial number), a TestName, and a diagnosisDescription. You will also need two global fields, g.test and g.diagnosis.

You will create one record for each diagnosis and test combination (if diagnosis A can be indicated by test 1 and test 2, you will have one record for DiagnosisDescription = A and TestName = 1, and another for DiagnosisDescription = A and TestName = 2).

 

Now create the relationship. Go to File->Define Relationships. Click New. A dialog box will appear which is captioned "specify a file to relate to Diagnosis.FP5". Navigate to, and select Diagnosis (yes, we are relating the file to itself) and click Open. In the new dialog that appears, click g.test in the left pane, and TestName in the right and click OK

 

Create the Value Lists. Select File->Define Value Lists. Click New in the dialog that appears. Name the Value List "Test Value List" and select "use values from field". Click Specify and in the new dialog that appears, choose "All values" and select TestName. and click OK. Click New again and name the Value List Diagnosis Value List. select "use values from field" again,and click Specify and in the new dialog that appears, choose "Only related values". Select Diagnosis from the pop-up menu, choose :: Diagnosis Description and click OK. Click OK and then click Done

 

Go to layout mode and delete all fields except g.test and g.diagnosis in the layout. Select g.test and choose Format->Field Format. Select "Pop up List using value list 'Test Value List'" and click OK. Select g.diagnosis and make it a pop up list using value list diagnosis value list and click OK

 

Go to Browse mode and click the g.test field. The popup should show the list of tests. Select one and the g.diagnosis field will show the related diagnoses.

[LingoJango]

I would suggest getting the demo version of FM9 for yourself to do the designing. Doing it in several files entails a LOT more work, especially for a beginner, and then maintaining it will be another nightmare - not to mention the issues that WILL come up when you port the solution to FM9. Don't go there.

[drzagar]

Doug:

 

A few weeks ago you were kind (and brilliant) enough to supply an answer to the below request. I FINALLY got to the task, followed what you said to do and it worked PERFECTLY! Many, many thanks. Now here is the next step.

 

After I set up the database, I now need to enter records for individual patients that do the following:

 

1. Creates a "record" that includes the patient's name (first, mi, last) and DATE that the TEST was ordered (for simple record keeping).

2. In that same record, allows for the doctor to select a "TEST" and a "DIAGNOSIS" from the drop down lists (the reason I asked from help in the first place and that worked just like you said!).

3. Allows for several TESTS and DIAGNOSES to be seleceted and then printed out in a simple columnar list type format (these are used only for ordering TESTS and generally are discarded after the TESTS are ordered).

4. Since this is being done in FM 5, when they buy the newest version (9?), will there be any conversion problems I might encounter? Curious.

 

That is all that is needed.

 

I tried to "CREATE A NEW RECORD" and input the above information, but when I try to print out a list showing the three or four TESTS that have been ordered, they all come out showing the same TEST (the last one that was selected). Any ideas? More "relationships" needed? Many thanks.

smiley_cool

Dr. Louis Alan Zagar

****************

 

It appears that what I offered is exactly what you need. The diagnosis value list is indeed a filtered value list; it shows values based on the contents of the test field. The form will need the patient's name, at least, so the clerks can file it in the proper chart. While it appears complex, its beauty lies in the fact that when (and it WILL be "when", not "if") tests or diagnoses are added or deleted, no change in structure is required; just add or delete records in the table.

 

I didn't notice that you are using FM5. In FM7 and above, it is possible to put more than one "database" into a file. Each one is called a table. Previously, each one had to be a separate file. Give me a couple hours to translate my previous checklist into "five-ese". (it's been a while since I did anything in that older format. Smart guys like AHunter3 can jump back and forth between formats nearly instantly but I'm much more pedestrian.)

 

--Doug

[/size]

[doughemi]

Here is where we run into the problems predicted by LingoJango! This would be a lead-pipe cinch in FM9, but looks like a serious kludge in 5. Conversion also turns ugly because of the workaround(s) required. FM9 adds the powerful functionality of cascading relationships (A is related to B, and B is related to C, so A is automatically related to C) that was missing before FM7.

 

It would look like it's time to pay the $300, since you already have a proof-of-concept with the earlier example.

 

--Doug

[drzagar]

Hi again, Doug:

 

That is what I thought. Am I to believe that I will have to completely recreate in FM 9 what I just spent 6 hours doing in FM 5 (I needed to created 235 records to accomplish the task)? Please tell me otherwise, but I I am prepared to redo it from scratch.

 

Are you also saying that even if I try to do a conversion into FM 9 from FM 5 that I will have trouble from the get-go? Sounds like it to me.

 

Regardless, I am set to demo the FM 5 version this Friday to the owner of my wife's medical office and at least they can see the beauty of File Maker for this and other applications. I am still trying to remove the demons from my head after trying Microsoft Access. Just horrid! All for now.

 

Lou Zagar

Clearwater, FL (75 and sunny today!)

[doughemi]

No, what you have now will convert relatively seamlessly, although it would gag a professional developer. Better would be to build a new FM9 file using the instructions I posted in post #9 in this thread, and then import the data from the converted files. You could then build the new functionality onto it cleanly.

 

If you get them to spring for FM9, get them to add on the price ($30) for membership in this forum, so we can be of more help more quickly (being able to upload and download sample files). Also, a good Filemaker book, such as this one.

 

'Cause there is no doubt, they will be asking you almost daily, "Hey, Lou, can you make it do ?"

 

--Doug

[drzagar]

Doug:

 

Again, many thanks for your input. Far be it from me to "gag" anyone, although as a professional musician, that same reflex was certainly put to the test with the likes of Kenny G and Yanni (a virtual God in the heavily Greek populated city of Tarpon Springs just a little north of us here in Clearwater)! Go figure.

 

I don't think it is a problem springing for the $300 and I do agree that no doubt I will be asked to do more with the database (the office actually has nothing in place other than the standard MS Office apps). Heck, even I have offered to update the office to SOME kind of database other than a standard spreadsheet. They are all ears.

 

I have a demo of what I (we?) did so far tomorrow with my wife's business partner who will make the decision. I do agree that there is SO MUCH MORE that can be done to make data keeping easier and more functional so I am prepared for more requests. As far as this app goes, it will al least start the ball rolling.

 

Later.

 

Lou Zagar

***********

[LingoJango]

While you're at it, get someone to spring for an upgrade to FM Developer for you. There's no looking back!