14 Replies Latest reply on Apr 17, 2015 9:06 AM by justinc

    Fixing empty values in the result of a eSQL: an idea


      Hi all,

      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

                                                    15                                                  NULL

                             --->                 17           but wanted is                  NULL

      15                                                                                                15


      17                                                                                                17



      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 ...


      I use





      STRVAL(myValue) ||



             STRVAL(myValue) IS NULL

             THEN 'NULL'

              ELSE ''




      FROM ...





      hope this will help somebody.


      Attached: using 0 as default for empty (instead of NULL), in filemaker graph.