11 Replies Latest reply on Jun 7, 2013 10:28 AM by rkassis

    Displaying fields from multiple related tables in list view

    rkassis

      Title

      Displaying fields from multiple related tables in list view

      Post

            Hello

           I am currently working on  health care database. In my database I have multiple tables For example, Main (patient info), laboratory results (as each patient may have multiple lab entries), hospital admission/encounters (patient may visist hospital many times), and treatment (patient may recieve many different types of chemo)

           What I want to do is to display all of the fields in these tables in a list view. I posted a picture of how I would like it to be. I want the records to be arranged by date. Each table has a field "date" In the picture light blue="lab", darkblue="hospital admissions" pink="diagnoses" and orange="treatment". In other words every color refers to a different table. So far I can't find any way to do this. Someone help me please.

            

           Thank you!

            

      database_lab_report.jpg

        • 1. Re: Displaying fields from multiple related tables in list view
          philmodjunk

               There are features to your layout example that do not have direct equivalents in FileMaker, but a "cross tab" type relationship where the tests are listed as column headers, the dates are listed as row labels the results of the test may be listed as the intersection of a given test name and date can be set up in FileMaker with the right relationships and layout design.

               The format differences within the row such as a "cell" that spans multiple columns, will, however, be rather difficult to do in FileMaker.

          • 2. Re: Displaying fields from multiple related tables in list view
            rkassis

                  Okay, I really dont understand. Is it possible to make a summary layout that can be formatted in this way. The picture I posted is color coded and each color represents a different table. I want to show specific information in the layout from all of the records in these relating tables. I dont understand making a new relationship and "cross tab". I already have the tables for all of this info with the fields and everything. I would just like to formulate some sort of summary layout like the picture (does not have to be identical) The reason is to see how each patient is being treated and what is happenin to them over a course of time.

            • 3. Re: Displaying fields from multiple related tables in list view
              philmodjunk

                   If the data in the different color boxes represents data from different fields, then no this is not possible. But that does not mean that the data shown need come from different tables.

                   That fact that you already have tables and relationships in place does not mean that they will work for the report format you specify. Additional relationships will likely be needed to produce the desired format.

                   I am not spellling out a detailed solution for you for two reasons:

                   There is not enough information in your posts to know what data resides in which tables nor how they are related.

                   This type of report is complex there are multiple options you can use for setting it up and some options require FileMaker 12 others do not. It really is not something that a novice should try to set up. You are either looking at a significant investment in time learning the concepts invovled or you will need to hire the services of a consultant to set it up for you.

              • 4. Re: Displaying fields from multiple related tables in list view
                rkassis

                      Okay, thanks for the help. I can explain more about how my database is set up. As of right now I have 3 tables. "Main" (contains all of the personal patient info), "Treatment" (contains their treatment, and "Lab" contains their lab work.

                     __pk_main------>__fk_main_LAB

                                          ------->__fk_main_TREAT

                      

                     So my main table is related to both the lab and treatment table in a 1: many relationship. Is there a way to use calculation or querie table occurances to do this? I would like to know how I could do this as it would create an easy way to follow small studies with this info. Thank you

                      

                      

                      

                      

                • 5. Re: Displaying fields from multiple related tables in list view
                  philmodjunk

                       That doesn't seem to match your screen shot. You've indictaed that each color represents data from a different table. The image you've uploaded shows at least 5 different colors--implying that you have 5 tables but now you only describe 3.

                  • 6. Re: Displaying fields from multiple related tables in list view
                    rkassis

                          I know, the screenshot is a planned version of what I want it to look like. I currently just have 3 tables. I just want to know how I can display information in such a way. As of now I have the light blue and orange colors. I dont even know how to display those two as shown in the screen shot

                    • 7. Re: Displaying fields from multiple related tables in list view
                      rkassis

                            Would it be easier to just put all of the fields from the tables I want to display into one large table and then use conditional formatting to show the values of the fields? I really just want this table thing with the dates to be read only and to update when i create a new record in either one the tables it corresponds to.

                      • 8. Re: Displaying fields from multiple related tables in list view
                        philmodjunk

                             Things are way to fuzzy here to make much in the terms of suggestions. A good database design starts with a sound data model that works for the requirements of the user and from there you build the best interface you can within the limits of what can or cannot be done with a given database application such as FileMaker.

                             What you describe in your last post is possible, but unlikely to be the optimum layout design as it is unlikely to be sufficiently flexible to work well.

                        • 9. Re: Displaying fields from multiple related tables in list view
                          rkassis

                               Thanks for the reply.  I understand I can only do things within the limits of filemaker. Would you like me to show you a detailed sketch of how my database relationships fit together so that you can see where I am coming from. I would like a summary layout like this to show multiple tables because the doctors want to see the timeline of the patient in a quick glance without having to flip through laypout after layout. If you would like to help me more on this I am wiling to give you as much info as possible. I really need this to somehow be done. I ended up taking all of the fields in those tables and sticking it into a massive table and just conditionally formatting the fields on the layout to display or not display depending on a certain field. It worked but I dont want it to eventually mess everything up since in the future there will be a massive amount of patients on the DB.

                               Thanks again

                                

                          • 10. Re: Displaying fields from multiple related tables in list view
                            philmodjunk

                                 There are limits to what I am willing to do timewise to help you. What you are asking for is basically a "free tutoring" session on a fairly complicated concept and I only have so many hours per day that I can donate to this forum.

                                 I suggest starting a new thread where you break this down in to smaller "chunks" so that others are more likely to chime in with suggestions. The reason that I keep saying things are "too fuzzy", there's not enough info... is that it's not clear which parts of your screen shot correspond to data in a particular table in your system.

                                 If you started a new thread (long threads get fewer responses) and more clearly indicated the links between your example of your layout with the data in your tables, you may find more help forthcoming.

                            • 11. Re: Displaying fields from multiple related tables in list view
                              rkassis

                                    Okay thank you. Ill try to post different posts to explain my question better