Sum Field between Date Range (w/o Sorting)
Using FileMaker Pro 10 (not Advanced) on Mac OS 10.5.8 with database of equipment (date of install, serial number, location, etc).
October 2008 began importing Excel billing invoices into a separate table that related to the original Inventory table by the serial number field and viewed on the original table through a portal.
Presently have 17 months of monthly invoice amount per serial number (represents 17 months times 245 devices) which I can get a "running total" in a summary field. But I want a "final cost" for a specific timeframe (FY09 Fiscal year cost from Oct. 1, 2008 thru Sept. 30, 2009) for each device. This number should be a fixed number that does not change so I can use it for other calculations and should not to change if I sort the data based on other criteria.
I have tried to Sum, GetSummary, If calculation, and Case calculation. I either get a $0 or a blank return. Applicable fields called "Inv_Month_Year" (date field like 10/1/2008) and "Total_Mo_Cost" (number field like 140.93 which is currency).
This database is not shared and only on my computer (not published) for my own management of these devices.
I am a self-taught FileMaker user since 1996 and consider myself a Intermediate user. But I haven't ventured into using Scripts.
Thanks in advance!