I am new to filemaker charting features....
I am attempting to place a chart on an opening layout (dashboard) in a FM solution. The chart pulls data from tables not in the layout so I am using ExecuteSQL() to get the data. I am running the SQL directly in the chart object calculation on the X and Y axis (X axis is date grouping, Y axis is a count() of inspections). the performance is terrible on 4500 records making the screen take several seconds to load. Is there a much better option? Calc field in a table perhaps?
Secondly, this is simple X axis calculation. I cant seem to get it to resolve the "year()" in the where clause of the SQL. What is the syntax to pull a year (or even month) from a date field?
thisyear = Year(Get(CurrentDate)) ;
ExecuteSQL ( "select inspdate from inspection_parent where year(inspdate)=? order by inspdate";"";""; thisyear) )