how to link value lists with executesql parameters?

[lw1990]

I'm using sqlexplorer add in to write a sql function using the "LIKE" operators, as shown below.

I'm trying to figure out a way to dynamically change the executeSQL parameters by using value list pop up menus the end user can select.

So for example having 3 values on a pop up menu which are linked to COMBO1

whatever they select becomes COMBO1

and then when they hit 'run'. COMBO1=theirvalue in the ExecuteSQL command

In microsoft access, to do this all you had to do was put COMBO1 in braces like this (COMBO1)

how do we link value lists in FM 12 to executeSQL parameters?

// ------------  BEGIN EXECUTESQL BLOCK ------------  



Let ( [ 



ReturnSub = "\n" ; // We need to swap out carriage returns in your results with a different character, so specify the character here.  \n is the default.

SQLResult = ExecuteSQL ( 



// ------------  BEGIN QUERY ------------



"SELECT DISTINCT a.\"RequiredDensity\", a.\"RequiredArea\", a.\"AdjustmentArea\", a.\"MinimumArea\", a.\"Orientation\", a.\"MinInsideHose\", a.\"MaxInsideHose\", a.\"TotalHoseStreamDemand\", a.\"WaterSupplyDuration\", a.\"AreaLimit\"

FROM \"CommercialLight\" a

WHERE a.\"System\" LIKE ? AND a.\"HoseStations\" LIKE ? AND a.\"Hc_min\" LIKE ? AND a.\"Hc_max\" LIKE ? AND a.\"CeilingPitch\" LIKE ? AND a.\"CombusSpaces\" LIKE ? AND a.\"CeilingPockets\" LIKE ?" ; 



// ------------  END QUERY ------ ------ 



// ------------  BEGIN FIELD AND ROW SEPARATORS ------------ 



"    " ; "|*|" ; 



// ------------  END FIELD AND ROW SEPARATORS ------------ 



// ------------  BEGIN ARGUMENTS ------------  

// ------------  These arguments are pulled from the values you entered when running your query.  You can substitute your own values, fields or variables here. One argument per ? in your query, in query order, separated with semicolons. ------------ 



"(COMBO1)" ; "(COMBO2)" ; "(EDIT1)" ; "(EDIT2)" ; "(CHECK1)" ; "(CHECK2)" ; "(CHECK3)"



// ------------  END ARGUMENTS ------------  



 ) ] ; 



// ------------  BEGIN CARIAGE RETURN SUBSTITUTIONS ------------  



 Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  )



// ------------  END CARIAGE RETURN SUBSTITUTIONS ------------  



)



// Compliments of SeedCode… Cheers!



// ------------  END EXECUTESQL BLOCK ------------  

[adellos]

You figure this out yet? I'm not sure what you're asking for but I think it's something along the lines of having a query:

SELECT id, name, address_one, city, state FROM customers WHERE id = 12

Where the value 12 would correspond to a customer picked from a pop-up menu by a user? If that's the case, you might benefit from using a global field. Somewhat counter-intuitively (at least to me), global fields work on a session level, meaning their local to an instance of FileMaker.

So say you have a two-field value list where the first (hidden) value is the id from the 'customers' and the second(displayed) value is 'name' from the same table. You could store the selected id in a global field named something like:

global_customer_id

You would then refer to that global field when you built your executeSQL function, so something like this:

ExecuteSQL("SELECT id, name, address_one, city, state FROM customers WHERE id = " & global_customer_id)

hth!

anthony

(Notice that I didn't put single-quotes around the id value because in my imagined scenario, the id is a number field and I believe that even in v12, FileMaker is picky about quotes around numeric values in WHERE conditions)