5 Replies Latest reply on Sep 11, 2012 11:07 AM by disabled_mr_vodka

    Strange results using ExecuteSQL and merge variables


      I am getting some strange results when trying to use ExecuteSQL to populate merge variables on my layout.


      I am using a text field with a let statement in a conditional formatting calculation to assign data to my variables, but it seems that the ExecuteSQL statements aren't always executing.


      To confirm that it wasn't something specific to my database I downloaded John Ahn's dynamic value list demo which is using the same technique:




      I went to the ID_Values layout, and as I navigate through the records the data on the screen does NOT refresh correctly.


      If I go to Records - Refresh Window it fixes up the current record, but when I navigate to the next record it will be wrong again.




      I am on record 1 which shows:


      I then navigate to record 2 (Note that the ID's on the right side have changed but the names have not) which shows:


      Then I refresh the window and it shows:



      I am running FileMaker Pro Advanced 12.0v2


      I have tried this on both a PC running Windows 7 and a Mac running OS X 10.6.8


      Is anyone else experiencing this issue or has anyone found a fix?





        • 1. Re: Strange results using ExecuteSQL and merge variables

          Is the object with the conditional formatting calculation that sets the merge variables below the text objects displaying those variables? I don't mean undernead with exactly the same coordinates — just "underneath" in the z-order for the layout. Try selecting the object with the conditional formatting and sending it to the back with Arrange > Send to Back.


          I've seen this kind of problem when using the technique of setting merge variables with conditional formatting calculations when the object with the conditional formatting is rendered after the object displaying the merge variable. Objects further back in the layout's z-order — including their formatting calculations and merge variables and fields — are rendered before objects further forward in the layout. When you switch to the second record, the layout renders the merge before recalculating values for them because they're further back in the z-order.

          • 2. Re: Strange results using ExecuteSQL and merge variables

            This is the expected result when using merge variables. If you want your merge variables to be displayed accurately FMI states you must refresh for each record either manually or via script as it is being browsed, previewed or printed.



            • 3. Re: Strange results using ExecuteSQL and merge variables

              I have also seen this happen when the conditional format text is a different size, a different location, over the merge variable, etc. etc.  I have pretty much solved it by putting the conditional formatting ON the merge variable text box.  That way they always render together.  It's also convenient for me - I don't have to look for the conditional format text box to edit the merge variable or move things around on the layout.


              This works so well that even when I have multiple merge variables on a layout, as well as multiple records in a LIST view, the correct data shows without a refresh for each record.  At least so far!  ;-)


              Running FMP12v2 on MacOS 10.7.4 and on MacOS 10.8 


              Also tested on FMP11v4 on WinXP and Win7


              Hope that helps..



              • 4. Re: Strange results using ExecuteSQL and merge variables

                On a side note it's interesting that the SQL calc won't run if it's "off" the layout - you know, in that new offside bit. As long as a least one pt is "on" the layout then it works. I haven't tried any other calcs to see if their offside placement affects them.



                • 5. Re: Strange results using ExecuteSQL and merge variables

                  My apologies. When I brought this demo to DevCon it was an older copy, which I didnt realize until now. ( sloppy by me )


                  All you should have to do is put in an OnRecordLoad and reference the freeze window script.