10 Replies Latest reply on Mar 31, 2014 8:44 AM by philmodjunk

    three level report



      three level report


           Thanks for all of your support so far, I really appreciate all the advice.

           I now have my three level file created and its basically this:




           where each project has multiple buildings, and each building has multiple issues.

           I need to be able to produce an emailed report that has all of the info from ONE project at a time. So, ONE project, its multiple buildings, and each buildings multiple issues. How can I accomplish this without the report including all the other projects in the report?

           (as you guessed or already know, Im very new to this!)

           thanks ahead of time.

        • 1. Re: three level report


               OK, so I figured out how to layout the report so that I have it set up the way I want, but I dont know how to limit the report to only the ONE project. It keeps repeating the sub-summary (Projects) section when I only want it to show the ONE project that was active in the layout I chose to produce the report from.

               How can I resolve this one little issue?

          • 2. Re: three level report

                 You haven't described how you set up your layout for the report. I would guess that you based your report layout on the Issues table as this allows you to include data from Projects and Buildings.

                 To limit the report to a single Project, perform a find on this report layout or use Go to Related Records from a record in the Projects layout to pull up only those issues records for the desired Project, then sort your records to get your sub summary layout parts to appear.

            • 3. Re: three level report

                   Hi Phil, thanks for everything so far!

                   Yes, I created the report layout based on "Issues", and now I did a find which laid everything out (see photo - never mind the goofy data, its all for set up purposes at this point)

                   But how do i set it up so that when I am in another layer (based on projects - my data entry layout for the issues the inspector is finding), I can click a button and go to this layout (which is based on Issues) to show the previous layouts "project" records only? Currently, I do the find once in the report layout and it stays with the one project. I need the project to change when the button is clicked from the other layout. I know it is probobly done via a script that "on layout enter" will set the project, but I dont know how to write that script.

              • 4. Re: three level report

                     You can use Go to Related Records to bring up a new found set of Issues Records on the report layout. Generally, you need a script similar to the following:

                     If [ Not IsEmpty ( Issues::_fkBuildingID ) // check to make sure that related issues records exist for this project]
                        Go To Related Record [Show only related records; From table: Issues; Using layout: "Report" (Issues) ]
                        Sort Records [No dialog ; Restore ]---> specify the needed sort order required to make your sub summary layout parts visible.
                        Show Custom Dialog ["This project does not have any Issues records yet."]
                     End If

                • 5. Re: three level report

                       further to this post, I now have my issues listing per building in the body of the report with the buildings in a subsummary above the body. This gives me a list of buildings with issues under each one. So does this mean that I can have multiple "bodies" in the report? I need to be able to have more information from other tables following and preceading the the Building/issues information.

                       So the questions are:

                       how many subsummaries can I have?

                       Can I have several subsummary/body combinations following each other?

                       Do I need to have multiple reports if I cant have multiple subsummary/body combinations, and if so, can I merge them and email as one report?

                  • 6. Re: three level report

                              So does this mean that I can have multiple "bodies" in the report?

                         If you meant "bodies" that list records from different tables/table occurrences, no this cannot be done using additional Body layout parts. you are limited to a maximum of one Body layout part to a layout. ( you can have zero body parts, however.)


                              how many subsummaries can I have?

                         I wouldn't be surprised to find that there's a limit of some sort, but I've never reached it so it must be a fairly large number. But all sub summary layout parts refer to groups of records from the same found set from the same Tutorial: What are Table Occurrences?.


                              Can I have several subsummary/body combinations following each other?

                         I do this all the time, placing multiple sub summary layout parts above and/or below the body layout part, but please note the limitation on sub summary layout parts I that I stated in my previous answer.


                              Do I need to have multiple reports if I cant have multiple subsummary/body combinations, and if so, can I merge them and email as one report?

                         Maybe, maybe not. It depends on details not available to me at this point. A summary report is based on a single found set of records drawn from a single table via the layout's specified table occurrence. Relationships to records in that found set then make it possible--within the limitations imposed by those relationships, to access data in related tables.

                         Sometimes it's possible to produce reports with a very complex structure where you might have a portal inside a header, grand summary, sub summary or footer layout part to show multiple records from another table. It can also be possible to place a large multi-row calculation field onto your layout that uses ExecuteSQL to display data derived from its SQL query with tab characters separating the fields and tab stops set to arrange the data into neat columns. So you have a number of options that can sometimes, but with significant limitations, combine data from multiple tables in a variety of different ways.

                         If all else fails, you have two additional options:

                         1) You can use more than one layout to produce your report. For Hard copy, you can set up a script that prints pulls up the records, sorts them and then prints them on each layout in turn to produce a single report from multiple layouts. For an electronic copy that you can email, Save As PDF has the option to append the PDF that it is saving to the end of an existing PDF file. Thus, a script can move from layout to layout just like the print script, but saving and appending PDF files to produce a single PDF file to attach to an email.

                         The key limitation to this method is that each layout will start at the top of a new page.

                         2) This last option can be truly awful to set up, but if needs must... You can also define a 'report' table with large numbers of fields and base a layout on it with very complex, overlapping fields such that a script can pull up the data need for your report in multiple tables and then copy/import that data into this report table in order to produce the needed report. Working out all the tricks and picky details needed to make that work--since you only use this option when the alternatives fail--can be a major cause of insomnia.

                    • 7. Re: three level report

                           Thanks Phil, Your reply is most helpful.

                           I am trying the append to pdf method and the one issue I seem to have is figuring out how to get records only from the one project. Is this where I would have a sort command? And would I need a sort command for every one of the three reports?

                           So, basically:

                           go to layout ["project report" (Projects)]

                           sort record [restore; no dialog]

                           set variable ($whatever; Value:GET ( TemporaryPath ) & "Report")

                           Go to related record [show only related records; From table: "Projects"; Using layout: "Project Report" (Projects)]

                           save records as PDF [Restore; no dialog; "$whatever"; Records being browsed]

                           #-------------------------------------And then repeat all of the above for Y and Z with this addition to Z's script:

                           Send Mail [send via E-mail Client; to:"whoever"; Subject:Projects::Projectname; "whatever"]

                           Am I on the right path?

                      • 8. Re: three level report

                             You need to find the records you want for each part of your report. You may need to sort them to get them into the correct order or not.

                             A script can use Perform Find or Go To Related Records to pull up a new found set of records for a report. It can also use constrain Found set or extend found set to modify the existing found set.

                             Here's a thread with some examples of scripted finds: Scripted Find Examples

                             But keep in mind that once you have pulled up a specific record for the Project, you can use Go To Related Records from the projects layout to pull up all Buildings records for that  project on a Buildings layout or all Issues for that Project on an Issues layout.

                        • 9. Re: three level report

                               OK, so this probobly will show how stupid I am...

                               When I go into the settings for "perform find", It asks me for criteria.

                               How do I put in that I want to only find the current project that I was working on? I cant use = or < to go find the name of the project I am working on.

                               My brain is starting to really hurt from learning this program, but its addictive and Im too far into it now to turn back. I should have started learning this when I was in my 20's or 30's, not in my late 40's!

                          • 10. Re: three level report

                                 Please look at the examples shown in the thread that I recommended. None of them specify find criteria inside a perform find or enter find mode script step. They use set field steps to specify the criteria after entering find mode. You can use set variable to capture the ID of the current project, then change layouts, enter find mode and use set field to copy the value of the variable into the appropriate ID field to set up your find criteria.