2 Replies Latest reply on Feb 3, 2013 11:35 AM by pthomas

    Charts powered by ExecuteSQL not refreshing on iOS

    pthomas

      Hello,

       

      I seem to be having trouble getting my charts to refresh when new records are entered.

       

      I have a very basic database with a single layout that allows a user to enter a date and their weight, then there is a chart that plots the last 7 days worth of data below the data entry fields:

       

      When I create a new record and enter in a new date and weight the chart does not update. Even if I manually refresh the window or navigate to another record and back it doesn't update however If I exit the database and re-open it will be updated.

       

      Running the solution on my desktop refreshes the chart correctly.

       

      I have tried a few different things to get this working, but none seem to have done the trick:

       

      Embedding the ExecuteSQL commands directly into the chart.

      Having OnRecordLoad and OnRecordCommit execute a script to Refresh and/or Freeze the window.

      Having the chart based on global variables, and using a script to run the ExecuteSQL command to update the variables - the script is called on both OnRecordCommit and OnRecordLoad.

       

      Currently I have the chart set up as follows:

       

      X-Axis Data:

       

      GetValue ( $$date ; 7 ) & "¶" &

      GetValue ( $$date; 6 ) & "¶" &

      GetValue ( $$date; 5 ) & "¶" &

      GetValue ( $$date; 4 ) & "¶" &

      GetValue ( $$date; 3 ) & "¶" &

      GetValue ( $$date; 2 ) & "¶" &

      GetValue ( $$date; 1 )

       

      Y-Axis Data:

       

      GetValue ( $$weight ; 7 ) & "¶" &

      GetValue ( $$weight ; 6 ) & "¶" &

      GetValue ( $$weight ; 5 ) & "¶" &

      GetValue ( $$weight ; 4 ) & "¶" &

      GetValue ( $$weight ; 3 ) & "¶" &

      GetValue ( $$weight ; 2 ) & "¶" &

      GetValue ( $$weight ; 1 )

       

      I have a script that sets the two global variables with the following values:

       

      $$date =

      Let ( [

      $date = "

      SELECT
      wt."dateLogged"
      FROM
      weightTracking wt
      WHERE
      wt."dateLogged" <= ?
      ORDER BY
      wt."dateLogged" DESC
      ";

      $dateResult = ExecuteSQL ( $date ; "" ; "" ; weightTracking::dateLogged )

      ] ;

      $dateResult

      )

       

      $$weight =

      Let ( [

      $weight = "

      SELECT
      wt."weight"
      FROM
      weightTracking wt
      WHERE
      wt."dateLogged" <= ?
      ORDER BY
      wt."dateLogged" DESC
      ";

      $weightResult = ExecuteSQL ( $weight ; "" ; "" ; weightTracking::dateLogged )

      ] ;

      $weightResult

      )

       

       

      I have another database that is very similar but it requires more data entry so I have the charts sitting on a different layout, so far I haven't seen any issue with those charts not refreshing when I view them however I don't really want to have to have a second layout with the charts on it for this database as there are only 2 data entry fields.

       

      Has anyone else come across this sort of issue?

       

      Any ideas on how to force the chart to update?


      Cheers,

       

      Paul.

        • 1. Re: Charts powered by ExecuteSQL not refreshing on iOS
          FileKraft

          i can confirm that charts don't update just by using complexer funcitons (custom functions) and don't update even thru explicit refresh & cache . i have an example without using executeSQL instead using some home made custom functions and all works well on client desktop but not at all on iOS. the (ugly) workaround is to change scope by going to another layout and returning to the original layout running all triggers to recompute the function in use by the chart.

          • 2. Re: Charts powered by ExecuteSQL not refreshing on iOS
            pthomas

            Hi FileKraft,

             

            Thanks for looking into this and for the suggestion to try moving to a different layout, however it doesn't seem to work when using ExecuteSQL.

             

            I tried changing the record commit script to go to a blank layout and then back to the original layout again with no luck.

             

            I also tried duplicating my data entry/chart layout and then having an on record commit trigger on both of the layouts to move to the other one when a new record is saved, also no luck!

             

            Just to confirm that the ExecuteSQL was actually firing correctly I added my global variables into merge fields on my layout and they update correctly as I navigate between records and when I add new records.

             

            It would seem that the issue is with the chart itself not refreshing correctly - I guess its a bug?