5 Replies Latest reply on Jan 17, 2014 11:25 AM by philmodjunk

    Create Report Script by year

    dg3321

      Title

      Create Report Script by year

      Post

           I have a portal that tracks expenses for various projects per year.  At the top of the page, i have buttons (for each project) which then take you to an itemized report of the expenses.  Essentially those buttons are scripted to find all expenses under 'Project A', and sort the expenses based on the year.  There is 1 record for each year.

           Now that we're in a new year, i created a new record and have started listing the expenses, however when i click the project buttons to create the report, its still showing the 2013 expenses b/c the 'find' in the script says to show only the 2013 expenses.  I have a "Year" field in each record.

           How to I write the script to make the button create a report based on the year of that exact record?

        • 1. Re: Create Report Script by year
          philmodjunk
               

                    How to I write the script to make the button create a report based on the year of that exact record?

               Use set variable to capture the year of that record. If you have a date field, that might require using Year (YourTable::DateField) to extract the year from that date field.

               Then use the value of that variable as criteria in your find. This method is compatible with the scripted find examples you can find here: Scripted Find Examples

          • 2. Re: Create Report Script by year
            dg3321

                 Do all fields in the portal need to be a global field or just the fields being used in the search criteria?  Should the 'Year' field (not in the portal) be a global field?  When I created the global fields, thats when things got tricky.

                 Here is the script:

                 Go to Layout ["Taxpayer Report" (Taxpayers Expenses)]

                 Enter Find Mode []

                 Set Field [Taxpayers Expenses::Year]

                 Set Error Capture [On]

                 Perform Find [Restore] <- this is where i ask it to find the project based on the button clicked.

            • 3. Re: Create Report Script by year
              philmodjunk

                   You may not need any global fields. Global fields are a way to set up a form where a user enters search criteria while in browse mode and then the script enters find mode and uses that data to construct a find request.

                   In your case, there may or may not be a need for such fields as it depends on how you need your script to work.

                   But above all, DO NOT, change any of the actual data fields being searched to global storage. Not only will this not work for the scirpt, you will lose the individual values stored in each record. in those fields.

                   Set Field [Taxpayers Expenses::Year]

                   is missing a parameter.

                   Perform Find [Restore]

                   If you use set field to set up your criteria, this last step can just be:

                   Perform Find []

                   That way, all of the criteria used in the find is immediately visible when you examine the script. You don't have to open up a dialog in order to see what criteria was used.

              • 4. Re: Create Report Script by year
                dg3321

                     Ok, i think you are correct in that i shouldn't need a global field.  The user does not need to enter any search criteria as that is already fulfilled by a. being in the specific record (The Year), and b. clicking on a certain project's 'Report' Button.  What I need is for when that button is clicked for the find to search a. the year listed on that specific record and b. the project name (specified in that button's script).

                     Here is the updated script:

                     Go to Layout ["Taxpayer Report" (Taxpayers Expenses)]

                     Enter Find Mode []

                     Set Field [Taxpayers Expenses::Year; Taxpayers Expenses::Year]

                     Set Error Capture [On]

                     Perform Find [Restore]    ...this is where i tell it to search for the specific project name.

                     When i apply this script, the report shows expenses for both 2013 and 2014 regardless of which record i'm in.  I need it to only show 2013 expenses when i'm in the 2013 record (and click the desired project report) , and same w/ 2014 and so on.

                • 5. Re: Create Report Script by year
                  philmodjunk

                       You aren't using the variable that I recommended. When you enter find mode, Taxpayers Expenses::Year goes blank so the set field can't access the value of that field--that's where you need to use either a variable or a global field as their data remains accessible when in find mode.

                       I'd do it this way

                       Set Variable [$Year ; value: Taxpayers Expenses::Year ]
                       Go to Layout ["Taxpayer Report" (Taxpayers Expenses)]
                       Enter Find Mode []
                       Set Field [Taxpayers Expenses::Year; $year ]
                       Set Field [Taxpayers Expesnses::ProjectID ; Get ( ScriptParameter ) ]
                       Set Error Capture [On]
                       Perform Find []

                       I'd then put the project ID in a script parameter specified for the button so that different buttons can specify different projects, but use just one script for all the buttons.