1 2 Previous Next 17 Replies Latest reply on Sep 26, 2012 1:58 AM by brian.curran

    Database Reporting



      Database Reporting


           I'm starting to generate a few sample reports for our FM Pro 12 database and wondered what is the best practice for displaying them. I had a look at the Starter solutions but couldn't find anything useful in there.

           I thought about using a Layout called 'Reports' which would have a drop down box listing some headings which would then conditionally display further items in another drop down list. Once the user navigates to the relevant report, it is displayed on screen for printing/exporting etc.

           Another method might be to have a scrolling list (portal?) with reports grouped under certain headings, not sure how to go about this at all, as it's been a while since I dabbled with FM due to my Elance developer letting me down 2-3 months ago.

           Any suggestions would be appreciated...


        • 1. Re: Database Reporting

               There are many, many options for setting up reports in FileMaker. The best options will largely depend on the user's needs and the structure of the database.

               Most reporting uses something similar to this outline:

               Select the layout used to display the report

               Perform a find to find only those records needed for this report.

               Sort the records into an optimum order for the report. This could involve grouping the records such that summary fields can report aggregate values such as totals and averages for each such group.

               Then either enter browse mode for simple reports or reports that do not need to be printed or saved as PDF

               or Print, save as PDF and/or enter preview mode. (and when you print/save as PDF, it's important to know the difference between Records being Browsed and Current Record.)

               With some layouts, such as a labels layout, the appearance of the data changes significantly when printed or previewed so it's often helpful to enter preview mode to show the user a more accurate representation of what they are going to get.

               That's just the very bare bones basics. It will be useful to also read up on some of he special features that can be specified for a report layout such as:

               Sliding Fields   (Field size reduces to just the size needed to fully display contents.)

               Sliding Layout objects (Other objects can slide up or over when a sliding field slides.)

               Sub summary layout parts (enables subheadings and sub totals for groups of records)

               Grand Summary parts

               Title Headers

               Merge Fields and Variables

               Page numbering and other report symbols you can insert from the Insert menu.

          • 2. Re: Database Reporting

                 Thanks Phil, lots of great information there for me to consider.

                 I'm only at the 'Select the layout used to display the report' stage so would like to decide on an easy to use method so I don't have to make too many amendments further into the development.


            • 3. Re: Database Reporting

                   First design a layout that produces the report you need. Then, for initial testing purposes at least, you can select the layout from the layout drop down in the tool bar to select that layout. More user fiendly options are to place a button on a layout that uses Go To Layout to go to the report layout or even a drop down list of report layout names with a script trigger that switches to the layout selected in the drop down.

                   Eventually, you'd replace that single Go to Layout with a script that does the other needed parts such as finding records and sorting to produce the desired report.

                   Just to whet your appetite, check out the floating window "dialog" that I use for some of my reports shown below. The user clicks a button named with the name of the report on a main menu page, this window then pops up to collect date range info and then, (when the user clicks "Find" in the floating window), a script uses the data entered to find the records needed for the report and sorts tham as needed.

              • 4. Re: Database Reporting

                     I've got everything as per your first paragraph but I'm not sure how to do the script trigger. I'm guessing at an OnObjectSave script trigger with a Script Parameter?

                     In my Reports table, I have ReportName and rptTitle so if "Employee List" is selected, a script runs which uses the "rptEmployeeList" script parameter to name the Layout/Report required. Would it use a =Self expression or something?


                • 5. Re: Database Reporting

                       If you use a drop down list, OnObjectSave is probably your best choice. With a PopUp Menu, OnObjectModify is a better option. You don't need a script parameter as your script can directly refer to the value of the field you set up with this value list of report names.

                       I don't understand your second question.

                  • 6. Re: Database Reporting

                         Ok, please ignore the second half of my question as I thought that whichever value was selected would determine which layout to go to. I'm not sure how to 'refer to the value of the field' in a script as you suggest though. I'll have a look to see what options are available...

                    • 7. Re: Database Reporting

                           I now have an OnObjectSave script trigger assigned to the Drop Down box which opens a basic script that has:
                           Go To Layout [Reports::ReportName]

                           This seems to work fine but I'll have to try Reports::rptTitle as I want user friendly report names in the drop down list. The only downside is that my four report names are being overwritten in the Table each time I choose one from the list?

                      • 8. Re: Database Reporting

                             Define a new text field. You can define it in any table in your file. Use field options to specify global storage for it. Often, it's helpful to put all global fields except those used to define a relationship in a single table named Globals. Fields with global storage can be accessed from any layout and script in your file. I'll name this field: gSelectedLayout.

                             Put this text field on your layout and format it with your drop down. Give the drop down's value list the same exact names as you used for the report layouts. (I'll outline an alternative where the names don't exactly match later.)

                             Write your script with just this one active step:

                             Go to Layout [Globals::gSelectedLayout]

                             After adding the Go to Layout step to the script select the "layout name by calculation" option and specify the gSelectedLayout field.

                             If you don't name your layouts exactly the same as the values in your value list, you can still get this to work:

                             If [ Globals::gSelectedLayout = "Employee Report" ]
                                Go to Layout ["rpt_Employee"]
                             Else If [ Globals::gSelectedLayout = "Sales Report"]
                                Go to layout ["rpt_Sales"]

                             and so forth.

                             And there are ways to use a table with the names from the value list in one field and the actual names or numbers of the layouts in a second field as well to avoid the lengthy chain of If-Else IF steps.

                        • 9. Re: Database Reporting

                               That's perfect Phil, many thanks :)

                               To use the Table method with the names from the value list, would that be when one field is hidden underneath another?

                          • 10. Re: Database Reporting

                                 That's a method for hiding an ID based drop down list behind a name field so that the user can't see the ID numbers. What I  am referring to is that you could set up a table, ReportLayouts, with fields and values such as:

                                                LayoutName                     LayoutNumber                     ValueListName
                                                Rpt_Employee                     2                     Employee Report
                                                Rpt_Sales                     6                     Sales Report

                                 You only need 2 of these three fields. LayoutName needs exactly the same name as found in the Manage | Layouts and LayoutNumber has to be the position of the layout in the Manage | Layouts window. (2 means that it's the second layout in that window's list.)

                                 Define a relationship such as:

                                 Globals::gSelectedLayout = ReportLayouts::ValueListName

                                 Define your value list with the "use values from a field" option and specify ReportLayouts::valueListname as the field used for this value list.

                                 Now your script can be a single step again using either the "layout name by calculation" or "layoutNumber By calculation" option:

                                 Go To layout [ReportLayout::LayoutName]


                                 Go To layout [ReportLayout::LayoutNumber]

                                 Both options require that you carefully keep the ReportLayouts table up to date, but it allows you to add new report layouts without having to redefine your value list nor the script its trigger performs. You just add a new record to the ReportLayout table and fill in the needed info in the new record's fields.

                            • 11. Re: Database Reporting

                                   Thanks for your patience, it's almost there but not quite.

                                   The ValueListNames are displayed in the drop down, when one is selected it appears in the Global text field correctly, there is a relationship between this Global field and the ValueListNames field in the table, which has the actual report name listed but for some reason, the wrong reports are being displayed.

                                   I've checked and double checked but can't find an error anywhere...


                              • 12. Re: Database Reporting

                                     Can you post the exact script that you are using? Are you using the layout number or layout name method?

                                • 13. Re: Database Reporting

                                       I'm using the layout name method and the script is:
                                       Go to Layout [ReportLayouts::LayoutName]

                                       I've tried running the Script Debugger but can't see anything wrong yet...

                                  • 14. Re: Database Reporting

                                         Did you enable the data viewer so that you can check the value of the global field and ReportLayouts::LayoutName when the script is executing?

                                         Are all of these fields text fields? Not number fields with text in them?

                                         If all looks correct, you might try adding a Commit Records step before the Go to layout step, but I wouldn't expect that to be necessary here.

                                    1 2 Previous Next