9 Replies Latest reply on Jun 15, 2009 3:01 PM by mark_d2x

    re: related tables



      re: related tables




      Just having a problem setting up a new database.


      I have 4 tables each with a key primary and key foreign.


      Here is how it is set up


      Patient -     _kp_patient


      Tumour -     _kp_tumour



      Treatment - _kp_treatment

      _kf_ tumour


      Follow up - _kp_follow up



      These are all set up as a relationship - so that records can be made from this relationship.  For each patient you can have more than 1 tumour and more than 1 treatment for that tumour hence setting up the database in this way.  Or have I managed to do that wrong?


      Okay here is the problem, I can set up the first layout for the Patient (based on Patient table), what I am not clear about is the next stage.  I want to then add buttons to take you to a new layout for Tumour or  Treatment; Follow up can be done using a portal.  Do I create the other tables based on the Patient or Tumour and Treatment? If I do it this way I will need to add a button that adds a new record to the Tumour or Treatment table rather than the Patient. 


      I am ok adding a button to get to another layout for navigation.


      Many thanks




      I hope that is clear?  



        • 1. Re: re: related tables



          Thank you for your post.


          Everyone is going to have their own take on this, so decide which way is going to work best for you.


          A patient database is obviously very essential, and I'm sure you have this set up the way you like.


          Since you are specializing in the field of tumours, I'm quite certain you have this set up the way you like, too.


          When a patient is diagnosed with a tumour (or tumours), I presume you have guidelines for a course of action.  I'm not in the medical field, but I don't understand why you would need a separate table for Treatment and for Follow up.  It seems both tables would have a Date of Visit, action taken, results, etc.  If a patient is scheduled for "Follow-up" on June 30, when the patient arrives, you can change the "Follow-up" entry to "Treatment" and the action taken.  Is that something to consider?


          Now to your questions.


          In your Patient table, you would have a history of the tumours in a portal.  You would then have the ability to add a tumour for this patient.


          You could then jump to the particular tumour (via a button - Go to Related Record), and on the tumour layout, have a portal into the Treatment (and possibly Follow-up) table.  Here, you could add a record to the portal that schedules a Follow-up or updates information for the type of treatment.  Since this tumour is already related to a patient (through _kf_patient), there is no need to update the patient record.  You could then add a button that takes you to a Treatment layout where you may want to enter more detailed information that you would be unable to show in the portal.


          I hope I'm not way off the mark.



          FileMaker, Inc. 

          • 2. Re: re: related tables

            You seem to have a sound table design to do what you need.


            "Do I create the other tables based on the Patient or Tumour and Treatment?"

            Not quite. You can create additional layouts for what you need and set up buttons/scripts that take you to the desired layout and bring up the related records needed.


            "I want to then add buttons to take you to a new layout for Tumour or  Treatment; Follow up can be done using a portal."

            Create a new layout that specifies Tumour as its base table. Given the relationships you appear to have, you should be able to add fields from the Patient table to this layout as well. That way, you can display things like Patient Name, etc. You can certainly add a portal that refers to the follow up table also if you have a relationship linking Treatment::_kf_tumour to Followup::_kf_tumour.


            To set up the button/script, there are several ways that depend on the exact design of your Patient layout. If you have a portal in your Patient Layout that lists each Tumour record, you can place a button on each portal row that uses a script with Go To Related record to find the related tumour records and display them on your Tumour layout.


            Alternatively, you can set up your tumour layout so that you do not even need the user to see the Patient layout as you can place all the patient fields on your tumour layout in a header and displays the Tumour fields in the body. With this approach, if you perform a find on the layout to find a specific patient (using name or _kp_patient), you'll get a list of all that patient's tumour records with the patient info in the header. If you are using FMP 10, the header fields will be editable.


            Hopefully, that gets you started.

            • 3. Re: re: related tables

              TSGal, PhilModJunk,


              Many thanks for your replies.  Sorry still confused :-(


              TSGal, what I didn't mention was that the patient table has approximately 60 fields, tumour 100, treatment 150 fields.  The background to this is that it is for a national dataset, however we are collecting a considerable number of extras that we need for local audit and research purposes.  Perhaps I haven't explained my problem correctly?


              Ok, here goes!


              Because of the way in which the dataset is written, there are 4 tables as previously mentioned.  The unique identifier for the patient table is the NHS Number, although I am using an auto-generated identifier for _kp tumour.  The  Tumour table is something called the CARE_SPELL_NUMBER - this is the NHS Number then the code for the tumour site then the date of diagnosis backwards eg 1234567890-10-20090529, this I have managed to do automatically with previous help from this forum.  The CARE_SPELL_NUMBER is then also used in the Treatment and Followup tables.


              I have set up the patient layout basing it on the patient table.  I want to keep the basic layout essentially the same on all layouts so I have duplicated the layout, deleted everything other than the basic demographics.


              The button on the Patient layout is - go to layout Tumour, Treatment or Followup.  once I am at that point it starts to go wrong.  Should I be using Go to related record rather than Go to layout?


              If so, then the settings should be:   Get related record from Tumour

              Shoe records using layout Tumour

              Show only related records

              Match current record only  or match all records in current found set?


              So the first time i go to the Tumour layout it will only have the basic demographics showing and no tumours showing. Then this is what I thought - a button to add a record to the Tumour table?  The same button could then be used to add a second or subsequent tumour?


              So unsorted the patient table will show all the records, in this case about 1000 patients; the tumour table will only show 1 maybe 2 tumours for that patient - then the treatment may be 3 records and the followup could be 40+ records. 


              I don't think Portals are going to be much use, other than for follow up, as there is far too much information to put into the portal? I guess I really like and want to use the tabs to split the data into process driven rather than data driven.


              What I think is confusing me is that I need to create a new record into each of the subsequent tables.


              Sorry, I think it is just my lack of understanding - just when I thought I had it cracked its all gone to pot. 


              The final thing is that to do the data upload to the Department of Health, the data needs to be uploaded as a CSV file, however all the data items need to be enclosed within "" eg "1234667890","BLOGGS","Joseph","06/07/1968"  - doing this manually is quite time consuming using a text editor - I thought I could do some calculation fields to do this for me, the question here is do you think it would be best to do this into another table since for some of the data items the calculation will be pulled from a lookup table rather than the main table? (eg for gender - male =1 female = 2, so in the look up it displays male or female but for the report I need 1 or 2, this worked well when I  created a table report for the previous upload, these fields I described as Description and Codes in the lookup table).


              Best wishes,



              • 4. Re: re: related tables

                Your tables sound fine. There is more than one way to create related records, a portal with [x] Allow creation of related records being one. But since you've got more to add, and want to go to the "child's" layout itself, this is the basic routine:


                Commit Records [ no dialog ] (just to get out of any active fields)

                Set Variable [ $id; primary key of this table ] (Patient in this case)

                Go to Layout [ child table's layout ] (Tumor in this case)

                New Record/Request

                Set Field [ PatientID; $id ]

                Commit Records [ no dialog ] (not entire needed, but Looks Up any field data from Patient)

                Go to Next Field (just get into the first enterable field)


                Go to Field [Tumor name] (specify a field) 



                Go To Related Record is how you get to child records, usually with (•) Current record only (thought Found Set is also useful in some cases). 


                Export as CSV automatically puts quotes around the fields.

                Export as Merge is similar, but includes the field names as the first line (not in quotes). 

                • 5. Re: re: related tables

                  Sorry Guys,


                  I am really not getting to grips with this at all ;-( - part of the reason is describing what I want as much as anything.


                  Ok, so lets recap


                  I have done the PATIENT layout, this is based on the Patient table and contains no data from any other table (this I am considering the master layout as this has to be completed first before you can do anything else).  This has been the easy part ;-)


                  These are the layouts I need:


                  Tumour - showing only the tumours for this patient - the demographics are held in the PATIENT table (there are only 5 data items from the PATIENT table that need to be shown) and the TUMOUR details are in the Tumour table.  


                  Treatment - showing the treatments for a particular tumour. 


                  So, if I have a button that says "Tumour details" I want that to take me to a layout that shows me only the tumours for this patient. When I click this for the first time there will of course be no records and will show the demographics and no record.    The option here is that the user can add details straight into a blank record or have to click on add tumour button.  However, what I am still not clear about is how to do this.  What I don't want to happen is everytime the "Tumour Details"  is clicked a new record is added.  Of course I want to add a next and back button to cycle through the tumours.


                  This will of course be essentially the same for treatment except that there will be the demographics from the PATIENT table, the tumour site from the TUMOUR Table and then the full table from TREATMENT. tumour site should act as the filter for the treatments - so that eg the patient has a rectal and a caecal cancer - they drop down to then show only the treatments for each respectively 


                  Is this getting too complicated?


                  So, I am not sure how to go forward with this at the moment other than delete out the layout I have done as they are not working properly and then start again from scratch. Jeez my head hurts...


                  Best wishes



                  • 6. Re: re: related tables



                    It sounds like you don't have the relationship set up yet.


                    Pull down the File menu and select "Manage -> Database...".


                    Click on the "Relationships" tab, and you will see graphical representation of your tables.


                    Find the Patient table and the Tumour table.  In the Patient table, click on the _kp_patient field, and drag it over to the _kf_patient field in the Tumour table.  When you let go, both fields will appear at the top of the tables with a line connecting the two tables.  Your tables are now linked.


                    Halfway on the link that links the two libraries together is an icon.  Double-click that icon, and an "Edit Relationship" dialog box appears.  You will notice one side of the dialog box displays the fields from the Patient table, while the other side displays the fields from the Tumour table.  At the bottom of the side that displays the Tumour table, check the option "Allow creation of records in this table via this relationship".  Click OK twice and go into Layout Mode (View menu).


                    At this time, for display purposes only, go to the layout that has your Patient information.  In the Status area, you will see a number of icons.  As you put your cursor over them, a tooltip will appear that describes each tool.  Towards the middle right, click on the Portal tool.  Go onto layout and draw a box somewhere off to the right.  Once you draw a box, a "Portal Setup" dialog box appears.  You want to Show related records from "Tumour" and click OK.  A new dialog box appears to "Add Fields to Portal".  Select two fields for now.  Click OK.


                    Go into Browse Mode, and you will see all Tumours connected to this Patient.  You also have the ability to add a Tumour.


                    You can then add a button to the portal that executes the script step "Go to Related Record" that takes you to another layout that displays the Tumour information.


                    This should give you a good starting point.


                    Please let me know if you need clarification for any of the above steps.



                    FileMaker, Inc. 

                    • 7. Re: re: related tables



                      The relationships were set up ok.  Forgive me, just trying to clarify this in my head; what you are suggesting is to use the portal tool to get the records started then complete the process using a layout based on tumour or treatment?   So if for example the portal has just 2 of the fields, say date of diagnosis and tumour site, that will be sufficient to get the rest of the process going?


                      If that is what you are meaning, then I think I get it now.....


                      As I said earlier, there are a huge number of data items in each table which isn't helping. 


                      Will give it a go tonight and tomorrow morning and get back to you.


                      Many thanks



                      • 8. Re: re: related tables

                        You sound like you're getting there.


                        Keep in mind that you can create a layout based on tumour and place related patient fields in the header (the top part in most layouts you create.) If you make this a list view or table type layout, you can then see your patient data at the top of the screen and the fields from your tumour table in rows beneath it.


                        With scripts using go to related records from the Patients layout (use match current record)  or a find request, you can pull up all the tumour records for the current patient in your patient layout. You can either use TSGals's trick with a portal or set up a button on your tumour layout that creates a new tumour record for the current patient.

                        • 9. Re: re: related tables

                          Thanks PhilModJunk


                          On this occasion and at this point the portal trick seems to be the most appropriate, however I will still need to use your suggestion for some other layouts.  


                          I am planning to try to complete this tomorrow (once i have slept!)


                          I realise there is always more than 1 way to do this, it is certainly a steep learning curve and this has been/still is a difficult project for a newbie to databases and Filemaker.   


                          I have a copy of The Missing Manual for Filemaker Pro 10, which is good but sometimes no book can help with some of this - thank goodness for people who are willing to share an  spend time helping others out.


                          I am sure there are going to be other questions i need answering in future and hopefully it won't be too long before I can  help others out too.


                          Best wishes