Query examples for lookups

You can create queries for lookups.

Lookup queries for formulas

Add a lookup that is based on a formula code. 

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

The formula_code >= [%1] is required for the Go To key to function correctly.

To retrieve logically deleted items in a lookup, set the logical_delete value to 1. 

WHERE logical_delete = 1

Queries for Lookup Searches

The search is run when the code is selected and the objects in the lookup are the results of the search.

  1. Create a query for lookup searches.
  2. Add the query to the Search Query Code column in the Lookup Set form.

Queries for Lookups only

Include an identifier in the code such as Search and Lookup. Here is an example of a query for a Lookup Search, (FormulaSearchLookup1). 

SELECT Object_Code, Description
FROM FsObjectData
WHERE Symbol = 'FORMULASEARCH' AND Object_Code >= [%1]
ORDER BY Object_code

Queries for Lookup Sets

You can include sets in lookup queries.

  1. Create a query for lookup sets.
  2. Add the query in the Lookup Set form.

For lookups only, include an identifier in the code such as Set and Lookup. Here is an example of a query for Lookup Sets, (FormulaSetLookup1). 

SELECT FI.FORMULA_CODE, FI.VERSION, FI.DESCRIPTION, FI.CLASS, FI.FORMULA_ID
FROM FsFORMULA FI,FSFORMULASets FS
WHERE FI.FORMULA_ID = FS.FORMULA_ID 
AND FI.LOGICAL_DELETE = 0 AND SET_CODE = [%1] 
AND FI.FORMULA_CODE >= [%2]AND type_ind in (0,2)
ORDER BY FI.FORMULA_CODE

Queries for Project Lookups

Project lookups have many attributes including, code, description, security etc.

For Project - P.TYPE_IND = 0 

SELECT DISTINCT P.PROJECT_CODE, P.DESCRIPTION
FROM FSPROJECT P
LEFT OUTER JOIN FSPROJECTACL A ON (A.PROJECT_CODE = P.PROJECT_CODE)
LEFT OUTER JOIN FSOBJECTROLESECURITY RS ON (RS.SET_TYPE = 'PROJECT')
WHERE P.PROJECT_CODE >= [%1] AND P.PROJECT_CODE NOT LIKE '@DFLT%' 

AND P.PROJECT_CODE NOT LIKE '@T%' AND P.TYPE_IND = 0 AND 
P.LOGICAL_DELETE = 0
AND
(
((P.OWNER_CODE = [%USER] AND P.OWNER_SECURITY >= 3)
OR (P.GROUP_CODE IN ([%%GROUPS]) AND P.GROUP_SECURITY >= 3)
OR (P.ROLE_SECURITY >= 3 AND RS.ROLE_CODE IN ([%%ROLES]) AND 
RS.SECURITY_IND >= 3))
OR
((A.KEY_CODE = [%USER] AND A.TYPE_IND = 0 AND A.ACL_SECURITY >= 3)
OR (A.KEY_CODE IN ([%%ROLES]) AND A.TYPE_IND = 1 AND A.ACL_SECURITY >= 
3)
OR (A.KEY_CODE IN ([%%GROUPS]) AND A.TYPE_IND = 2 AND A.ACL_SECURITY >= 
3)
)
)
ORDER BY P.PROJECT_CODE