I have a table with a Date field and an Hours field
How do I create a calculated field that calculates the sum of the hours for the last 30 days?
Both ExecuteSQL or a relationship could be used to do that. In either case, you'd sum a field over all records with dates from Get ( CurrentDate ) To Get ( CurrentDate ) - 30.
Define an unstored calculation field (cDateLess30 ) with a date result type:
Get ( CurrentDate ) - 30
Set up a relationship:
YourReport'sTO::cDateLess30 >= YourDataTO::DateField
Using that relationship, Sum ( YourDataTO::NumberField ) will sum NumberField over the last 30 days. A summary field defined in YourData and referenced using this relationship would also show the same result.
SELECT Sum ( NumberField ) FROM YourDataTO
WHERE DateFIeld <= ?";
"";"" ; Get ( CurrentDate ) - 30 )
do you need the running total from the oldest record and then detailing day by day for the last 30 days ? For example, say we are the 31st of October:
Much like a bank statement ?
philmodjunk suggestion will provide the total sum for the last 30 days.
It would look like this. Here it's done with 5 days
philmodjunk i tried this
ExecuteSQL ( "SELECT Sum (Daily_hrs) FROM (tblDate) WHERE (Date) < = ?" ; ";" ;Get(CurrentDate)-30)
Answer is ?
ExecuteSQL ( "SELECT Sum (Daily_hrs) FROM tblDate WHERE \”Date\” <= ?" ; "“ ; “” ; Get(CurrentDate)-30)
date is a reserved word. Needed pairs of double quotes for row and field separator parameters.
now it works, I had to do it with min instead of hh:mm
Thank you very much
You might consider doing your SQL queries like this:
Let ( [ @DailyHrs = SQLRef ( tblDate::Daily_hrs ; "Fld" ) ;
@DateTBL = SQLRef ( tblDate::Daily_hrs ; "TO" ) ;
@Date = SQLRef ( tblDate::Date ; "Fld" ) ;
SQLa = "SELECT Sum (@DailyHrs) FROM @DateTBL
WHERE @Date <= ?" ;
SQL = Substitute ( SQLa ;
["@DailyHrs" ; @DailyHrs ] ;
["@DateTBL" ; @DateTBL ] ;
["@Date" ; @Date ]
) ] ;
ExecuteSQL ( SQL ; "“ ; “” ; Get(CurrentDate)-30)
) // Let
The purpose of this is two fold:
SQLRef is a custom function and I have a tool set up with more custom functions such that I can type in the text between the quotes that is assigned to SQLa in the above example and the tool expands and builds the basic expression for me but with an "x" in place of any explicit Table::Field references. I can copy/paste that into the data viewer and finish the expression with a few double clicks and by doing it in the Data Viewer I can then test it to confirm what results will be produced.
The custom functions and more info about them may be found in this file:
Adventures in FileMaking #2-enhanced value selection
If you open the "flexibleSQL" tab in the "buttonbars as Value Lists" example.
This file is free to download
This file has multiple working examples in an unlocked file that you can take apart to study their design
Each example comes with detailed documentation on how they work
The Intro layout includes an "invitation" to say "thank you" via payPal for those so inclined to do so.
Retrieving data ...