In the calculation dialog box, try selecting "date" from the "Calculation Result Is" drop down menu
I'm guessing you need to adjust the SQL statement so you convert the query result dates into *real* FileMaker dates. Right now they're just text to the charting engine.
See this post on a handful of ways to do just that: http://www.seedcode.com/executesql-date-formats/
Sorry. I misunderstood. I thought you were talking about a calculation field. Like Perren says, I think you are going to need to convert the text result to a FileMaker date format.
There is a good post.
madmike6537, your sql returns only one column of date, so
Substitute ( ExecuteSQL ( ...your params here ... ) ; "-" ; "+" )
and use it in graph data as "date" will resolve. Do you have your format in list under there ?
"+" is used for Japanese Emperor Year Date separator.
Hmmm. Ok it seems like that post should be telling me exactly what I need to know, but I cant get it for some reason..
ExecuteSQL ( "SELECT DISTINCT, || Reading_Date FROM MATERIAL_READING WHERE \"_Room_KF\" = ? ORDER BY Reading_Date"; ""; ""; level_ROOM::_Room_PK)
What is the syntax exactly? What do the two bars (||) do? It seems he has extra quotes in his example.
Hello and thanks,
I tried changing my seperaters into ; "-"; "+" but I am still getting my result as ? with that change unfortunately.
Ok got it! YAY.
I read in the comments of that article that it is actually two single quotes, not a pair. Works now!
ExecuteSQL ( "SELECT DISTINCT '' || Reading_Date FROM MATERIAL_READING WHERE \"_Room_KF\" = ? ORDER BY Reading_Date"; ""; ""; level_ROOM::_Room_PK)
Ah, yes. I missed the double single quotes.
It's SQL for an empty string, much like FileMaker uses double regular quotes for the same.
The || (double pipe) is a text concatenation operator, like & is in FM.
I can't believe I didn't see that. Nice job on getting it to work!
Perren refers to a "handful of ways" and the link refers to one way. Here's a SQL-way:
SELECT DISTINCT STRVAL(Reading_Date)
this will give you the date format based on location, so for our non-US folks: dd/mm/yyyy, US: mm/dd/yyyy, instead of yyyy-mm-dd. this works on timestamp values, as well.
thanks! Another great way to do it.