Import Query Results (Import Provider)

About

Import Query Results is an Import Provider used to search a repository for documents you want to import, given whatever set of criteria you define.  This is done by performing a CMISQL query into a batch and importing the results of that query. This is similar to the pre-2.72 CMIS Import provider. 

!	The NTFS, FTP, SFTP, and OneDrive bindings are not currently queryable.  Therefore, these bindings are not suitable for the "Import Query Results" provider.

Version Differences

2.72 has expanded query capabilities:

• All bindings except NTFS, FTP, SFTP and OneDrive support full text search via the CONTAINS predicate. The OneDrive binding will support full-text search in the next release.
• Expanded CMISQL Syntax:  This adds support for CMISQL to provide several new features.
  • CONTAINS predicate - Allows full text search
  • IN predicate - Specifies a list of allowed values for a property, such as defining a file extension (i.e: FileExtension IN ('.pdf', '.docx', '.xlsx'). 
  • IS predicate - Either IS NULL or IS NOT NULL
  • NOT operator - Inverts the logic of individual predicates or a group enclosed by parentheses.  Note some CMIS Bindings have limited support for the NOT operator.
  • Unlimited nesting,

How To: Import Items Via Import Query Results

Step 1Step 2Step 3Step 4

Create a Batch

To use Import Query Results you must first create a batch.

• In Grooper Design Studio or Grooper Dashboard, create a new batch by pressing "Batch > CMIS Import > Import Query Results...".

File:001.PNG

Choose the Repository

If you have multiple CMIS repositories available, you'll need to choose the one from which you wish to import.

• Select the appropriate repository from the "Repository" property.

Find the "Repository" property	Select your Repository
File:ImportQueryResults002.PNG	File:ImportQueryResults003.PNG

Configure Query

Now we need to write a CMIS Query

• Open the "CMIS Query" dialog by clicking the dotted box at the right side of the property
• Configure your query by either:
  • Using the "Basic Search" tab, or
  • Using the "CMSQL" tab

The settings for the "Basic Search" tab will alter the query in the "CMSQL" tab and vice versa. You can use these to get a feel for what's going on.

Find the **CMIS Query** property
File:ImportQueryResults004.PNG
The "Basic Search" tab	The "CMISQL" tab
[[image:importQueryResults005.PNG	File:ImportQueryResults006.PNG

Analyze and Import

Now we can test it out.

• Pressing "Analyze" will run a search against your repository with your configured settings.
• Pressing "Import" will create a batch of documents that were imported using your configured settings.

Using the "Starting Step" property, you can choose from your published Batch Processes the step where your batch should be created. For example, in these screenshots, the batch will be created at the "Image Review" step of a Batch Process. )))

Example Queries

These are samples of a query string. They take the following general form.

SELECT * FROM <ContentType> WHERE <Criteria> ORDER BY <Sort>

Let's break each component, or "clause", to get a better idea of how this works

SELECT

This specifies which properties are to be returned with query results.

If you are querying all properties the asterisk or * will indicate all properties should be returned.

For example: SELECT *

Otherwise, you will list them out separated by commas.

For example: Let's say you are querying an Exchange repository and you only want to search the sender and recipients properties. You'd type SELECT Sender, ToRecipients, CcRecipients, BccRecipients to limit your query to only those four properties.

FROM

This clause indicates the type of object to search for. This will be a content type defined in the CMIS Repository. If the content type is document based, the query result will be a CMIS Document. If it is folder based, it will be a CMIS Folder.

The content type specified in the FROM clause has two jobs. One, it defines what properties are available to the other clauses. Two, it limits the scope of the search to only objects of the type specified in the clause.

For example: Let's say you are querying an Exchange repository and want to search email messages and not contacts or tasks or appointments. You'd type FROM Message to limit your query to just the Message content type.

WHERE

This is how you define what search conditions must be met to be included in your set of returns. Multiple conditions can be joined with the AND or OR or NOT operators. You can change the order of operations by using nested parenthesis. Each condition is followed by a predicate. The following is a list of predicates. Note not every property type may be able to utilize every predicate.  For example, the Subject property on the Exchange binding cannot use the "=" operator.

Predicate	Description	Example
Comparison Predicate	Specifies a condition for an individual property using comparisons, such as "equals to" or "less than".  The LIKE and IS operators are also a comparison predicates.	invoice_date<'12/31/2007'
IN Predicate	Specifies a list of allowed values for a property. This list is separated by commas.	FileExtension IN ('.pdf', '.docx', '.xlsx')
CONTAINS Predicate	Specifies a full-text query. You can use AND, OR and NOT operators.	CONTAINS('mortgage AND payment AND NOT vehicle')
Scope Predicate	Restricts the search scope to children or descendants of a folder	IN_FOLDER(/Inbox)

Note: The NOT operator cannot be used with the IN_FOLDER or IN_TREE predicates.

For example:  Let's say you are querying an Exchange repository and want to find an email which contains the words "cake" and "free" but not "birthday" in it, that was not received last Christmas Day found in the inbox folder and has attachments.  That would look something like WHERE IN_FOLDER(/Inbox) AND CONTAINS('cake AND free AND NOT birthday') AND (DateTimeReceived<>'12/25/2018') AND (HasAttachments=False)

ORDER BY

This is an optional clause which allows you to specify the order in which results are returned.  You can sort by multiple properties using a comma separated list.  Optionally, each property name may be followed by ASC or DESC to indicate ascending or descending sort direction.  The default sort direction is ascending.

For example:  If you wanted to sort a query of an Exchange repository by both whether they have attachments and by size in descending order you would type ORDER BY HasAttachments, Size DESC

Putting It All Together

Let's mash all our examples together and search for email messages in the Inbox that have the words "cake" and "free"  but not "birthday" in the body, received any day besides Christmas Day.  I'm going to go ahead and search all the properties available to me, and I want to sort the results by whether the message has attachments and by size in descending order.  This would be the resulting query

SELECT * FROM Message WHERE IN_FOLDER(/Inbox) AND CONTAINS('cake AND free AND NOT birthday') AND (DateTimeReceived<>'12/25/2018') ORDER BY HasAttachments, Size DESC

Query Examples

Below are a few more examples of queries.

Filter	Description
Import all descendant files.  This will import all files in the repository without any foldering.
SELECT * FROM File WHERE AT_LEVEL(1)	Import files which are immediate children.  This will only import files at that level, not from subsequent levels.
SELECT * FROM Folder	Import folders which are immediate children.  This will import both files and their foldering.
SELECT * FROM File WHERE cmis:name MATCHES '^\d{4}-\d{2}-\d{2}'	Import files with a specific naming pattern, using regular expression.
SELECT * FROM File WHERE cmis:name LIKE 'ca%'	Import files with a name starting with ca.
SELECT * FROM File WHERE cmis:contentStreamLength > 10000	Import files larger than 10,000 bytes.