Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am trying to set a default current fiscal year and month in my current selection for sheet.
Any help would be appreciated.
Thanks!!
Could you use a Document Trigger that does a 'Select In Field' on the action 'OnOpen' of the document. You could then use a formula to find the current fiscal year (there may be a function for this), and set your select in field value to the calculated fiscal year. This would save you from hard coding the fiscal year into the field. You could do the same for the month.
Yes I did try 'Document trigger'/'OnOpen'/'Select In Feld'/'SearchString = varYear', defining varYear = year(today()) in load script but didn't work.
I ended up using 'Sheet Properties'/'Trigger'/'OnActiveSheet'/'SearchString = Year(Today(1)) it woked well for calendar year , but am looking for fiscal year here.
For list box of fiscal year am using this code in LoadScript
(case when month(CreatedDt) < ($(vFirstMonth) then cast(year(CreatedDt) as varchar(4))
Else cast(year(CreatedDt) +1 as varchar(4)) END) as 'Fiscalyear'
Have you double checked what your variables are populated as? I will add a text box to print the values until I am sure I have it right.
Sometimes I end up with null values in my variables even though my script is loading fine.
The Variable looks good. I can see current year.
can you find the current fiscal year by saying something like:
fiscal year = if( month(date) <9, year(date), year(date)+1)
I would use the variable checking method that DataGrrl mentioned as well. Text Boxes are perfect for that!
Why do you cast the Fiscal Year as a varchar?
For the OnActivateSheet Search String use
Search String = if( month(date) <9, year(date), year(date)+1)
yes varchar.
Didn't work
I also used this variable
Let varCurrentFiscalYear = if(Month(today())< 7, year(today()), Year(today()) + 1)
Of all this only Substring = Year(today(1)) works but which gives calendar year