Again I return to the Community for help because I'm going nuts thinking about this puzzle.
I have a database that tracks spending for four funds in our laboratory. Each grant has a different fiscal year.
I searched around and found Brian Dunning's FM custom function sharing. I've successfully set up, replaced field contents and confirmed results for the function:
FiscalYear ( TheDate ; FYStartMonth ), so a funds that starts on September 1 is:
The calculation field DateOrderFYfund1 is FiscalYear ( DateOrdered ; 9 )
My next step was to write some SQL to run the calculation. In a Fund Table I have attempted to to set up calculations for each of the 4 funds, without success. My current bar graph shows the same amount for all 4 funds. The number doesn't make sense.
WHERE kfFund = ?
AND DateOrderFYfund1 = ?";"";"";Orders::kfFund;Year(Get(CurrentDate)) )
I also tried to go the TO route and set up one for each fund and linked start and end date global fields to the DateOrderFYfund1 field without success.
I have a dashboard set up, so that I can easily look and see the lab spending at the moment.
Right now I feel overwhelmed and I know I should go read more about SQL, but it's funny that a simple query should be so difficult.
Any and all advice is appreciated!