CMIS Lookup (Lookup Specification)

From Grooper Wiki

This article is about the current version of Grooper.

Note that some content may still need to be updated.

2025 20232.90

CMIS Lookup is a Lookup Specification that performs a lookup against a settings_system_daydream CMIS Repository via a "CMISQL query" (a specialized query language based on SQL database queries).

Introduction

A CMIS Lookup in Grooper is a specialized Lookup type that enables you to retrieve or validate data from external content repositories using the CMIS (Content Management Interoperability Services) standard. CMIS Lookups execute CMISQL queries against a connected CMIS Repository (such as SharePoint, Alfresco, or FileNet) to search for documents or metadata, and can automatically populate or validate Grooper Data Fields based on repository content.

Unlike other Lookup types (such as Database Lookup, Lexicon Lookup, Web Service Lookup, or XML Lookup), CMIS Lookup is designed specifically for interacting with CMIS-compliant repositories and leverages CMISQL for querying document metadata and content.

When to Use

Use a CMIS Lookup when you need to:

  1. Validate that a Data Field value (such as a document number or ID) exists in a CMIS Repository.
  2. Auto-populate additional Data Fields with metadata from repository objects (e.g., vendor name, invoice date).
  3. Enforce referential integrity between Grooper data and external content systems.
  4. Use repository content as a source of valid values for Grooper fields.

Prerequisites:

  • A CMIS Repository, which is created as a child of a CMIS Connection, must be imported and configured in Grooper.
  • The repository must support CMISQL queries and have the necessary permissions for querying and retrieving content.
  • CMIS Lookups are added to container elements: Data Model, Data Section, or Data Table. They cannot be added directly to Data Fields or Data Columns.

How to Add and Configure and Test a CMIS Lookup

Configure a CMIS Lookup

Follow these steps to add and configure a CMIS Lookup from the Grooper Design page:

  1. Select the target container element (Data Model, Data Section, or Data Table) where you want to add the Lookup.
  2. Click the ellipsis button for the Lookup property.
  3. In the "Lookups" collection editor, click the "Add" button.
  4. Choose "CMIS Lookup" from the drop-down menu.
  5. Configure the following:
    • General
      • Repository: Select the CMIS Repository to be queried. Only imported repositories are available.
      • Query: Enter your CMISQL query to define the lookup operation.
    • Lookup Options
      • The properties of this section are not required to be configured, and the defaults may suffice. Your use case will determine how and why you would configure these properties.
    • Lookup Info
      • The properties of this section are auto-populated based on the query written.

Test a CMIS Lookup

  1. Select a container element with a configured CMIS Lookup.
  2. Click the Tester tab.
  3. Select a Batch Folder in the Batch Viewer.
  4. Click the "Test Extraction" button.
  5. Populate the Target Field with appropriate data and tab out of the field.
  6. Depending on your configuration, the fields may or may not automatically populate.

How to edit CMISQL queries

CMISQL is a query language used to search and retrieve data from CMIS repositories. In Grooper, you write CMISQL queries in the "Query" property of the CMIS Lookup.

  • A typical CMISQL query uses the format:
  SELECT Property1, Property2 AS 'FieldName' FROM ContentType WHERE PropertyX = @FieldName
  • Use property aliases in the SELECT clause to ensure result columns match Grooper field names (e.g., SELECT VendNo AS 'Vendor_ID').
  • Reference Grooper Data Fields or variables in the WHERE clause using @FieldName or @VariableName syntax.
  • The query editor in Grooper provides syntax highlighting and intellisense for content types, properties, and fields.

Query execution and validation:

  • When the lookup runs, Grooper replaces query parameters (e.g., @PO_Number) with current field values.
  • The query is executed against the selected repository, and results are returned for field population or validation.
  • Use the diagnostic output to verify results and troubleshoot errors.

