7 Replies Latest reply on Feb 21, 2013 9:22 AM by philmodjunk

    Generating report based on parameters



      Generating report based on parameters


           Consider the following fields in several related tables: month, property, revenue of customer x, etc. I am designing a report that generates the revenue of a particular month for a particular property. Meaning the user should be able to select the month and the property, after leaving the field property a report based on those parameters should be generated on the same window / layout. The report is already running, my open item is how to allow for the users to enter the parameters and have the report show on the same window.

        • 1. Re: Generating report based on parameters

               I can't discuss the fine details without knowing the tables and relationships involved, but what you describe sounds like something where you can set up a pair of global fields for the user to select a month and a property and then a script can use that info to change layouts, perform a find and then sort as needed to produce your report.

               If so, you may find this thread that lists many different scripted find examples helpful: Scripted Find Examples

          • 2. Re: Generating report based on parameters

                 thanks, that's the general direction. 

                 The Layout Commission displays records from the table Request. There is a second table called Property. BTW, the tables Request and Property have a relationship via Request::PropertyID = Property::ID and on both sides the creation of records is allowed.

                 Now for the selection fields I am using pull down menus.  For instance I have a Property Value List that uses fields from Property::ID and Property::PropertyName (Also Display Values from Second Field, Include All Values, Show Values only from second field).

                 Correspondingly I have Property::Name field that is only for display purposes of the result of the selection in the value list.

                 What I need to do is:

                 Request::PropertyID = Property::ID. (... then need to call the script that sets the search fields, performs the find and displays the found Request records.

                 Where do I do it ? A script trigger after leaving the field and which one of both ?


            • 3. Re: Generating report based on parameters

                   A script trigger may not be the best option. A button may make it clearer to the user how to initiate the script to find records.

                   If you use script triggers on the selection fields, your script will need to check that there are values selected in both fields before producing the report.

              • 4. Re: Generating report based on parameters

                     OK, now: when I jump into the first field, that is the Property Value List of the table Property (don't forget, the focus of the layout is actually the table Request), I get the following message: No records are present. To create a new record choose the New Record menu command. Actually I don't want to create a new record. What I want to do is use the value list to filter the records in the table Request. I guess the pointer is in Nirvana, what I am missing ? 

                • 5. Re: Generating report based on parameters

                       You apparently have a found set of zero records.

                       More importantly, this tells me that this is not a field with global storage specified as you would not get this error message--even if there are no records at all in the table, if the field had global storage. This is one reason why it needs to have global storage specified. There are two other reasons:

                       2) In cases where the database is hosted over the network, global fields will keep the criteria entered by one user from having any effect on how the database behaves for other users.

                       3) Scripted finds can directly access the contents of a global field while in find mode. If you don't use global fields to collect the user specified criteria, your script has to copy the data into a variable before entering find mode and then it has to copy the data from there into your find request.

                  • 6. Re: Generating report based on parameters

                         I see what is happening now ! I get the message only after having entered parameters that yield zero records. When I go back to the fields to change the value, I get the error message. Meaning I probably need to make a Show All on entering any of the search fields. Is this the appropriate solution ?


                    • 7. Re: Generating report based on parameters

                           To repeat: You should not get this error message on your fields for specifying search criteria if they have global storage specified.