we do a lot of graphic representation of measurements data extracted via ExecuteSQL from Filemaker tables.
We use the very powerful xmChart plugin for plotting the data.
The problem is that the lists we calculate have often segments of missing data, like 20 in a row.
Otoh, xmChart expects a value for each list element, and a ¶ will simply cause the list to shrink, causing a misalign of the y data.
What it wants (and will accept) is NULL as a placeholder for no data.
20 20 20
30 30 30
---> 17 but wanted is NULL
Of course the received list can be post processed, with loops or recursive custom functions, but what I wanted was to get the data already ok.
I also did not want to create a calc field in the original data and pull that via SQL as the data files can have milions of records and adding a calc would have been a byte waste.
I think that others might have similar problems (maybe want a 0 instead of my NULL) so here is the way I solved it:
Instead of SELECT myValue FROM ...
STRVAL(myValue) IS NULL
hope this will help somebody.
Attached: using 0 as default for empty (instead of NULL), in filemaker graph.