5 Replies Latest reply on Jun 28, 2013 10:28 AM by philmodjunk

    Making Printable Reports Help

    user18408

      Title

      Making Printable Reports Help

      Post

      Background

           Databasedetails : A patient database recording adverse events with a main table linked to related tables by patient ID. The realted tables store data that is repeating. For exmaple the medical history of a patient, which may contain multiple records of surgical procedures for the single adverse event record of the patient (main table). I use portals on the main table for data entry into these realted tables. This is all great for input, but when i come to generating a full printed report this becomes a nightmare because i do not know how many instances of the "reapeting" data i need and thus printing portals and formatting a coherent report is difficult.

           Is there a way to get around this?

            

            

      Screenshot_26_06_13_2_31_PM.png

        • 1. Re: Making Printable Reports Help
          philmodjunk

               This is one of those nasty situations for which there is no simple, "one size fits all" answer. There are four possible approaches you can explore:

               1) Use the portals--this is the simplest to set up from your given design, but often bangs its head on printing issues with portals. (see notes at end of this comment) You set up portals to each related table sized many rows more than you ever expect to need for a given patient, then set them to "slide up"/"Resize enclosing part" to shrink each portal down to just the number of portal rows used.

               2) Use a single related table in place of all the different related tables. This table can still be updated via multiple portals, but will require a significant redesign of your database and this can be a real chore if you already have a lot of real data residing in these tables.

               3) Use a temporary "report" table with a script that pulls all the data from all the related tables into different records of this temporary table. This requires less "surgery" on your current design, but the script to pull all of this data into the report table each time you need a report can be complex and it will take more time to produce such a report due to all the data that has to be imported into this table.

               With both 2 and 3, the differing data requirements for each different "adverse event" can make design of the report layout very complex. Often it requires creative use of conditional formatting, special calculation fields etc layered on top of each other so that each type of "event record" can correctly display data in a different format. This could result in a very complex difficult to maintain layout if there are a lot of differences from one "event record" type to another.

               4) Use a different layout for each type of adverse event. Base each layout on a different "child" table with fields from the Main table placed as needed on the layout to show data about the specific patient. When you print hard copy, you print from each layout in sequence and collate the paper into a single hard copy document. A script can even use Save As PDF to generate a single PDF document containing all the individual reports so that it appears to be a single report. A script can also be set up that adjust a "page number" field so that the resulting combined report has correct page numbers that start from 1 on the first page from the firsrt layout.

               The main draw back to this method is that each set of of adverse event records included in the report must start a new page.

               PS. you appear to have some relationships that do not look correct here: The last 4 table occurrences include additional match fields that should not be needed and which can also create issues for your database. You should be able to get each of these to wor from a single ID number and not need the additional fields. Name fields are not good match fields as names are: a) not unique b) people change their names and c) vulnerable to data entry errors. (And if CSR identifier is not an internally generated serial number of UUID, it also should not be used as the match field in these relationships.)

               Key facts about sliding layout objects:

                 
          1.           It's only visible in preview mode and when you print/save as PDF...
          2.      
          3.           Sliding fields will shrink but not expand.
          4.      
          5.           All layout objects below and in the same layout part as the slide/resize field need to also be set to slide up and resize.
          6.      
          7.           Objects in headers and footers will not slide.
          8.      
          9.           Portals will shrink/slide to fit the number of rows of records, but fields within the portal row will not shrink/slide.
          10.      
          11.           Fields will slide up only if Top alignment is specified for it and will slide left only if Left alignment is specified.
          12.      
          13.           Consistent side borders are difficult to achieve with sliding fields.
          1 of 1 people found this helpful
          • 2. Re: Making Printable Reports Help
            user18408

                 Hi PhilModJunk ( i am very new to FM pro) so i appreciate all the help i get from these forums

                 Thankyou for a most detailed answer, i shall have to think on this.

                 In the meantime,  i will fix the relationships to just the CSR ID as you have pointed out. (thanks)

                   
            •           The CSR ID is an internally generated serial number on creation of the adverse event record. Just to check with you that i have done this right below is a screenshot for the setup of this "serial number field". I am pretty sure i have done this wrong as i want a unique ID for each adverse event record, so i would apreciate you checking this for me.

                  

                 Perhaps i should post my entire solution to you to have a look at, i wonder how i can do that?

            • 3. Re: Making Printable Reports Help
              philmodjunk

                   I would keep the serial number totally simple--just a number, no alphanumeric prefix. If that is needed for some reports or other purposes, put the prefix in its own field and combine the two fields when needed, but not for use as a match field (just match by the number and only the number).

                   The alpha prefix suggests that there is some additional "meaning" inserted into the field's value that is needed for/requested by your users. Once you allow any kind of additional "meaning" to be included in your primary key, you open up the door to your users wanting to change the content/format of that added meaning and such changes will threaten the data integrity of your database.

                   If you keep that extra info in its own field and don't use it as part of your primary key, any time you get a request for such a change, you can smile and make that change without anything threataning your database. If it's part of the primary key, you end up having to track down each affected record in the related tables and correctly updating them to new values--making your database unavailable for a long period of time and should you make a mistake during the update, you can end up with a real disaster on your hand. And this is all avoided just by using that separate field kept only in the parent table.

              • 4. Re: Making Printable Reports Help
                user18408

                     Now that is seriously good advice. The database has nothing in it but test for function data at present so in the words of the pink panther "Now is the time Kato" for changes such as this.

                     What would really be good is if the other  info indicated "country of origin and Year occured". I could then put this on reports as Merge feilds to create the "full ID" so to speak i.e "serial number" - "country of origin" - "year that the adverse event occured"

                • 5. Re: Making Printable Reports Help
                  philmodjunk
                       

                            What would really be good is if the other  info indicated "country of origin and Year occured". I could then put this on reports as Merge feilds to create the "full ID" so to speak i.e "serial number" - "country of origin" - "year that the adverse event occured"

                       There is no reason why you can't do exactly that. A calculation field can be defined that combines those values from separate fields and you can also just place the individual fields on the layout as merge fields so that they all run together and look like a single value to the user.

                       <<serial number field>> - <<Country of origin field>> - <<year field>>