techtronix

ExecuteSQL with Charts

Discussion created by techtronix on Jan 25, 2018
Latest reply on Jan 27, 2018 by beverly

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?

Let ( 
thisyear =  Year(Get(CurrentDate)) ;

ExecuteSQL ( "select inspdate from inspection_parent where year(inspdate)=? order by inspdate";"";""; thisyear) )

Outcomes