8 Replies Latest reply on Feb 17, 2014 5:44 AM by Fimano

    drop down list linking to reports

    nickeywalter

      hi file makers gurus!!!!

       

      I am currently working on a database....i have reports standing out each on a different layout..and i have a layout called Reporting tool where i want my users to select what report the want to view, this to be a drop down list and a "from" to "to" date sections....Now when in reporting tool, users can decide i want to view certain reports i.e. weekly report, from 3rd jan to 12 march, which would then link to that particular report layout and display only the report as entered from 3rd jan to 12 march. Please assist me this is my final step before i present the database.

       

      thank you all God bless

        • 1. Re: drop down list linking to reports
          empiricdev

          What version of FileMaker?

          • 2. Re: drop down list linking to reports

            Hi Nickey,

             

            Your Reporting layout should use global fields to accept the User values.  Create regular fields but then in Options > Storage, check 'use global storage'.  So you might have gReportName (text) and gStart (date) and gEnd (date).  Then place a button to the right of the fields called Run Report.  Script would do several things depending upon the report but here is basic approach:

             

            # make sure there are values
            If [ IsEmpty ( gReportName ) or not gStart or not gEnd ]
            Show Custom Dialog [ “You must select a value for each field.  Please try again.” ]
            Exit script
            End If

            This assumes you want something in every field.  But, you can also design it so that, if User does not enter an end date, script can assume it will find all dates past (and including) a start date entered. Same with start date … if empty but there is an end date, script can assume that it will find all records up to and including the end date. Change your portion in bold to test accordingly.  Either way, the script will work the same.  At this point then, the script is continuing because values were correct so continue with:

             

            Allow User Abort [ Off ]

            Freeze Window
            Go To Layout [ layout name by calculation ; gReportName ]
            Enter Find Mode [ uncheck pause ]
            Set Field [ yourDate ; gStart & “..” & gEnd ]
            Set Error Capture [ On ]
            Perform Find
            If [ not Get ( FoundCount ) ]
            Show Custom Dialog [ “No records found” ]
            # decide whether to include a Show All Records here (that is developer/user decision)
            Go To Layout [ original layout ]
            Halt Script
            Else
            # records found so sort your report, go into preview, pause, print or whatever else you wish to do.
            End If

            NOTES:

            • If FM receives a range search on a field as 1/1/2010.. and no ending, it assumes all greater records.  There is no need to insist on a date on each end of a span. 
            • If you change a layout name, remember to change the Report Name value list (if it is custom).
            • This generic script assumes you are always searching the same table:::Date.  If each report uses a different base table and field, you will need to include a branch in your script to handle each report (or group of reports) differently.
            • If your reports include a lot of aggregates, it is sometimes best to switch first to a null layout (no fields) and perform your find and sort there before switching to your final report.
            • You might wish to test that the end date is greater than or equal to the start date.  If vs. 11, you can use trigger to stop User immediately.

            I hope this gets you moving.  Ask questions as needed.

            • 3. Re: drop down list linking to reports
              nickeywalter

              hi LaRetta,

               

              Thanks alot for your support about the reporting option....One questions...in the reporting layout, when you say my reporting layout should use global fiels,, am assuming that in i have to created a table for the reporting layout, since this field show records from other tables..I have not understood how to set my report layout field to global in that they get records from other table..please see screenshots below..

               

               

               

                                   Tracking Activities Form Layout

              Screen Shot 2012-02-07 at 9.13.41 AM.png

                       Tracking Activities Report Layout

              Screen Shot 2012-02-07 at 9.13.19 AM.png

                      Reporting Tool

              Screen Shot 2012-02-07 at 9.21.41 AM.png

               

              My idea is when a user has keyed in all the information in the form and save...he can go to the reporting tool layout and have a drop down list with all the reports names...and can choose  date range and when presses find report...he is taken to the report layout showing the particular report he/she is interested in and within the date range....and if possible all the information to be in one single report....

               

              Thanks LaRetta....

              thanks in advance..

              • 4. Re: drop down list linking to reports

                nickeywalter wrote:

                ...in the reporting layout, when you say my reporting layout should use global fiels,, am assuming that in i have to created a table for the reporting layout, since this field show records from other tables..

                 

                The global fields can be placed in any table, Nickey, because global fields can be accessed from anywhere.  And the Reports layout can also be based upon any table because it will only be used as a stopping-point for the User, i.e.,  viewing the report options in form view, selecting their criteria, and then script taking them to the requested report in the Tracking Activities table where the data resides (or taking them to other tables depending upon the report requested). 

                 

                Most Developers create a one-record table which holds preferences, admin, globals and that works nicely for Main Menu or Reports Menu.  If there are many reports (and they have complex criteria), I create a Reports table to hold Report Name, base table and other information instead of custom value list.  It allows more flexible dynamic scripting of the reporting process.  I usually also have a virtual reporting table.  Creating all your global fields in a single table allows easy administration by the Developer.  Unless global is needed within a specific table occurrence for a relationship, they do not need to be in your data tables at all.

                 

                nickeywalter wrote:

                My idea is when a user has keyed in all the information in the form and save...he can go to the reporting tool layout and have a drop down list with all the reports names...and can choose  date range and when presses find report...he is taken to the report layout showing the particular report he/she is interested in and within the date range....and if possible all the information to be in one single report....

                 

                You envision correctly ... and the script I provided will give you what you wish.  So, once you decide where to put your globals and get them created, go to your Reporting Tool (your last screen shot) and re-specify the dates (double-click them) to the new global fields.  Then create your script.  If you get stuck again, let us know.  :^)

                 

                EDITED:  For some reason, it was messing up the quoted portions of the post so I removed them.

                 

                Message was edited by: LaRetta

                • 5. Re: drop down list linking to reports
                  nickeywalter

                  Am using filemaker pro version...

                  • 6. Re: drop down list linking to reports
                    nickeywalter

                    LaRetta....sorry for the bother once again....can u please send me your email address so i can attach my database and have you to have a look at it and may be help me with the first report generation which i can use as an example and work through to the rest....

                     

                    Thanks in Advance,

                    Kind Regards

                    Walter

                    • 7. Re: drop down list linking to reports
                      beverly

                      Walter, you can send a private message to anyone on this forum (there may be exceptions). Click on the name and in their Profile, there is an Actions link "Send private message" in the rightmost column. Click this and compose your message. They will be emailed a notice that they have a private message and must retrieve in the forum.

                       

                      This does not seem to allow attachments, but is a way for you to communicate email addresses privately, should you so desire.

                      I've sent to you a private message as a test.

                       

                      Beverly

                      • 8. Re: drop down list linking to reports
                        Fimano

                        Great post, LaRetta.

                        I have done it along the same lines. I just changed the script (and layout names) to gol along the principle of:

                        "Go To Layout [ layout name by calculation ; gReportName ]"

                         

                        I am in the process of combining the report specification layout with this:

                        http://filemakerhacks.com/2012/03/30/user-friendly-excel-exports-part-5/

                        Because until now, we [the users, that is] have been satisfied with looking at the preview window that I pop up (flipped to the last page, showing grand total), and then just saving to Excel from the toolbar. The sub-summaries (groups) did not go into Excel, but I just used the FMP12 trick to include those same fields off-screen, to the right of the body part.

                        However, the summary field used to count the (sub)totals goes into Excel as well. And the column names are internal field names.

                        Hope it works out well.

                         

                        Your notes are great too. I did what you suggest, making users able to use the same date selection for different report groups (TOGs).

                         

                        Thanks,

                        Jens