Script to find records with future years

[deego55]

I have a need to develop a script that will search for funding records for future years.

In my system i can create related funding records, and assign them a fiscal year (e.g. 08/09).

I already have a field that calculates the current fiscal year based on the current date.

Is there a way to do a search for any funding records that are in the future (beyond the current fiscal year)?

I am not sure if a ">" will work since the year is a text field like 06/07, 07/08, 08/09, etc.

Thanks for any guidance

[mr_vodka]

You can try something like this I guess:

Lets say your fiscal year covers October 1st 2006 - Sept 30th 2007, and you just want to select 06/07 to find all those records. You can do a script such as

Set Variable [ $yearstart; 2000 + Left ( gYearSelect; 2 )]

Set Variable [ $yearend; 2000 + Right ( gYearSelect; 2 )]

Enter Find Mode []

Insert Calculated Result [ yourTable:TranactionDate; Date ( 10; 1; $yearstart ) & ".." & Date ( 9; 30; $yearend) ]

Perform Find []

Assuming that your pulldown to select which year will always be in ##/## format that is.

[deego55]

thanks vodka.

yes pulldown will always be ##/## from 97/98 (1997/1998) to 19/20 (2019/2020).

can you clarify why i need to do the calculated result? there is never an individual date associated with the records, just the fiscal year.

maybe i can use the variable methods to just get the year end and do a search for greater? although there are some 19## and 20## so not sure if that will work either.

thanks again

[comment]

I think you will need to make the conversion in a calculation field. Either calculate 2004 from "04/05", or (preferably) enter 2004 and let "04/05" be a calculation.

[deego55]

yes comment you are exactly right. i forgot that i already did that for another purpose a while ago. so all i need to do now is a greater than search on the "2004" field.

sometimes the answer is so obvious it escapes me!

[T-Square]

I'd go a different route: I'd simplify my FY calculation so that the result is a number that is a particular year (e.g., 2007) and do a simple find on the numeric result (that is, ">2007"). You can always have a different field that displays "06/07" if that's what you want, and all you need to do is make sure you agree with yourself that FY "2007" equates to "06/07".

Also, I don't know what your calculation for FY is, but it could be as simple as:

Year(SourceDate) + (Month(SourceDate) > 6)

Finally, if you DO decide to use a simple year to designate the FY, you'll need to replace the existing entries with the right 4-digit year. This calculation should work:

1900 + (100 * (Right(SourceField; 2) < 50) + Right(SourceField; 2)

This assumes you're using the later year as the FY designator (i.e., 2007 for 06/07); if you use the first part, change the "Right" function to the "Left" function.

David

[deego55]

thanks T-Square. i was able to use your 4-digit fiscal year calc.

I have my report/script working exactly like i needed it.