Step by Step Query Edit

  1. Select a container element, such as a Data Model, Data Section, or Data table.
  2. Click the ellipsis button for the Lookups property.
  3. In the Lookups collection editor, with a CMIS Lookup in the collection, click the ellipsis button for the CMIS Query property.
  4. Start by writing the Select clause followed by a space and *.
  5. Press space and intellisense will provide the option for the From clause.
    • Here you select the content type. You can select a subsite from your CMIS source, and typically will select the document type.
  6. Delete the * after the Select clause, then backspace to the Select clause and press space.
  7. Intellisense will give you a list of fields from the CMIS source you can select. Use the As clause if the alias of the field in the CMIS source is different from the name of the field in Grooper. These fields selected here are the ones to be populated or validated based on data from the target field.
  8. If more than one field is used, type a comma, then a space and intellisense will come up again allowing you to continue adding fields to the query.
  9. Once done adding fields put your cursor after the content type of the From clause, then press space.
  10. Intellisense will pop up allowing you to select the Where clause.
  11. Select the field that will function as the target field to fire the lookup.
  12. Press space and intellisense will allow you to select the = sign.
  13. Press space after this and use the @FieldName syntax to define the target field.

How fields are populated

  • When a CMIS Lookup executes, the result set is analyzed:
 * Hit: Exactly one row is returned—fields are populated from the result set.
 * Miss: No rows are returned—behavior depends on the "Miss Disposition" property.
 * Conflict: Multiple rows are returned—behavior depends on the "Conflict Disposition" property.
  • Fields are populated by matching result column names to Grooper Data Field names. Use property aliases in your query to ensure correct mapping.
  • Data types are interpreted based on the Data Field configuration in Grooper.
  • If field population is disabled, only validation occurs (no fields are updated).
  • For collections (such as Data Table or multi-instance Data Section), multiple results can be added as new rows or instances.

Properties overview

Below is a summary of all CMIS Lookup properties, with definitions and use cases:

General

  • Repository
    • The CMIS Repository to be searched.
    • The selected repository determines the available content types, properties, and query capabilities for the lookup. Only repositories that have been imported and configured in Grooper can be selected here.
  • Query
    • The CMISQL query used for the lookup operation.
    • Enter a CMISQL query to define the lookup operation. The query determines which objects are retrieved from the CMIS Repository and how their properties are mapped to Grooper fields.
  • Description
    • A user-defined description of the lookup for documentation purposes.

Lookup Options

  • Trigger Mode
    • Determines when the lookup is executed (e.g., on field change, on demand, or always).
    • Determines the conditions under which a lookup operation is performed in Grooper. This setting allows you to control whether lookups are executed automatically, only under certain conditions, manually, or based on a custom expression. Choosing the appropriate trigger mode is essential for balancing automation, user control, and data integrity in your solution.
  • Miss Disposition
    • Specifies what happens when no results are returned.
    • Determines how Grooper responds when a lookup operation does not return any matching records from the external data source. This setting controls whether errors are flagged, target fields are cleared, or no action is taken.
  • Conflict Disposition
    • Specifies what happens when multiple results are returned.
    • Controls how Grooper handles situations where a lookup operation returns more than one matching record from the external data source. This setting determines whether errors are flagged, data is cleared, the first result is accepted, or no action is taken.
  • Field Population
    • Specifies how target fields are populated with the lookup results.
    • Controls whether and how the results of a lookup operation are written to the target fields in Grooper. This setting determines if lookup results will overwrite existing values, supplement only empty fields, or leave all fields unchanged.

Lookup Info

  • Lookup Fields
    • Lists the Data Fields referenced in the query (used as parameters).
  • Target Fields
    • Lists the Data Fields that will be populated from the query results.

For advanced configuration, refer to the Grooper documentation for CMIS Repository, CMIS Connection, Lookup Specification, and CMISQL Query.

See also:

Retrieved from "https://wiki.grooper.com/index.php?title=CMIS_Lookup_(Lookup_Specification)&oldid=31368"