1 Reply Latest reply on Sep 24, 2012 12:30 PM by philmodjunk

    FileMaker 12 ExecuteSQL and valuelist popupmenu parameters?

    LukeWallace

      Title

      FileMaker 12 ExecuteSQL and valuelist popupmenu parameters?

      Post

           Ok, I have a popupmenu with 3 options (System1, System2 and System3)

           I'm new to FileMaker but I believe you guys call this a valuelist?

           I also am using a executesql function that looks like this:

            

           
      NOTWORKING:
           
                 
           
                SELECT DISTINCT a."System"
           
                FROM "CommercialLight" a
           
                WHERE a."System" LIKE popupmenuitem
            
           
      WORKING:

                     SELECT DISTINCT a."System"
                     FROM "CommercialLight" a
                     WHERE a."System" LIKE System1
                      
      WORKING:

                          SELECT DISTINCT a."System"
                          FROM "CommercialLight" a
                          WHERE a."System" LIKE System2
                           
      WORKING:

                               SELECT DISTINCT a."System"
                               FROM "CommercialLight" a
                               WHERE a."System" LIKE System3
            
            
            
           Now, if I replace "popupmenuitem" with either System1, System2, or System3 it will work, but I have to do it manually.
           It would be nice to be able to change this argument, or parameter, or whatever it's called on the fly by a nice drop down menu so an end user can specify the type of executesql they want to run without knowing how to read and modify sql code..
            
           To do this, "popupmenuitem" has to be able to read whatever value they select from the pop up menu in browse mode, but I don't know how to code this, any ideas?

        • 1. Re: FileMaker 12 ExecuteSQL and valuelist popupmenu parameters?
          philmodjunk

               Is this the script step or the function with this name?

               What you post appears to be the function, but with important syntax details missing.

               If this is a calculation field, the following should work:

               ExecuteSQL ( "

               SELECT DISTINCT a.\"System\"
               FROM \"CommercialLight\" a
               WHERE a.\"System\" LIKE ?
                
               " ; Char ( 9 ) ; ¶ ; popupmenuitem
                
               )
                
               This assumes that popupmenuitem is the name of the field and that it is defined in the same table as the calculation field using this ExecuteSQL function call.
                
               This syntax should also work:

                    ExecuteSQL ( "

                    SELECT DISTINCT a.\"System\"
                    FROM \"CommercialLight\" a
                    WHERE a.\"System\" LIKE " & popupmenuitem
                     
                    ; Char ( 9 ) ; ¶ ;
                     
                    )

               Keep in mind that the first parameter in ExecuteSQL is a text calculation and thus care must be taken in how quotation marks are used.