CMIS Lookup (Lookup Specification)

⚠	This article was migrated from an older version and has not been updated for the current version of Grooper. This tag will be removed upon article review and update.

This article is about the current version of Grooper.

Note that some content may still need to be updated.

2025

2023

2.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).

You may download and import the file below into your own Grooper environment (version 2023). There is a Project configured according to its instructions.

Media:2023_Wiki_CMIS-Lookup_Project.zip

About

Grooper uses the CMIS protocol to connect to a variety of Content Management Systems. This connection can be used to integrate data in powerful ways by allowing the collection of one (or sometimes many) fields of information within a model in Grooper (let's call it Field A), then leveraging that extracted data point against the column of like information in the Content Management system (called Column A) to then pass back other desired fields from other columns in the same row of information.

How To

The most basic thing to understand about performing a lookup in Grooper is what the "lookup field" is and what the "target field(s)" is(are). The lookup field is the trigger, and the target fields are the result.
Put another way, the lookup field is what will be populated with information from an Activity like Extract (a user could also manually enter data into the field). This field will then be "bounced" off of the source of data to supply the remaining fields, or the target fields, with information

Understanding the Source

Perhaps the most challenging aspect of explaining how to leverage this functionality is the fact that it requires access to a CMIS repository (in this case a SharePoint document library). One was created for the sake of this article, but unfortunately cannot be shared externally. The scope of this article also does not allow explaining setting up a SharePoint document library, as that's an exhaustive topic on its own. Note that CMIS Lookups are not limited to SharePoint, that just happened to be the content system chosen for this article.
Given that, it will be assumed you have access to a source that Grooper can connect to via a CMIS Connection.

The below image is showing a SharePoint document library with a single document in it and several columns added and populated with information.

Understanding the Content Model

The Content Model used here is very simple. It has no Content Types or any extraction setup. None of that is necessary to setup and demonstrate the lookup. It simply has a Data Model with several fields.

Configuring a CMIS Connection

This configuration is specific to this article. While aspects of it can apply to any configuration, it's worth noting that this is not THE way to configure a CMIS Connection. This will be configured for a SharePoint environment. Note that the URLs in the image cannot be connected to externally, so attempting to copy this configuration verbatim will give you errors. This is meant as a guide, and will require an actual SharePoint environment on your end to be established, and real URLs from it supplied.

Expand the node tree to the Infrastructure folder and in the CMIS Connections folder create a CMIS Connection object.
Set the Connection Type property to SharePoint.
Fill in the Base URL property with the URL for the SharePoint site you're wanting to connect to.
- You can be as granular with this as you want. What this means is you can set the URL to a specific site, and have the CMIS Connection object pertain to that specific site of the SharePoint environment (https://hydra.sharepoint.com/sites/ChiliConToso), or, you could set the URL to the base SharePoint URL and manage all sites from one CMIS Connection object (https://hydra.sharepoint.com).

The Authentication Method property can only be set to SharePoint OAuth when configuring a SharePoint connection.
When you click the Authentication property the first time you're configuring a CMIS Connection object with the Connection Type property set to SharePoint a window will pop up to allow the entering of credentials for your Office 365 account.
- To log out, click the ellipsis button on the property.
Set the Enable Subsites, Enable Library Types, Enable Caching properties to True.
- Enable Subsites lets you navigate the hierarchical folder structure of the base site. If disabled, you can only view the immediate contents of the base site.
- Enable Library Types is used if custom Document Library fields are to be used in CMISQL Query operations or mapped to Grooper fields. When enabled, a CMIS content type will be exposed for each custom type defined in a Document Library.
- Enable Caching if enabled, the hierarchy of Sites and Libraries in the SharePoint repository will be cached on first use. Subsequent connections will load this information from cache, speeding up the initialization process.
With the previous settings saved, you can now click the List Repositories button.
Having listed the repositories, you can now select one and import it.

This will create a CMIS Repository object.

You can use the "Browse" and "Search" tabs to navigate the folder structure of the site and write CMISQL Queries respective, and on the "Types" tab...
...you can select a desired Library Type...
...and view its properties. In this case, we can see the columns used in this Document Library for embedding index data on a file.

Configuring the CMIS Lookup

With an established connection to a CMIS repository, the CMIS Lookup can now be configured. This is done on a Data Model.

Select the Data Model.
Select the Lookups property then click the ellipsis button.

This will bring up the Lookup Specification Collection Editor window. In this window click the Add drop-down menu and select CMIS Lookup.

Select the CMIS Lookup ojbect in the list.
Select the Repository property then click the drop-down arrow to get a mini-node tree view.
Select the CMIS Repository object you wish to perform a lookup against.

Select the CMIS Query property then click the ellipsis button.

This will bring up the CMIS Query window.
Start by beginning to type...
SELECT

...in the editor. The intellisense will pop up a menu that will let you complete the command by pressing tab (or enter, or double clicking the value). You can also just continue writing it out.
- A CMIS Query has syntax almost just like a SQL Query, so if you're familiar with that this part will be quite simple. Grooper's intellisense for these queries is quite good, however, so even someone with little-to-no experience with SQL Queries will be fine.
- This is also as good a time as any to remind you to use the Grooper Help: that easily glossed over area with the medium gray background underneath nearly everything you select. CMIS Query is a great example of the Grooper Help as it goes a long way to help you get a result, including telling you what to type, and examples to try.

Having completed the...
SELECT

...command, press the space bar to move the cursor forward one space. In doing so the Grooper's intellisense will kick in again and push you in the direction of the...

FROM

...statement. Not only that, but having pointed to the CMIS Repository object, the intellisense has an awareness of Content Types within the repository. Select the site that contains the writable properties you're looking to leverage for the lookup.

All you SQL sleuths out there might argue, "HEY! You haven't told it WHAT to select, so you shouldn't write the FROM yet!". Estute observation, however, Grooper's smarter than you think. In writing the...

FROM

...statement first you've given Grooper awareness of what CAN be selected. This is made clear in the next step.

With the "From" statement completed and pointed at an appropriate Library Type you can expand the sub-properties of the "Select" property...
...and check the properties you want to select from. You can also simply type the desired select points, but this is done for you if you use the property grid. These are the target fields that will be populated with index data when the lookup field fires.

You will want to be careful and pay attention to the names of desired select targets. If the Grooper Data Elements have different names than the target sources, you will need to use an "AS" statement to match them up.
For example EMail_Address AS 'EMail'

We can now set the desired Lookup Field by leveraging the "Where" statment. Use the drop-down for the Where property (again, you can also just type this out in the query editor)...
...and choose the Comparison Predicate option.

Click the drop-down for the Property Name property and choose the index field you want to use to fire the lookup.
In this case the "Employee_ID" index field will be used to fire the lookup.

With the Where statement completed we should have a complete query that will look like...
SELECT First_Name, Last_Name, Phone_Number, EMail_Address AS 'EMail', Gender, SSN, IP_Address, Street_Number, Street_Name, City, State, Postal_Code AS 'ZIP' FROM sites/Chili con Toso/Shared_Documents/Document WHERE Employee_ID=@Employee_ID

Closing the query editor you should now see the Lookup Fields and Target Fields are automatically populated based on the settings of the query.
Satisfied with your settings, click the "OK" button to close the window, then save your changes.

Select the Data Model...
...then put a value into the "Lookup Field" (pictured in this case as the "Employee_ID") you know to be a valid entry from the CMIS source.
When you tab out of the "Lookup Field" the "Target Fields" will all be populated with data as a result of the lookup firing correctly.