Adding Custom Query Parameters

In addition to the query parameters provided by Infor CRM, you can also add custom query parameters to the CUSTOMQUERYPARAMETERS table in the database.

To add a custom parameter:

  1. Open your Microsoft SQL Server or Oracle database management tool.
  2. Open the CUSTOMQUERYPARAMETERS table.
  3. Add a row to the table.
  4. In the CUSTOMQUERYPARAMETERID column, type a unique ID value.
  5. In the NAME column type a descriptive, but short name. This is the name you will see in the Value Is drop-down list when adding or editing a conditional statement in Query Builder.
    Note:  The name may contain letters and number, but may not contain spaces or symbols. Do not include a leading colon:
  6. In the PARAMETERVALUE field type the SQL query statement.
  7. In the SECCODEID column, specify the SeccodeID SYST00000001 to make the query parameter available to everyone.
  8. In the DATATYPE, type the field type this parameter applies to. The supported data types are: DateTime, String, and Integer.

    The query parameter will display in the Value is drop-down list for the specified data type and supported operators.

  9. In the ISSCALAR column, type T for True or F for False.
    • T: The query parameter is available for the operators
      • = equal to
      • <> not equal to
      • > greater than
      • < less than
      • <= less than or equal to
      • >= greater than or equal to
    • F: The query parameter is only available for the operator In.
  10. In the DATABASETYPE column, type one of the following:
    • MSSQL: Type MSSQL if the Parameter Value is specific to MSSQL.
    • Oracle: Type Oracle if the Parameter Value is specific to Oracle.
    • Both: Type Both if the Parameter Value is valid for MSSQL and Oracle.
  11. Save your changes.

    Once saved, the custom parameter will be available in the Query Builder, Conditions, Value is drop-down list for the data type specified in steps 8 and 9.