3 Replies Latest reply on Jun 7, 2016 2:38 PM by erolst

    Australian financial date calculation help

    ajayz

      I would like to be able run a number of reports that will always show the current Australian financial year, without having to change the find date range manually.

       

      i.e   1st July 2015 to 30th June 2016 and so on for the next financial year.

       

      I need the date range to automatically update depending on the current date when the user logs in.

       

      I was thinking that I would need to use a Global field set as a DATE field  g_Start  and another one for the end date field g_End

       

      I thought I would need add a SET FIELD to my opening database script to work out the current date and populate the global fields.

       

      Using something like:  set field [Rent::g_Start; Date (7;1;Year ( Get ( current date ) ))]

       

                                          set field [Rent::g_End; Date (6; 30;Year ( Get ( current date ) ))+365]

       

      I then realised this wasn't going to work..

       

      Has anyone a solution ?

        • 1. Re: Australian financial date calculation help
          erolst

          You don't need global fields; script your find portion of your report script as

           

          Enter Find Mode

          Set Field [ Table::dateField ; Let ( y = Year ( Get ( CurrentDate ) ) ; Date ( 7 ; 1 ; y - 1 ) & ".." & Date ( 6 ; 30 ; y ) ) ]

          Perform Find

           

          If this range expression is required in different places, and you have FM Advanced, create a Custom Function .

          1 of 1 people found this helpful
          • 2. Re: Australian financial date calculation help
            ajayz

            Thanks for you help, however I it seem to removed a date from my record.

             

            I decided to use the script attached to a button, so I can see all records and then press the button to only see the records for the financial year and another button to show all records.

            Here's all the test records prior to running the script:

             

            Screen Shot 2016-06-08 at 7.20.22 AM.png

             

            Run the script attached to the button: - it works and shows all the records

             

            Screen Shot 2016-06-08 at 7.20.50 AM.png

             

            When I click show all records, one of the dates is missing?

             

            • 3. Re: Australian financial date calculation help
              erolst

              Your script is not exactly what I suggested; most importantly, it doesn't Enter Find Mode, so you are using Set Field in Browse mode and overwrite your date field with the range expression - that is not a valid date, hence the ?.

              1 of 1 people found this helpful