Sample queries

Prompt with employee field

The first example shows a prompt where the user must enter an employee ID. The query then shows the inspections that are assigned to the selected inspector.

To define the query, first add a new Query node under the Building category (under CDR > Permitting under the Queries node). Specify these attributes for the new query:

• Id: DepartmentBuildingInspectionsByEmployee
• Type: SQL
• Model: CDR_Building_Inspection
• Description: Nearby Departments Building Inspections
• SQL: Enter this SQL statement:

SELECT BLDGINSP.*, (NVL(CONTACT.NAMEFIRST,'') || ' ' || 
NVL(CONTACT.NAMELAST,'')) as ContactName
FROM CDR_BUILDING.BLDGINSP BLDGINSP
INNER JOIN CDR_BUILDING.BLDGAPPL BLDGAPPL
ON BLDGINSP.APBLDGKEY = BLDGAPPL.APBLDGKEY
INNER JOIN PROPERTY.ADDRESS ADDRESS
ON ADDRESS.ADDRKEY = BLDGAPPL.ADDRKEY
INNER JOIN CDR_BUILDING.BLDGWORKTYPE WORKTYPE
ON UPPER(BLDGAPPL.WORKTYPE) = UPPER(WORKTYPE.CODE)
INNER JOIN CDR_BUILDING.BLDGAPPLTYPE APTYPE
ON APTYPE.APBLDGDEFNKEY = BLDGAPPL.APBLDGDEFNKEY
INNER JOIN CDR_BUILDING.BLDGINSPTYPE INSPTYPE
ON BLDGINSP.APBLDGINSPTYPEKEY = INSPTYPE.APBLDGINSPTYPEKEY
INNER JOIN RESOURCES.EMPLOYEE EMPLOYEE
ON UPPER(BLDGINSP.ASSIGNTO)  = UPPER(EMPLOYEE.EMPID)
LEFT JOIN
  (SELECT bldgapl.apbldgkey,
    bldgapl.cntctkey,
    cntctid.namelast,
    cntctid.namefirst
  FROM cdr_building.bldgapl
  INNER JOIN resources.contact
  ON contact.cntctkey = bldgapl.cntctkey
  INNER JOIN resources.cntctid
  ON contact.idkey               = cntctid.idkey
  WHERE bldgapl.cntctkey         > 1
  AND Upper(bldgapl.prim)        = 'Y'
  ) CONTACT ON CONTACT.apbldgkey = bldgappl.apbldgkey
WHERE UPPER(BLDGINSP.RESULT) = ' '
AND UPPER(BLDGINSP.ASSIGNTO)  like UPPER(@EmployeeId)"

To define the prompt for this query, add a child Parameters node to the Query node, then add a child Parameter node. Specify these attributes for the Parameter node:

• Id: EmployeeId
• SearchFormat: Contains
• Prompt: Select Employee
• PromptFormat: Exact
• DataType: System.String
• DefaultValue: %

<QuickMaps>
  <QuickMap ModelId="Application.PrimaryContactName" QueryId="ContactName" />
</QuickMaps>

Prompt with employee list

The second example shows a list of available employees. Rather than entering an employee ID, the user can select an employee from the list to view that inspector's inspections.

Except for the SQL statement, the attributes for the Query node are the same as in the first example. The SQL for this query is shown here:

SELECT BLDGINSP.*, (NVL(CONTACT.NAMEFIRST,'') || ' ' || 
NVL(CONTACT.NAMELAST,'')) as ContactName
FROM CDR_BUILDING.BLDGINSP BLDGINSP
INNER JOIN CDR_BUILDING.BLDGAPPL BLDGAPPL
ON BLDGINSP.APBLDGKEY = BLDGAPPL.APBLDGKEY
INNER JOIN PROPERTY.ADDRESS ADDRESS
ON ADDRESS.ADDRKEY = BLDGAPPL.ADDRKEY
INNER JOIN CDR_BUILDING.BLDGWORKTYPE WORKTYPE
ON UPPER(BLDGAPPL.WORKTYPE) = UPPER(WORKTYPE.CODE)
INNER JOIN CDR_BUILDING.BLDGAPPLTYPE APTYPE
ON APTYPE.APBLDGDEFNKEY = BLDGAPPL.APBLDGDEFNKEY
INNER JOIN CDR_BUILDING.BLDGINSPTYPE INSPTYPE
ON BLDGINSP.APBLDGINSPTYPEKEY = INSPTYPE.APBLDGINSPTYPEKEY
INNER JOIN RESOURCES.EMPLOYEE EMPLOYEE
ON UPPER(BLDGINSP.ASSIGNTO)  = UPPER(EMPLOYEE.EMPID)
LEFT JOIN
  (SELECT bldgapl.apbldgkey,
    bldgapl.cntctkey,
    cntctid.namelast,
    cntctid.namefirst
  FROM cdr_building.bldgapl
  INNER JOIN resources.contact
  ON contact.cntctkey = bldgapl.cntctkey
  INNER JOIN resources.cntctid
  ON contact.idkey               = cntctid.idkey
  WHERE bldgapl.cntctkey         > 1
  AND Upper(bldgapl.prim)        = 'Y'
  ) CONTACT ON CONTACT.apbldgkey = bldgappl.apbldgkey
WHERE UPPER(BLDGINSP.RESULT) = ' '
AND EMPLOYEE.DEPT            =
  ( 
  SELECT EMPLOYEE.DEPT FROM RESOURCES.EMPLOYEE WHERE EMPID = @EMPID
  ) AND UPPER(BLDGINSP.ASSIGNTO)   = UPPER(@EmployeeId)

To define the prompt for this query, add a Parameter node with these attributes:

• Id: EmployeeId
• SearchFormat: Contains
• Prompt: Select Employee
• Moniker: Hansen.Resources.Employee
• PromptFormat: Exact
• DataType: System.String

This query also uses the same QuickMaps node that was shown in the previous example.

Menu configuration

After you've defined your query you must add it to the menu. This menu item will work for either example, because they both have the same ID:

<Item QueryId="DepartmentBuildingInspectionsByEmployee" 
DisplayAction="QueryGridMapViewController/ShowGridView?
gridViewId=CDR_Building_Inspection_GridView" 
SelectAction="BuildingInspectionViewController/ShowRecordViewer/{0}" 
Id="DepartmentBuildingInspectionsByEmployee" ResultCode="IsResulted" 
DisplayName="Department By Employee" Description="Search department 
inspections by Employee" />

To add the query to the menu, add this item to the BuildingInspections grouping under the Menus node in the Mobile configuration. This grouping can be found in the Building category, under CDR > Permitting.