1 2 Previous Next 17 Replies Latest reply on Jan 17, 2012 8:02 AM by philmodjunk

    Medical database structural build problems



      Medical database structural build problems


      I have been using a database I designed (with tremendous help from this forum). It has primarly been an invoice databae. 

      Now I want to add my medical record keeping and need some help pulling it off. I have the following tables that I need to relate.


      CASES (each patient may come for multple cases or for mutliple reasons over time)

      TREATMENTS (each patent will receive multiple treatments but each treatment will be realated to only one case)

      INVOICES (Currently I am planning to keep the treatments and the cases seperate from the invoices but it could be helpful to link them)

      I am attaching a screenshot of the current setup.

        • 1. Re: Medical database structural build problems

          Patieints----<Cases-----<Treatments>---Invoices   (I am assuming that a patient needs each "treatment" itemized on an invoice and that an invoice can list multiple treatments.)

          Patients::PatientID = Cases::PatientID
          Cases::CaseID = Treatments::CaseID
          Invoices::InvoiceID = Treatments::InvoiceID

          This is really a classic invoicing structure with an added cases table between the customer (patients) and the itemized list of products/services sold to them (Treatments).

          A list view report based on Treatments can list all the treatments for a given invoice, with the needed info from Patients, cases, and invoices included in the same report.

          • 2. Re: Medical database structural build problems

            PhilModJunk Thanks again for some help.


            Actually "treatments" are different than "services" which are currently linked to the "invoices" and are at this time a seperate strand of the database used by the office administrator. "Treatments" are a record of the inidividual consultation for the physician, with a report of symptoms, signs, and the therapy administered (the actual medical record). "Cases" are the initial complaint report and treatmet plan. The big hang up I have right now is I want to attach a case to each treatment. I would like to have a field in the treatment table that displays the case associated with that treatment. If I do that as a drop down menu I get all the cases of all the records. I want to see only the cases for that patient and chose the appropriate one, establishing the link between that case and that treatment.

            I am certain the answer is quite basic and apologize for niavete'. Thanks for any and all help.

            • 3. Re: Medical database structural build problems

              The relationship that you need is spelled out in my last post:

              Cases::CaseID = Treatments::CaseID

              Cases::caseID should be an auto-entered serial number. and Treatments::CaseID should be a number field.

              If you format Treatments::CasID as a drop down list or pop up menu, you can select a case record from the list of available cases. If you first specify the patient ID, you can set up a conditional value list that lists only the cases for the current patient. Once you've selected the CaseID, fields from the Cases Table that you you choose to place on this treatments layout, will display data from the selected Cases record.

              Here are some links on conditional value lists that you may find helpful:

              Forum Tutorial: Custom Value List?

              Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

              Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html

              • 4. Re: Medical database structural build problems


                I reveiwed what you sent and checked to confirm the realtioships are set up properly. I created the value list and under the "include related values starting from" checkbox I entered "Patients" as the correct table. This did not do the trick. Now no values are displayed. This situation is different than the examples I have looked at in the stuff you linked me to in that I am jumping from treatments through cases to the patients.

                Thanks again.

                • 5. Re: Medical database structural build problems

                  On what layout did you set up your value list?

                  Each layout refers to a specific table occurrence (Box in manage | database | relationships) as selected in Show Records From in Layout Setup. What table occurrence does this layout specify. Most of the time, the "starting from" table occurrence you specify in the value list setup is also the table occurrence of the layout.

                  If that doesn't resolve this issue, please spell out exactly what you have set up, both in the value list and on your layout as well as the relationship you used and I'll take a swing at spotting what needs to be changed.

                  • 6. Re: Medical database structural build problems

                    I used the "treatment" layout. It shows records from "treatments". I changed the "starting from...." to the "pateints" table so that the conditional value list  would be from the patient entered into the first field of the treatment layout. See attached image file.



                    • 7. Re: Medical database structural build problems

                      I see no attached image file. Was it of type gif, jpg or png? Those are the only file types that can be uploaded so if you used a PDF or bmp file the forum accepts the upload but nothing shows up in your post.

                      If you are selecting a PatientID in the Treatments file and then selecting the Case from the list of cases for that patient, you should have Treatmetns as your starting from table, not patients.

                      You'd also need this relationship:

                      Treatments::patientID = CasesByPatient::PatientID

                      and list your values from CasesByPatient, include only related values starting from Treatments.

                      CasesByPatient is a new table occurrence of the Cases table. You create it by clicking Cases in manage | Database | Relationships, then clicking the duplicate button (two green plus signs). YOu can then double click this new box to get a dialog where you can rename it to CasesByPatient as I have done here.

                      • 8. Re: Medical database structural build problems

                        I'm wondering if this approach might make more sense:

                        Put a portal to cases on a patients layout. Put a button in the portal row with a script that goes to a treatments layout and pulls up all treatment records for that case. It's possible to then set up the system so that new treatment records created on this layout auto-enter the current CaseID of the record clicked in the portal of case records.

                        Let me know if you like this idea and I'll respond with a step by step discription of the process.

                        • 9. Re: Medical database structural build problems

                          I am attaching some images that migh help you see what I am looking to do and what I have done. The layout you will see is from "treaments" it has some of the fields I am trying display. Nothing is in a portal currently.

                          • 10. Re: Medical database structural build problems
                            /files/8694e9d384/layout_in_%2522treatments%2522.png 1280x800
                            • 11. Re: Medical database structural build problems

                              I don't see any layout, just your relationships graph.

                              What method do you want to use here, the conditional value list, or the alternative that I just described in my previous post?

                              The relationships look correct for a conditional value list located on a Treatments layout. The value list setup should list values from "cases by patient"--not "cases", and Treatments would be the "starting from" table.

                              • 12. Re: Medical database structural build problems

                                I like trying to continue with the conditional value list. I think it allows me to do what I want. I have attached the screenshot of the "treatment" layout again for your reference

                                • 13. Re: Medical database structural build problems

                                  Like I said before, the value list must list values from "cases by patient"--not "cases".

                                  The drop downs in the specify fields for vlaue list dialog show that you are listing the values from cases instead of cases by patient. It's the same data source table in both cases, but the table occurrence you select here in the drop down identifies one half of the relationship used with the "include only related values" setting to filter the values. The other half of the relationship is identified by the "Starting from" table occurrrence.

                                  • 14. Re: Medical database structural build problems

                                    I did make the change but sent you the original picture. I am still not getting the "<no values defined>". I will take some time to review all your posts and understand a bit before coming back with more information. I have been spending five minutes on this here and there through the day. Your help is greatly appreciated. I know we are closer and I thank you for all your patience.

                                    1 2 Previous Next