ODBC data sources normally use a hard-coded query to retrieve data. However, EasyCatalog allows elements of the query to be user definable or ‘parameter’ based. These parameters are shown on the ‘info’ dialog box, accessible from the binocular icon on the data panel. Any changes made are applied the next time the data is synchronized.
Parameters are defined using double curly braces. Executing the query then prompts for the parameters by name. A maximum of 4 can be defined. Optional default values can be configured using the colon separator. For example:
1 | SELECT * from PRODUCTS where ID = {{Catalog ID:99}} |
This defines a parameter called “Catalog ID” with a default value of “99”
Retrieving parameter values via SQL
Rather than hardcoding parameter values, an SQL query can be used to present the user with a pop-up menu of choices. For example:
1 | SELECT * from PRODUCTS where country_code = {{Country:[[select * from countries]]}} |
Here, the user will be presented with a parameter called ‘Country’, with a pop-up containing the values from the ‘countries’ table. Values to appear in the pop-up can also be hardcoded in the query:
1 | SELECT * from PRODUCTS where country_code = {{Country:[[SELECT 'US' AS country UNION ALL SELECT 'UK' UNION ALL SELECT 'DE']]}} |