AnsweredAssumed Answered

Totalling each of 4 "groups" with different Fiscal Years

Question asked by haggart on Mar 13, 2016
Latest reply on Mar 17, 2016 by haggart

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.


ExecuteSQL("SELECT SUM(TotalCost)

FROM Orders

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!