2.90:CMIS Lookup (Lookup Specification)

Performing data lookups on CMIS sources like SharePoint can be a powerful data integration tool.

A base Content Model for use with this article can be found here. It is not required to download to understand this article, but can be helpful because it can be used to follow along with the content of this article. This file was exported from and meant for use in Grooper 2.9

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 fileds, or the target fields, with information

Understanding the SourceUnderstanding the Content ModelConfiguring a CMIS Connection

Understanding the Source

Perhaps the most challenging aspect of explaining how to leverage this functionality is the fact that it requires access to 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. It simply has a Data Model with several fields. None of that is necessary to setup and demonstrate the lookup.

Configuring a CMIS Connection

Click the Create Table button. Click the Content Type property and click the drop-down arrow to get a mini node tree view. Select the first Content Type. In the case of this exmaple, it will be the Document Type named Database Export 01.
Becaues the Database Export 01 Content Type was selected, the following Data Element Scope property will have a drop down of which will contain Data Elements relative to this particular Content Type. Selecting the Earnings Data Table will set the baseline structure of the table to be created, and it will inherit upward. Think back to the notion of the one-to-many relationship. This table can have a dynamic number of rows and the Data Columns are capable of capturing and reporting back unlimited instances of data, hence multiple rows. The Data Fields within this scope, however, are only capable of capturing and returning a single piece of data. But, given the nature of inheretence, the table that will be created will make columns not just for the Data Columns of the Data Table, but for each of the Data Fields contained within this scope. Were there Data Fields further up the inheretence tree, say at the base Data Model of the main Content Model the table would also attempt to inheret those as well.
The Table Name property is arbitrary, but by default is a concatenation of the strings passed by the Content Type and Data Element Scope properties.M Click the Execute button to close this window and continue the process forward...
Here's where Grooper is doing the hard work for us. Notice in the Review/Edit SQL Statement window the SQL Statement required to create our table is already written for us. Click the Run SQL Statement button to create the table.