13 Replies Latest reply on Jul 27, 2013 11:07 PM by Peter Wagemans

    SQL Select Returned Data into variables


      Im running a SQL SELECT statment and set variable with the result. but the problem is the result has more than 1 field and i see the result in the variable like: ellen, kigirman (the expression is selecting firstname, lastname) so here is my statment:


      SET Variable $result ; Value: executeSQL (



      SELECT firstname, lastname FROM clients WHERE c_id = " & $$var_userID1 & " and cc_Id = " & $$var_SelID & "



      ; "" ; "" )


      I'd like to put each info on each variable, lets say "firstname" on var: fname and "lastname" on var: lname


      and not the way it is returning both field in the same line separated by coma.



        • 1. Re: SQL Select Returned Data into variables
          Peter Wagemans

          SET Variable $result ; Value:


          let (


          myResult = executeSQL ( "SELECT  firstname, lastname FROM clients WHERE c_id = " & $$var_userID1 & " and cc_Id = " & $$var_SelID & " ; ¶ ; ¶ ) ;

          $firstName = GetValue ( myResult ; 1 ) ;

          $lastName = GetValue ( myResult ; 2 )

          ] ;

          ValueCount ( myResult ) // just to return something useful


          • 2. Re: SQL Select Returned Data into variables

            The script as a whole might be more readable if the variable-setting happens in Set Variable script steps rather than in a Let function:


            Set Variable [$clientData; Value:ExecuteSQL ( "SELECT ..." ; ¶ ; "" ; $$var_userID1 ; $$var_SelID )]

            Set Variable [$nameFirst; Value:GetValue ( $clientData ; 1 )]

            Set Variable [$nameLast; Value:GetValue ( $clientData ; 2 )]


            P.S. There's a discussion about evolving best practices for formatting ExecuteSQL usage on FileMakerStandards.org. The salient points for this example are:

            1. Use the ExecuteSQL function's extra parameters to quote data rather than doing it yourself. Ex: ExecuteSQL ( "SELECT ... WHERE id = ?" ; "" ; "" ; $id ) is better than ExecuteSQL ( "SELECT ... WHERE id = " & $id ; "" ; "" ).
            2. Wrap field and table references in your SQL queries in custom functions so that your queries still work if you rename any of your tables or fields.
            • 3. Re: SQL Select Returned Data into variables

              Thank you Peter, jbante.. it worked very well... If you guys don't mind I have another question. The biggest difference for me between Programing in VBA Ms Access and Filemaker is to work with unbound fields, since you can't just put a text box or combobox in the layout without been assoiated with a field, (please let me know if there's a way for that because i didnt figure out it yet). So, lets say I have a file in filemaker server, and a table with field "accout type" is the as global field ok, if i log into the file and put a info, when someone else connect into the sale file, it you show for him the same info right? I mean if I put on my screen acc_type: Personal, them someone opne the file in the order side, change this value to Business, them it will show for me his information and my question is what if I a working with this field as variable, how can I have it only for my end and if someone open the same file the information wont be shared.



              • 4. Re: SQL Select Returned Data into variables

                If any field is a global, then the info in that field only save for that user during that session.  If you close out of it, it will not save that information in the global field.  It will not show it to anyone else.


                If you need it to be personalized to you and saved between, then you could make a preferences table, where you can save account type (as not a global), then copy that value to a global account type field after it has detected you (and not someone else) are logged in.

                • 5. Re: SQL Select Returned Data into variables

                  Great ideas for "ui" on the queries, y'all. Thanks for sharing J! Bruce Robertson suggested the substitute to me shortly after the "eSQL missing reference" <http://www.filemakerhacks.com/?p=6605> was published. Great way to avoid the changing table/field naming problems.


                  As for 'standards', I'm still leaning to the ";" and "," separators at the BEGINNING of the lines. And the "~" is used in EDI frequently (and/or web), so I avoid using it like the plague for variables. The "sugar" was giving me diabetes, so I didn't agree with Brian's methods. But that's me and not really helpful to this topic for poliveira.


                  Is "readability" the primary reason, J, for using separate variables instead of one Let()? I can see your point. I opted to put many things into variables for debugging, but they were not really necessary.



                  • 6. Re: SQL Select Returned Data into variables

                    well I must be doind something wrong them.. because when I close the file and open it again the value on global field still there. What should I do or any confuguration to check?


                    • 7. Re: SQL Select Returned Data into variables

                      is this "hosted" by FMP and you are the "host"? or is this with FMServer?


                      If you are the owner of the file (as host), your interaction sans FMServer will change the global and make it "stick".



                      • 8. Re: SQL Select Returned Data into variables

                        I tell you how it is working... I have the file on FM Server. Im connecting from my iphone as guest and i put the user name and password in the layout on global value to log in.. so fine. but I close the file and if I open again the information still there, the fields are not blank. is that how works, i have to run script to clear the fields or it shoudnt have the info of my last section there?



                        • 9. Re: SQL Select Returned Data into variables

                          How are you closing it? Are you just pressing the home button on the iPhone?  In that case, you're not actually closing the file, just suspending it.

                          • 10. Re: SQL Select Returned Data into variables

                            No, Im closing pressing in the top tool bar in the left and then press the red X to close it.

                            • 11. Re: SQL Select Returned Data into variables

                              There's no pleasing everybody. I'm curious to hear if you have a rationale to prefer argument delimiters as the beginnings of lines, or if it's just a personal taste, but this is not the thread for it. This is, though. The substitute pattern is falling out of favor with me, personally; the readability severely breaks down with larger queries.


                              Readability is my primary motivation for setting variables with multiple Set Variable script steps — the more logic the next developer (who is not you, doesn't know you, can't contact you, and is completely differently informed than you) can see without opening up modal dialogs, the better — the same reason Enter Find Mode > Set Field > Perform Find is more readable than Perform Find [Restore].

                              • 12. Re: SQL Select Returned Data into variables

                                Thanks, J. My preference because I use several "languages" for scripting (JS, PHP, C, etc.) beside FMP. I've adopted the usage as more "readable" and easier to spot errors (an extra "," or ";", for example) in my code.



                                • 13. Re: SQL Select Returned Data into variables
                                  Peter Wagemans

                                  It's important that you understand how globals work.


                                  Assuming that "host" means the application that makes the file available on the network, and "guest" means the application that connects to that host over the network:


                                  1. When a global is set by the host of a file, it's saved in the file when the file is closed.

                                  2. When a global is set by a guest of a file, it's not saved with the file.

                                  3. When a global is set by any user of a file ( guest or host ), the global is not changed for any other user of the file that currently have the file open.

                                  4. Guests logging in to a hosted file, receive the value of the globals as they were last set by the host, or when the host did not touch them yet, they initialize to the value they had when the file was last closed by the host.

                                  5. FileMaker Server does not change globals in a file.

                                  6. Server side scripts, IWP scripts or CWP scripts are running as a guest of the host, so the rules for globals for guests apply.


                                  What does this practically mean to you? As soon as you move your file to a FileMaker Server, there will be no way anymore to change the value the globals initialize to. The best practice here, is to clear all globals in your close script, especially when you are still the host of the file with Pro, Advanced or Go. Use a startup script that initializes the globals. Remember that globals that are already initialized on a host, broadcast their value before anything can happen on the guest, even the startup script. When that value consists of a lot of data, like a container value, this can cause a serious lag when logging in over a slow network.


                                  Please re-evaluate the problems you are having, apply the rules of the game to them. It should make sense to you then.