4 Replies Latest reply on Apr 13, 2012 11:38 PM by KatG

    List view of a found data set incorporated into the body of a report



      List view of a found data set incorporated into the body of a report


      I need to create a report that incorporates a found Data Set or List View of records from the Child 2. I have a Parent table and a child1 table. I would like to add a Child 2 table.     So the Parent ID> Child Table1 and Parent ID> Child Cable 2.  The main report I have created originates from the child table 1 with fields from the parent table added and I would like to add  the list view of records for the child table 2.

      I'm pretty sure I need a script for this but I do not understand the first thing about script writing. I don't know where to start. I have "The Missing Manual" and have read the script chapters several times, I just can't seem to wrap my blonde head around the concept.

      I did post a similar question using a join table but I did not understand how they work together and I was unable to create a workable report. 

      I have created the "report" of the child table 2 that shows the found set or list view, now I need to find a way to incorporate that into the main report.

      I hope this is clear.... Thanks!


        • 1. Re: List view of a found data set incorporated into the body of a report

          What exactly are the relationships between these three tables?

          If by "found data set" you mean the set of related records, you have a table design--report issue that is a challenge to handle in FileMaker. The simplest option--which does not always produce acceptable results--is to use the very thing we normally advise against: a portal. If you basically need a list of records without sub summary parts and you don't need variable height fields, you can put a portal to Child 2 sized with many more rows than you expect to need and set the portal to slide up | resize enclosing part. This will shrink the portal to just the rows needed to display the set of related records.

          A more complex option is to set up a temporary "report table" and import data from both child tables into it strictly for reporting purposes. This can lead to very complex strategies in both layout design and field definitions but can be made to produce a more sophisticated report than is possible with the sliding/shrinking portal method.

          • 2. Re: List view of a found data set incorporated into the body of a report

            Right now the Relationship is a one to many :  parent table------<child1  I thought that if I added another "one to many" table  (the parent table------< child table 2, this might work. I have also attempted using the child table 1 as a join table between the parent  and the child 2, ( Parent table ------<Child1 -------< child 2 and I have played with test data and I can't get what I need on the report, using the join table... I have tried (what I think was ) every combination of relationship direction....  I am sure I am doing something wrong in the relationship settings but I don't know what. I am effectively using portals in my current set up, but I do have to be mindful of the record ID ....it is very easy to inadvertantly add a new record and enter info into the wrong line... I have addressed this by entering the  ID in every portal so that if it does get out of order I can scroll and make sure I am in the right place.  I am using this for patient progress notes. The parent table is the patient information that does not change. Name,  SSN etc. and the child table at this point consists of everything I need to document... I have created a report and a format that is basically a "sentence builder". It works ok but I usually address multiple diagnoses within an encounter so currently I have Dx1, Dx2, Dx3 etc and plan 1, plan2 and so on.... I should be able to design this in such a way that I am able to add a Dx record the same way I am adding an encounter record... I "find" the patient I am seeing and then I add a new encounter.... within that same encounter record, I want to then add each diagnosis.... so I don't know if a join table is what's needed or if I am not utilizing the portals correctly. How this information is stored is not an issue,  I just need to be able to design the "Report" basicall so that it appears as a word document... archaic I know ... it is what it is.

            I don't want to set this up by printing a separate layout "list view" because I have to account for the number of pages, I have created a field next to the auto page number so the page number will be "1 of 3" , "2 Of 3" ... etc.... there are also encounters in which I have separate procedures ... One patient has many encounters, within one encounter there can be multiple procedures and there are multpile diagoses.

            I hope that makes sense, thanks so much for all of your help Phil!


            • 3. Re: List view of a found data set incorporated into the body of a report

              WHen I ask this: What exactly are the relationships between these three tables?

              I'm asking for field names, value types, operators used in the relationship , etc. I want to see the "nuts and bolts" to make sure that there are no alternative approaches that will work better for you. YOu can upload screen shots of Manage | Database | relationships or you can list them like this:

              Table1name::KeyFieldName = Table2Name::Keyfieldname3
              Table1name::KeyfieldName2 = Table3Name::fieldFieldname4

              and so forth.

              It sounds like you actually may have this set of relationships, but I'm guessing a lot here:


              A patient can have many encounter record and each encounter record can be linked to many Diagnosis records.

              Or you might have:


              Either way I have more than the three tables you defined originally and I don't know enough about what you are doing (that needs documenting in the database) to know for sure if either guess is correct.

              If the first option is correct, a list view layout based on Diagnoses could be used to print out all your data.

              If the second is correct, a list view layout basd on Procedure_Diagnosis could be used.

              I'm not ignoring what you said Here:

              I don't want to set this up by printing a separate layout "list view" because....

              It's just that I don't know enough to see why this is an issue for you.

              • 4. Re: List view of a found data set incorporated into the body of a report

                 I reviewed the chapter on Portals in the missing manual and with your suggestions I was able to accomplish what I needed. In a different post I learned how to make a text calculation field (thanks!) and on the report I use the calculation fields in the portal. I tend to get caught up in the minutia and make things harder than they need to be. I created several child tables (with a one to many relationship) and I am now beginning to understand the power of portals. I was having a hard time visulizing the links because the serial ID numbers were not consistent and I realized I was inadvertantly adding records and they were getting out of order. Some of this is actually beginning to make sense! Thanks again Phil!