│ Grooper Help - Version 25.0
25.0.0017 2,127
  • Overview
  • Help Status

CMIS Lookup

Lookup Specification Grooper.CMIS

Performs a lookup against a CMIS repository.

Remarks

Executes a CMISQL Query against a CMIS Repository, and uses the result set to validate and/or populate fields.

Overview

The CMIS Lookup object enables Grooper to retrieve and validate data from external content repositories that support the CMIS standard (such as SharePoint, Alfresco, FileNet, and others). It leverages CMISQL queries to search for documents or metadata, and can be used to:

  • Validate field values against repository content.
  • Auto-populate fields with metadata from matching repository objects.
  • Enforce referential integrity between Grooper data and external content systems.

Supported Scenarios

  • Field Validation: Ensure that a field value (such as a document number or ID) exists in the repository.
  • Auto-Population: Retrieve and fill in additional metadata fields (such as vendor name, invoice date, etc.) from repository objects.
  • Reference Data: Use repository content as a source of valid values for Grooper fields.

Configuration

  1. Repository
    Select the CMIS Repository to be queried. This determines the available content types and properties.

  2. Query
    Enter a CMISQL Query to define the lookup operation. Use @FieldName or @VariableName syntax in the WHERE clause to reference Grooper fields or variables.

    • The SELECT clause should include all properties to be used for field population, using aliases as needed to match Grooper field names.
    • The WHERE clause should filter results based on field or variable values.

  3. Field Mapping
    Ensure that the column names in the result set match the names of the Grooper fields to be populated.

  4. Field Population and Error Handling
    Configure the 'Field Population', 'Miss Disposition', and 'Conflict Disposition' properties to control how results are applied and how errors are handled.

Query Editing

The query editor provides advanced features such as syntax highlighting and intellisense, making query creation fast and intuitive.

  • Syntax highlighting improves readability by coloring query components.
  • Intellisense provides context-sensitive pick lists for content types, properties, keywords, and fields as you type.
  • See the 'Query' property for query examples and details.

Query Execution

When a query executes, the result set will fall into one of the following categories:

  • Hit: The result set contains exactly 1 row. This is considered a successful lookup.
  • Miss: The result set contains 0 rows. The action taken depends on the Miss Disposition property.
  • Conflict: The result set contains multiple rows. The action taken depends on the Conflict Disposition property.

Field Population

When a successful lookup occurs and Field Population is enabled, fields are populated from the result set based on name.

  • The SELECT clause must include each property to be used for field population, specifying property aliases where necessary to ensure that the property names in the result set match the names of the fields they are intended to populate (e.g., SELECT propname AS 'Alias').
  • When Field Population is disabled, the result set should contain minimal columns for efficiency (e.g., select only a lookup column instead of "*").

Execution Flow

  1. The lookup is triggered based on the configured trigger mode.
  2. Grooper collects field and variable values referenced in the query.
  3. The query is executed against the selected CMIS repository.
  4. The result set is analyzed and applied to target fields according to the population and error handling settings.

Error Handling

  • If no results are returned, or if multiple results are found, behavior is controlled by the 'Miss Disposition' and 'Conflict Disposition' properties.
  • Errors during query execution (such as syntax errors or connection issues) are logged in the diagnostic output and can set error status on affected fields.

Best Practices

  • Use variables in the WHERE clause to dynamically filter results based on field values.
  • Use property aliases in the SELECT clause to ensure result columns match Grooper field names.
  • Test queries using the diagnostic output to verify correct results and troubleshoot issues.
  • Minimize the number of columns returned for validation-only lookups to improve performance.
  • Review repository schema and permissions to ensure queries return the expected results.

Examples

Validation Only:

SELECT * FROM PurchaseOrders WHERE PoNo = @PO_Number

Validates that the value of the 'PO Number' field is present in the repository.

Field Population:

SELECT VendNo AS 'Vendor_ID' FROM PurchaseOrders WHERE PoNo = @PO_Number

Populates the 'Vendor ID' field with the value of the 'VendNo' property.

Multiple Field Population:

SELECT Last AS 'Last_Name', First AS 'First_Name', DOB FROM Students WHERE StudentID = @Student_Number

Populates the 'Last Name', 'First Name', and 'DOB' fields from the corresponding properties.

Properties

NameTypeDescription
General
Lookup Options
Lookup Info

See Also

CMIS Repository

Used By

Data Lookup

Notification