2 Replies Latest reply on Oct 31, 2012 7:07 AM by Atkins

    Searching by Fiscal Year



      Searching by Fiscal Year



           I'm trying to create a report that searches by fiscal year and shows items that have a date in that range.  The "date" field is a calculation field based on other fields related to the record, and it's working well.  Because I'm searching by fiscal year, I know the beginning and end dates, and I want the user to give a single input as to what fiscal year they want to search.

           For instance:  I have a pop-up that asks, "what fiscal year would you like to search?" and prompt them to put in a 4-digit year.  If they input 2012 then I want to search the date range for 9/1/2012 to 8/31/2013.  I've tried several different iteration of calculations and scripts to try and make this work and am just not having any luck.  I also read a post that said to use a 'set field' script and put in a date range using the "..." operator, but I can't set the field I want to search because it's a calculation field and is not editable.  Maybe this is the root of my problem?

           Any feedback would be appreciated.  I think I need help with the logic as much as the actual scripting.  How do I get a year entry (2012) into a date range of 9/1/2012 to 8/31/2012 in my script?  And then how do I search my records using that info?


        • 1. Re: Searching by Fiscal Year

               In find mode, set field can enter search criteria into a calculation field so this is not a problem.

               Make your pop up field a field with global storage and then this script will find your records by the specified fiscal year:

               Enter Find Mode [] ---> clear the pause check box
               Set Field [YourTable::YourCalculationField ; "9/1/" & YourTable::YourGlobalYearField & "..." & "8/31/" & YourTable::YourGlobalYearField + 1 ]
               Set Error capture [on] ---> keeps no records found dialog from interrupting the script if no records are found
               Perform Find []

               It's also possible to define a "fiscal year" field in the records being searched that calculates the fiscal year and then your search criteria is simply the contents of your pop up field.

          • 2. Re: Searching by Fiscal Year

                 Thanks PhilModJunk.  That solution worked.  I was very close in my initial attempts but had a few syntax issues in my calculation.  Also, after I put in your code it wasn't working and I dug a little more and saw that I had originally created my global field as a date field, even though I was just inputting the 4-digit year into it.  Once I switched that to a text field it worked fine, so I had a few issues.

                 Thanks for the help...again!