Parameterized Queries

Parameterized queries require input at runtime.

Parameterized queries are queries that require user input. Use the placeholder $parValue for fields the user has to enter when executing the query. The screenshot below shows a new query “MyQuery” in the CRM.Designer.

In Aurea CRM.Web these fields are displayed as input fields in the respective query form Query Designer, QueryRun and QueryReport actions. See the screenshot below:

Using One Value in Multiple Conditions

You can define parameterized queries where the same value is used by multiple conditions. The user only has to input the value once. Use placeholders e.g. called $parValue<number> for fields the user has to enter when executing the query. The value specified by the user is applied to all occurrences of $parValue<number>.

Example

select (Company, Country, RepID, Rep2ID, Upd, FIKP.Sex, FIKP.FirstName, FIKP.LastName, FIKP.RepID, FIKP.Country, FIMA.Contact, FIMA.Date, FIMA.Time, FIMA.Upd) from (FI)
where ((Upd>='$parValue2' OR RepID='$parValue1' OR Rep2ID='$parValue1') AND Country='$parValue3')
plus (KP as FIKP)
where (RepID='$parValue1' OR Country='$parValue3')
plus (MA as FIMA)
where (Upd>='$parValue2')
orderby (Company, FIKP.LastName, FIMA.Date, FIMA.Time)

Users only see one field per placeholder:

The provided criteria are applied to all occurrences of the placeholder. Depending on the query definition (AND/OR, relation between info areas) the condition must be fulfilled by all fields using the same placeholder or not.

Make sure you do not apply a $parValue<number> placeholder to fields of different types. No validation takes place when saving the query.

Specifying $parValue<number> for a hierarchical catalog automatically adds an input field for the parent catalog. However, using the same $parValue<number> for parent and child catalog results in an error.