Code examples for tokens

Tokens are placeholders. Optiva deploys tokens for use in queries.

Optiva evaluates the actual value of a placeholder according to the context of a query; then Optiva creates and submits the evaluated SQL query to the relational database.

[%1]

Start key. Use as a substitutable token for the WHERE clause in SQL.

...WHERE Item_Code like [%1]...

In this example, the token is replaced with the value input. For example, if the letter 'A' is input, the query returns all of the item codes that contain an 'A'.

...WHERE Item_Code = [%1]...

Oracle database only: If NL_SORT is not set to enable case-insensitivity, use the UPPER function to convert values in the Start key to uppercase.

...WHERE Item_Code like UPPER[%1]...

[%GROUP], [%USER]

Use these tokens to filter search results for a User and Group.

SELECT f.formula_code, f.version, f.description 
FROM fsformula f, [%%SEARCHTABLE] s 
WHERE f.formula_id = s.formula_id  
AND f.owner_code LIKE [%USER] 
AND group_code LIKE [%GROUP] 
ORDER BY f.formula_code, f.version

[%%PIVOT_CONTXT_CONTEXT-TYPE]

Use this token with these context attributes: SELLOC, MFGLOC, BRAND, PRODTYPE, ENDUSE, ENDUSER.

Here is an example of a formula search.

SELECT f.formula_code, f.version, f.description, fa.Attrib_Code, 
Fa.Attrib_Val 
FROM fsformula f, [%%SEARCHTABLE] s, FsFormulaAttrib fa 
WHERE f.formula_id = s.formula_id 
AND f.formula_id = fa.formula_id 
AND fa.Attrib_Val in ([%%PIVOT_CONTXT_SELLOC])  
AND Fa.Attrib_Code = "SELLOC" 
ORDER BY f.formula_code, f.version

Here is an example of an item search.

SELECT i.item_code, i.description, ia.Attrib_Code, ia.Attrib_Val 
FROM FsItem i, [%%SEARCHTABLE] s, FsItemAttrib ia 
WHERE i.item_code = s.item_code 
AND i.item_code = ia.item_code 
AND ia.Attrib_Val in ([%%PIVOT_CONTXT_SELLOC]) 
AND ia.Attrib_Code = "SELLOC" 
ORDER BY i.item_code

[%%PIVOT_INGR]

Use this token to display the ingredients that are specified in the Formula Search form.

SELECT f.formula_code, f.version, f.description, rm.item_code, 
rm.material_pct 
FROM fsformula f, [%%SEARCHTABLE] s, FsFormulaIngred rm 
WHERE F.FORMULA_ID = S.FORMULA_ID 
AND F.FORMULA_ID=RM.FORMULA_ID 
AND RM.ITEM_CODE IN ([%%PIVOT_INGR]) 
ORDER BY f.formula_code,f.version

[%%PIVOT_REF_REFERENCE-TYPE]

This token is used to designate the symbol name for a reference available on the References tab of a Search.

For example, Tests: [%%PIVOT_REF_TEST].

When using an object with a two-part key, such as Specifications, expand the identifier to include both the code and version: [%%PIVOT_REF_SPECIFICATION_ID]

It is not necessary to show this data in pivoted form.

[%%SEARCHTABLE]

The token that can be substituted for the temporary table. The temporary table is used to store the returned records. For example: ...FROM [%%SEARCHTABLE]...

[%%OBJECTSYMBOL]

This token specifies the symbol of the current lookup.

[%%REFOBJECTSYMBOL], [%%REFOBJECTKEY]

These tokens are only used in Create Rules that are creating something from a reference.

[%USER], [%GROUP]

Use these tokens to filter results for a user or user group.

...AND F.OWNER_CODE LIKE [%USER] ...

...AND GROUP_CODE LIKE [%GROUP] ...

In this example, the results are displayed only if the user owns the formula.

SELECT description, formula_code, version, class, d.formula_id 
FROM FsFormula d  FsFormulaSets S 
WHERE logical_delete = 0 
AND type_ind = 0 AND description >= [%1]
AND OWNER_CODE=[%USER]D.Formula_id = S.Formula_id and S.Set_code = [%LAB]
ORDER BY description