13 Replies Latest reply on Mar 17, 2016 10:36 AM by haggart

    Totalling each of 4 "groups" with different Fiscal Years

    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!

      Thanks,

      M