I'm finding your description of what you are trying to achieve a little difficult to understand. It seems you have a Fund Table. Are there 4 records in that table - one for each fund? What other tables do you have? What are the relationships between the tables? You mentioned TOs for each fund - with start and end global dates - what table is on the other side of that relationship? Would like to help, but need more information.
Agree with Chris.
Right now, there is no problem statement.
Just some rambling about SQL, which for all we know, is functioning correctly, as it appears it should.
I apologize for not being clear. I manage a lab with 4 sources of money.
Fund A starts on June 1
Fund B starts on July 1
Fund C starts on August 1
Fund D starts on September 1
I want to be able to track the fiscal year spending for each fund.
I have a Fund table with 4 records
I have an Order table with (among other fields) date ordered, year ordered, fiscal year ordered
I have a Dashboard table with (among other fields) global start and end dates
My 4 TOs are of the Order table, the Dashboard is on the other side of the relationship.
I used the order table because that's where I created the custom fiscal year calculation for each fund.
In matching related records between the Dashboard and Order tables I used the global start and end dates from the Dashboard to match the the fund specific fiscal year calculation. I have a constant for each fund in the Dashboard and that joins with each of the kfFund fields for each of the 4 TOs. The constant is a calculation = "Fund1"
FYordersFUND1 ------------------------- Dashboard
kfFund = ConstantFund1
DateOrderFYFund1 >= gStart
DateOrderFYFund1 <= gEnd
Using this set up I get nothing when set up a chart on the Dashboard. Using the the calculation:
Sum(FYordersFUND1:TotalCost) & ¶ &
Sum(FYordersFUND2::TotalCost) & ¶ &
Sum(FYordersFUND3::TotalCost) & ¶ &
I hope this is clearer and I do appreciate your patience Chris.
Your relation is connecting with FY(year) and gStart/gEnd(date).
If you use start/end date, (fiscal)year may have no use.
I think this is going to be a lot easier to handle if you'll upload your file; or a clone; or a simplified example.
Though if you use a clone; I think we'll need to see at least example data.
There is just way too much room for confusion when attempting to follow your description.
For example DateOrderFYFund1, is that really a date field?
Is gStart really a date field? Are the calcs and totals being generated in the dashboard table?
haggart, on dashboard perhaps you expect to get 4 values, so you need GROUP.
WHERE \"Fiscal Year Ordered\" = ?
GROUP BY kfFund
I thought briefly I saw that you had submitted a simplified example file; very helpful. But it seems to be gone now.
OK, I went through the Months and Dashboard tables and corrected my Calculation results.
Dates are dates
Text is text
Numbers are numbers
(My database is based on a file from Filemaker Academy - on Dashboards. Obviously I didn't pay close attention to the expression details)
Thank you Bruce.
I suggest you continue posting the updates as you make them.
Though I hinted at some of the problems, others exist.
For instance - your end date calcs are WAY more complicated than necessary.
First of all; learn to use the basic date calculation:
Date( month; day; year )
Filemaker help for the date function.
Note in particular the use of zero for the day.
Day zero of any month is the last day of the prior month.
So your DateEnd would be
Date( MonthNumber + 1; 0 ; Year ( Dashboard::gCurrentDate ))
Note in particular that this is NOT a date; it's just a badly written division expression.
MonthNumber & "/" & 1 & "/" & Year ( Dashboard::gCurrentDate ))
Thank you Bruce. You've given me a lot to consider. Now off to make some progress...
Ps. I also have to note that the expressions that I've used were lifted from