Upsert Workspace | Administrate

This page details the specific configuration and input options for the Upsert Workspace Orchestrator.

This guidance should be read in conjunction with the Common Configuration page which details the standard application configuration.

Purpose

The key purpose of this Orchestrator is to create and update workspace profiles.

Operation Name

UpsertWorkspace

Common Configuration

There is common configuration available within this job Orchestrator. The common configuration options can be found here.

Input Data Schema

Data can be read via SQL or CSV and requires the following data headers and values:

CSV

Headers	Description	Mandatory	Expected Value
`Id`	This is the row ID	Yes	Numeric value
`Action`	Specifies the type of action to apply to the row	Yes	Either: `Create` `Update` (requires `TargetObjectSearchQuery)` You can use an empty value (“”) which will update if a match is found or create if no match is found
`TargetLibraryId`	The ID of the iManage Library	Yes	iManage Library identifier i.e. ‘Active’
`TargetObjectId`	Allows you to target a specific workspace Id If `Action` = `Update` and no result is found then the Workspace will be created	No (unless `Action` = `Update` and `TargetObjectSearchQuery`is blank)	iManage identifier i.e. ‘Active!1234’
`TargetObjectSearchQuery`	A JSON object which contains a dictionary matching any applicable profile parameters for the corresponding `TargetObjectType` Multiple parameters can be used in the search query If `Action` = `Update` and no result is found then the Workspace will be created	No (unless `Action` = `Update` and `TargetObjectId`is blank)	JSON format i.e. `{"custom1": "*", "custom2": "test"}` if using csv input then escaped values need to be used for multiple parameter queries. The following keys can be provided: `custom 1-12, 17-20, 23-30` `name`
`TemplateName`	Specifies the specific template to be applied to the workspace	Yes (for creation only)
`Author`	Specifies the Author	No
`Name`	Specifies the Name	Yes (for creation only)
`Owner`	Specifies the Owner	Yes (for creation only)
`OwnerDescription`	Specifies the Owner Description	No
`Class`	Specifies the Class	No
`ClassDescription`	Specifies the Class Description	No
`Subclass`	Specifies the Subclass	No
`SubclassDescription`	Specifies the Subclass Description	No
`DefaultSecurity`	Specifies the Default Security	Yes (for creation only)
`Description`	Specifies the Description	No
`Custom1`	Specifies the name of the entry for this custom field.	No (Unless Custom 2 is specified)	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom1Description`	Specifies an optional description for the custom field.	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 254
`Custom2`	Specifies the name of the entry for this custom field.	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom2Description`	Specifies an optional description for the custom field.	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 254
`Custom3`	Specifies the name of the entry for this custom field.	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom3Description`	Specifies the Custom 3 Description	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 254
`Custom4`	Specifies the name of the entry for this custom field.	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom4Description`	Specifies the Custom 4 Description	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 254
`Custom5`	Specifies the Custom 5 Value	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom5Description`	Specifies the Custom 5 Description	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 254
`Custom6`	Specifies the Custom 6 Value	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom6Description`	Specifies the Custom 6 Description	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 254
`Custom7`	Specifies the Custom 7 Value	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom7Description`	Specifies the Custom 7 Description	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 254
`Custom8`	Specifies the Custom 8 Value	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom8Description`	Specifies the Custom 8 Description	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 254
`Custom9`	Specifies the Custom 9 Value	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom9Description`	Specifies the Custom 9 Description	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 254
`Custom10`	Specifies the Custom 10 Value	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom10Description`	Specifies the Custom 10 Description	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 254
`Custom11`	Specifies the Custom 11 Value	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom11Description`	Specifies the Custom 11 Description	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 254
`Custom12`	Specifies the Custom 12 Value	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom12Description`	Specifies the Custom 12 Description	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 254
`Custom17`	Specifies the Custom 17 Value	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom18`	Specifies the Custom 18 Value	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom19`	Specifies the Custom 19 Value	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom20`	Specifies the Custom 20 Value	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom23`	Specifies the Custom 23 Value	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom24`	Specifies the Custom 24 Value	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom25`	Specifies the Custom 25 Value	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom26`	Specifies the Custom 26 Value	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom27`	Specifies the Custom 27 Value	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom28`	Specifies the Custom 28 Value	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom29`	Specifies the Custom 29 Value	No (unless Custom 30 is specified)	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom29Description`	Specifies the Custom 29 Description	No (unless Custom 30 is specified)	Alphanumeric: a-z, A-Z, 0-9 Max length: 254
`Custom30`	Specifies the Custom 30 Value	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 32 Spaces are not allowed
`Custom30Description`	Specifies the Custom 30 Description	No	Alphanumeric: a-z, A-Z, 0-9 Max length: 254
`Processed`	Indicates if the row has been processed	No	'0'

SQL

SQL Input uses the same values as the CSV input above. The SQL Schema is detailed below, with the addition of three fields, that are used to track the status of the record:

SQL

CREATE TABLE UpsertWorkspaceInput (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Action NVARCHAR(50) NOT NULL,
    TargetLibraryId NVARCHAR(255) NOT NULL,
    TargetObjectId NVARCHAR(255),
    TemplateName NVARCHAR(255),
    TargetObjectSearchQuery NVARCHAR(MAX),
    Profile_Author NVARCHAR(255),
    Profile_Custom1 NVARCHAR(255),
    Profile_Custom1Description NVARCHAR(255),
    Profile_Custom2 NVARCHAR(255),
    Profile_Custom2Description NVARCHAR(255),
    Profile_Custom3 NVARCHAR(255),
    Profile_Custom3Description NVARCHAR(255),
    Profile_Custom4 NVARCHAR(255),
    Profile_Custom4Description NVARCHAR(255),
    Profile_Custom5 NVARCHAR(255),
    Profile_Custom5Description NVARCHAR(255),
    Profile_Custom6 NVARCHAR(255),
    Profile_Custom6Description NVARCHAR(255),
    Profile_Custom7 NVARCHAR(255),
    Profile_Custom7Description NVARCHAR(255),
    Profile_Custom8 NVARCHAR(255),
    Profile_Custom8Description NVARCHAR(255),
    Profile_Custom9 NVARCHAR(255),
    Profile_Custom9Description NVARCHAR(255),
    Profile_Custom10 NVARCHAR(255),
    Profile_Custom10Description NVARCHAR(255),
    Profile_Custom11 NVARCHAR(255),
    Profile_Custom11Description NVARCHAR(255),
    Profile_Custom12 NVARCHAR(255),
    Profile_Custom12Description NVARCHAR(255),
    Profile_Custom17 FLOAT,
    Profile_Custom18 FLOAT,
    Profile_Custom19 FLOAT,
    Profile_Custom20 FLOAT,
    Profile_Custom23 DATETIMEOFFSET,
    Profile_Custom24 DATETIMEOFFSET,
    Profile_Custom25 BIT,
    Profile_Custom26 BIT,
    Profile_Custom27 BIT,
    Profile_Custom28 BIT,
    Profile_Custom29 NVARCHAR(255),
    Profile_Custom29Description NVARCHAR(255),
    Profile_Custom30 NVARCHAR(255),
    Profile_Custom30Description NVARCHAR(255),
    Profile_DefaultSecurity NVARCHAR(255),
    Profile_Description NVARCHAR(MAX),
    Profile_Name NVARCHAR(255),
    Profile_Owner NVARCHAR(255),
    Profile_OwnerDescription NVARCHAR(255),
    Profile_Class NVARCHAR(255),
    Profile_ClassDescription NVARCHAR(255),
    Profile_Subclass NVARCHAR(255),
    Profile_SubclassDescription NVARCHAR(255),
    Processed BIT NOT NULL,
    DateAddedUtc DATETIME NOT NULL DEFAULT (GETUTCDATE()),
    DateUpdatedUtc DATETIME,
    Profile_C1C2 NVARCHAR(255)
);

CREATE TRIGGER trg_UpsertUpdateDateTime
ON dbo.UpsertWorkspaceInput
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    /* 
       Avoids recursion if not disabled in the server. 
       If DateUpdateUtc is updated, regardless of other updates, it will not 
       change the date
    */
    IF NOT UPDATE(DateUpdatedUtc)
    BEGIN
        UPDATE u
        SET u.DateUpdatedUtc = GETUTCDATE()
        FROM dbo.UpsertWorkspaceInput u
        INNER JOIN Inserted i ON u.Id = i.Id;
    END
END;

CREATE NONCLUSTERED INDEX IX_UpsertWorkspaceInput_Profile_C1C2
ON dbo.UpsertWorkspaceInput (Profile_C1C2);

Job File Options

This section details the configurable options within the orchestrator.

Orchestrator Level Options

Configuration	Description	Expected Value
`CreateMissingMetadata`	If enabled then any custom metadata (Custom1-30) which does not currently exist will be created	`true` `false`
`ActionMultipleQueryResults`	If enabled then the application will process all results returned by the search query. When disabled then the application will only process the request if one search result is identified	`true` `false`

Create Options

Configuration	Description	Expected Value
`Enabled`	Sets whether the create process is enabled or disabled	`true` `false`
`UseTemplateDataAsPriority`	If enabled it will use metadata set under the template as the primary update if values are provided in both the template and the profile input	`true` `false`

Update Options

Configuration	Description	Expected Value
`Enabled`	Sets whether the update process is enabled or disabled	`true` `false`
`ExplicitRefile`	Forces a workspace refile after update	`true` `false`
`UseTemplateDataAsPriority`	If enabled it will use metadata set under the template as the primary update if values are provided in both the template and the profile input	`true` `false`

Update Workspace Options

Configuration	Description	Expected Value
`UpdateMetadata`	Whether to update the non-security metadata on the workspace	`true` `false`
`UpdateDefaultSecurity`	Whether to update the default security on the workspace	`true` `false`
`ACLUpdateBehaviour`	Sets the behaviour for ACL updates	Either: `Override` values from the workspace are completely replaced `Patch` existing values from the workspace are left, additional entries from the template are added `Skip` ACL updates are not processed

Configuration

Description

Expected Value

UpdateMetadata

Whether to update the non-security metadata on the workspace

true false

UpdateDefaultSecurity

Whether to update the default security on the workspace

true false

ACLUpdateBehaviour

Sets the behaviour for ACL updates

Either:

Override values from the workspace are completely replaced
Patch existing values from the workspace are left, additional entries from the template are added
Skip ACL updates are not processed

Update Folder Options

These are the configuration options available under ‘TemplateSync’

Function	Configuration	Description	Expected Value
Matched	`UpdateMetadata`	If enabled then existing metadata will be updated	`true` `false`
Matched	`UpdateSecurity`	If enabled then both Default Security and ACL updates will be carried out as per the template or input data	`true` `false`
Missing	`Action`	Sets the behaviour for any existing folders that are not included in the new template	Either: `Remove` folders will be deleted `RemoveIfEmpty`folders will only be deleted if they are empty `Skip`folders will not be deleted
Missing	`DeleteSearchFolders`	If enabled then search folders not included in the new template will be deleted	`true` `false`

Output Events

These output events are specific to the Upsert Workspace Orchestrator and works in addition to the common output events detailed in Common Configuration

The following output events can be persisted via the ‘Subscribed To’ configuration

Target	Action	Status	Full Event Identifier

These should be listed in the Orchestrator in the following format ‘Target:Action:Status’.

The event output schema is shown below:

SQL

CREATE TABLE UpsertWorkspaceOutput (
   Id INT IDENTITY(1,1) PRIMARY KEY,
   EventTime DATETIMEOFFSET NOT NULL,
   CorrelationId VARCHAR(36) NOT NULL,
   [Action] VARCHAR(30) NOT NULL,
   [Status] VARCHAR(25) NOT NULL,
   [Message] NVARCHAR(MAX) NOT NULL,
   DataSourceRowId VARCHAR(25),
   TargetObjectType VARCHAR(25) NOT NULL,
   TargetObjectId VARCHAR(25),
   TargetSearchQuery NVARCHAR(MAX) NULL,
   TargetSearchQueryResultCount VARCHAR(10) NULL,
   TargetSearchQueryResultList NVARCHAR(MAX) NULL,
   Workspace NVARCHAR(MAX) NULL,
   AppliedTemplateName NVARCHAR(255) NULL
);

Sample Job File

This is an example Job File for the UpsertWorkspace Orchestrator:

JSON

{
  "Operation": "UpsertWorkspace",
  "Options": {
    "Create": {
      "Enabled": true,
      "UseTemplateDataAsPriority": false
    },
    "Update": {
      "Enabled": true,
      "ExplicitRefile": false,
      "UseTemplateDataAsPriority": true,
      "Workspace": {
        "UpdateMetadata": true,
        "UpdateDefaultSecurity": false,
        "AclUpdateBehaviour": "Skip"
      },
      "Folder": {
        "TemplateSync": {
          "Enabled": true,
          "Matched": {
            "UpdateMetadata": true,
            "UpdateDefaultSecurity": false,
          },
          "Missing": {
            "Action": "RemoveIfEmpty",
            "DeleteSearchFolders": true
          }
        }
      }
    },
    "CreateMissingMetadata": true,
    "ActionMultipleQueryResults": false
  },
  "InputType": "CSV",
  "Inputs": {
    "CSV": {
      "Path": "<repo>\\source\\WorkspaceAutomate\\Test Files\\Csvs\\UpsertWorkspace.csv"
    }
  },
  "Outputs": {
    "Console": {
      "Enabled": true,
      "SubscribedTo": ["*"]
    }
  },
  "Integrations": {
    "IManage": {
      "URL": "<url>",
      "Username": "<username>",
      "Password": "<password>"
    }
  }
}

Failure and Skip Behaviour

This Orchestrator follows the standard behaviour for failed/skipped records:

Skipped = Scenarios configured to not be processed for a specific reason e.g. Update Enabled: falsewould skip any processing of the the row if a matching workspace was found.

Failed = Administrate tried to perform an action on the target object and an unexpected error occurred. e.g. iManage connection dropped and Administrate was unable to delete a document that was configured to be deleted.

iManage Behaviour

This section details the different behaviour across different versions of iManage i.e. On-Prem, iManage Cloud and Cloud iManage.

There are no differences for this operation between On-Prem and Cloud iManage.