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

    Australian financial date calculation help


      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

          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

            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

              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