Scripting Class Object

Scripting Class Object

From within VBScript operations (ScriptedVariable, VBScriptProcedure, ScriptedField) in additional to normal VBScript functions, the developer has access to a custom “Connect Creatio” class. The tables below define its usage. This class may also be used with C# scripting.

Stage Variables

You can use these stage variables like you would use a variable in your code. For example, you could have VBScript where you wanted to see if a stage resulted in an Update or Insert, so you could do If "@@STG:0,#Action@@" = "Update" Then...

Property Name
Description
Action
Use in scripting as: "@@STG:0,#Action@@" for the first stage. Mostly used in an After Operation script. It will return if the stage was an Update, Insert, Ignore, or Error.

Class Properties

You can use these class properties like you would use a variable in your code. For example, you could have VBScript where you wanted to see if you were running or Previewing, so you could do If PreviewMode = True Then...

Property Name
Description
CurrentStageName
Read-only. Returns the name of the current stage. Useful during “Repeat Each Stage” Exec Operations to determine path of execution/next steps.
TotalRows
Returns the total number of Origin Rows in the job.
CurrentRow
Returns the current row number.
ReturnValue
Sets the return value to the calling application. Useful for passing an output back when the Job was called with an argument.
Origin ( String )
Returns the current Origin row’s value for the field specified.
PreviewMode
This boolean variable can be used to determine if you are Previewing or Running a Job. Returnes True of False. Ex: If PreviewMode <> True Then...
ErrorMessage
String containing the error which occurred. For use in the "On Row Error" event.
SuppressError
Set this boolean value to True within the "On Row Error" event to suppress the error message. The Error count will not be incremented and the error message will not be logged.

Class Methods

Custom Engine Settings & Variables Class Methods

These values can be seen and modified on the General tab in Connect Creatio. The values are stored in a local SQLite database, C:\inetpub\wwwroot\StarfishEngine\engine.db and are persistent across jobs and runs.

Method Name
Description
GetSetting(name As String) As String
Use this to retrieve an engine variable.
SaveSetting(name As String, value As String) As Boolean
Use this to set an engine variable.

Connect Creatio Stage & Job Control Class Methods

Method Name
Description
GotoStage ( String )
Directs Connect Creatio Engine to go directly to a Stage (skipping any others before it). String Parameter is the Name of the stage to go to. Once GotoStage has been called within a Job, it is considered in a “manual operation mode”. No more stages will be called in order and it will be up to the user to implement a After Each Stage Exec Operation to control stage flow. If no After Operation is called, it will go to the next row after the stage runs.
GotoJob ( String )
Directs Connect Creatio Engine to go directly to a Job. Pass in the GUID of the Job found on the General Tab, "Current Job ID". Please use with caution.
GotoNextRow ()
Skips all Stages and moves to the next Origin row.
EndJob ( String, Boolean(Optional) )
Cancels the Job execution immediately, ending with an error message as passed into the String param. The 2nd Boolean parameter indicates whether the job should be treated as if the job Failed (True/Default), or completed successfully (False).

Lookup and data retrieval Class Methods

Method Name
Description
GetFields ( String )
Executes a SQL SELECT statement returns all field values from the first row, comma-delimited. VBScript Split() function may be used to convert to an array within your function. Can improve performance when needing multiple fields from a single table.
ExecScalar ( String, String(Optional) )
Executes a SQL SELECT statement on the Destination database. Returns the first column of the first row returned by the query. Compatible with OLE DB destinations.

There is an optional parameter to query the origin. To query the origin, use the second parameter of "ORIGIN"EX: ExecScalar("select id from company", "ORIGIN")

ExecScalarCache ( String )
Same as ExecScalar, except that results are cached and before a query is executed, a search is performed on a locally-stored set of results to prevent execution of identical queries, thus boosting performance. Should only be used for queries where multiple identical queries may be run (such as looking up user id’s or picklist values) – otherwise could consume resources needlessly.
ExecSQL ( String, String(Optional) )
Executes a non-query statement, such as UPDATE or DELETE against the destination database. Returns number of rows affected. Compatible with OLE DB destinations. First parameter is SQL Statement. Second parameter is optional connection to run again. "DESTINATION" is default, other option is "ORIGIN".
SmartLookup ( String, String, String, [Boolean], [String], [String] )
Performs a lookup against the destination database, and returns one field value based on supplied filter.

String – Table to perform lookup in

String – Name of field to return

String – Filter code (such as Where clause, not including keyword “Where”)

Boolean – Default False. Enable cache for this lookup. This cache lasts for ALL chained Jobs. If you are looking up the Account in chained Jobs for Invoices, Payments, Sales Order, etc, it may be a good idea to enable caching.

String – Default Blank. Order By

String – Default "DESTINATION". Use "ORIGIN" to query the Origin.

SmartQuery ( String, [String], [String], String(Optional))
Performs a lookup query against the destination database, returns all columns and all rows from query as a multi-dimensional array.

String – Table to perform the query in

String – Filter code

String – Default All Columns. Comma-separated list of columns to return

String – Default "DESTINATION". Use "ORIGIN" to query the Origin.

If the filter and columns parameters are left blank, you may pass the full SQL statement into the first parameter.

EX: arr = SmartQuery("team_sets_teams","deleted = 0 and team_set_id = '@@ORG:team_set_id@@' and team_id <> '@@ORG:team_id@@'","team_id","ORIGIN")

SmartQueryDT ( String, [String], [String], String(Optional))
Same as above, except returns the results as a System.Data.DataTable object.

Intended for C# scripting only; to use it will be necessary to add the following to the .NET Global - External Assemblies: "System.dll,System.Xml.dll,System.Data.dll".

Other Class Methods

Method Name
Description
LogMessage ( String, MsgBoxStyle )
Appends a custom message to the Log which will always be displayed (even if Logging Level is set to None). Message is the passed in String parameter. Second parameter is a valid MsgBoxStyle (vbInformation, vbQuestion, vbExclamation, vbCritical)

C# Example: Starfish.LogMessage("foo");

SendEmail ( String, String, String, [String], [String], [String], [String], [Boolean] )
Sends an email directly from within the VBScript function/procedure. SMTP Server settings are pulled from the values entered on the General tab.

Parameters:

(String) To_Addr – Email Recipient Address(es), separate multiples with ";"

(String) Subject – Subject of the Email

(String) Body – Body of the Email

(Optional, String) From Address (default is what's entered on the general tab)

(Optional, String) From Display (default is what's entered on the general tab)

(Optional, String) CC Address(es)

(Optional, String) BCC Address(es)

(Optional, Boolean) Body Format HTML (default is false - plain text)

ParseName ( String, String )
Parses as string containing a person’s name, and returns the part of the name requested.

Parameters:

String – Input String containing entire name

Part – Part of the name to return. Valid parts are “Title”,”First”,”Last”,”Middle”,”Pedigree”,”Degree”.

Expects name in "First Last" format. Names passed in "Last, First" format will not be parsed correctly by this function.
RemoveIllegalXMLCharacters ( String )
This function removes Illegal XML Characters from a string. This function can be used on a string before inserting it into the Target to resolve the "error in msg parsing: XML error parsing SOAP payload on line 32: Invalid character" error.
GetStageValue ( Integer, String)
Returns the destination value used on a previous stage.

Integer – Index number of the stage, 0-based.

String – Field name of the value from this stage to return.

For some of the CRM Native API’s (SData, Microsoft), generated ID’s will be automatically stored in a field named “Key”.

SetLastRunDate
This will write the Last Run Date/Time to a local Xref. 
Note that this Method MUST be run in an After Operation and Executed as "Once After Conn".
 Use this to 
.

Use @@VAR:LastRunDate@@ in your origin to use the stored value. Note that you may run into TimeZone issues. Please see  Retrieve Records Modified After Last Run DateTime  for workarounds.

IsEmailValid ( String )
Returns True if supplied email address is in a valid email format; otherwise false.
FormatDate ( String, String )
Returns date in supplied format.

String – Date to format

String – Format String

Example:

newdt = FormatDate(olddt, “yyyy-MM-dd hh:mm:ss”)

This function uses the .NET DateTime formatting notations found here:   https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings .

File Class Methods

Method Name
Description
MoveFile ( String, String )
Moves a file on the server.

String – From filename path

String – To filename path

CopyFile ( String, String )
Copies a file on the server.

String – From filename path

String – To filename path

DeleteFile ( String )
Deletes a file on the server.

String – Filename path

ReadFile ( String )
Reads a file in text mode and returns the contents as a string.

String – Filename path

WriteFile ( String, String )
Writes the supplied contents to a file in text mode. Entire file is overwritten.

String – Filename path

String – contents to write to the file

Compression Class Methods

Method Name
Description
ExtractZipToDirectory ( String, String )
Extracts a zip archive to the specified path.

String – Source zip full path & filename

String – The path to extract to

CreateZipFromDirectory ( String, String )
Creates a new zip archive with the contents of the specified folder.

String – The path to the contents to be zipped

String – Full path & filename for the new zip archive

Xref Class Methods

Method Name
Description
XRefInitWrite ( String )
Initializes a cross-reference list for writing. If list exists, new data will be appended to it.

String – cross-reference list name

XRefInitRead ( String )
Initializes an existing cross-reference for reading. This reads all ids into memory so they will be available within your job.

String – cross-reference list name

XRefWrite ( String, String, String )
Writes a new ID reference entry to an initialized list.

String – cross-reference list name

String – ID 1 ("Old ID": the ID you’ll look up by in future jobs)

String – ID 2 ("New ID": the ID you’ll want returned by future lookups)

EX: XRefWrite "XrefName", "OldID", "NewID"

XRefRead ( String, String )
Reads an ID entry from an initialized list. (Returns ID 2 from above)

String – cross-reference list name

String – ID 1 ("Old ID")

XRefClear ( String )
Deletes all of the values within a list. Useful to call this function in a Pre-Process script to clear a list.

String – cross-reference list name

Xref-Flag Class Methods

Only compatible with database Xref format. These methods allow user to set a 'processed' flag against each entry within a Xref list. Useful in applications where you need to keep track of whether a particular records was processed or not. For example, if you need to detect whether records were removed from a source list where they previously existed. To query a list of unprocessed xref entries, it is recommended to set your Origin to use the SQLite Connector, connect to your engine.db, and read directly form the xref table.

Method Name
Description
XrefSetFlag ( String, String, Boolean )
Sets the flag value for a given entry within a list.

String – cross-reference list name

String – ID 1 (the "Old ID" value to look up the entry by)

Boolean - value to set the flag to (True/False)

XrefGetFlag ( String, String )
Gets the current Boolean flag value (True/False) for a given entry within a list.

String – cross-reference list name

String – ID 1 (the "Old ID" value to look up the entry by)

XrefClearFlag( String )
Sets the flag to False for all entries within a given list.

String – cross-reference list name

MS Dynamics CRM Specific Methods

Method Name
Description
GetMCRMPicklistValue( String, String, String )
Returns the integer value for a supplied picklist string.

String – Entity for the picklist (“account”)

String – Fieldname of the picklist (“accounttype”)

String – Value for the picklist (“Prospect”)

MCRMSetRelationship ( String, String, String, String, String )
Sets a relationship between two entites.

String – Relationship Name

String – Entity 1 Name

String – Entity 1 ID

String – Entity 2 Name

String – Entity 2 ID

SugarCRM (SOAP) Specific Methods

Method Name
Description
SugarSetRelationship (String, String, String, String )
Sets a relationship between two modules. Returns True or False based on the success of creating a relationship.

String – Module 1 Name

String – Module 1 ID

String – Module 2 Name

String – Module 2 ID

SugarSetRelationshipv4 (String, String, String, String, Number(Optional) )
Sets a relationship between two entities, compatible also with custom modules. Returns True or False based on the success of creating a relationship. Also able to remove a relationship between two entities with the optional fifth parameter.

String – Module 1 Name

String – Module 1 ID

String – Relationship Name

String – Module 2 ID

String – [Delete] (Optional) – Default is 0 (false). Set to 1 (true) to Delete a relationship.

SugarGetRelationships ( String, String, String )
Gets comma-separated a list of ID’s related to an entity

String – Module 1 Name

String – Module 1 ID

String – Module 2 Name

SugarGetRelationshipsv4 ( String, String, String )
Gets comma-separated a list of ID’s related to an entity

String – Module 1 Name

String – Module 1 ID

String –Relationship Name

Email (IMAP) Specific Methods

Method Name
Description
MoveIMAPMessage ( String )
Moves the current email message to the specified folder on the server.

String – Name of IMAP folder to move message to

Conversion Methods

Method Name
Description
ConvertRTFToText ( String ), ConvertHTMLToText ( String ), ConvertBase64ToBytes ( String )
Converts the String supplied.

SQLite Class Methods

Method Name
Description
SQLiteInsert
Returns boolean, true if successful: b = SQLiteInsert("test_table", "field1,field2", "'@@ORG:DirectoryRelative@@','@@ORG:Name@@'")
SQLiteSelect
Returns a multi-dimensional array: b = SQLiteSelect("test_table", "field1,field2", "field2='@@ORG:Name@@'")

EX: 

                  
                     
                        
                           if
                        
                     
                  
                  
                  
                  
                     
                        UBound
                     
                  
                  
                     
                        (
                     
                  
                  
                     
                        b
                     
                  
                  
                     
                        )
                     
                  
                  
                     
                        >-
                     
                  
                  
                     
                        1
                     
                  
                  
                  
                  
                     
                        
                           then
                        
                     
                  
                  
                  
                  
                     
                        
                           'there is data
                        
                     
                  
                  
                  
                  
                     
                        logmessage
                     
                  
                  
                  
                  
                     
                        b
                     
                  
                  
                     
                        (
                     
                  
                  
                     
                        0
                     
                  
                  
                     
                        ,
                     
                  
                  
                     
                        0
                     
                  
                  
                     
                        )
                     
                  
                  
                  
                  
                     
                        logmessage
                     
                  
                  
                  
                  
                     
                        b
                     
                  
                  
                     
                        (
                     
                  
                  
                     
                        0
                     
                  
                  
                     
                        ,
                     
                  
                  
                     
                        1
                     
                  
                  
                     
                        )
                     
                  
                  
                  
                  
                     
                        
                           else
                        
                     
                  
                  
                  
                  
                     
                        logmessage
                     
                  
                  
                  
                  
                     
                        "no data"
                     
                  
                  
                  
                  
                     
                        
                           end
                        
                     
                  
                  
                  
                  
                     
                        
                           if

    • Related Articles

    • Using Connect Creatio Scripting Class Properties & Methods in C#

      See available Connect Creatio Scripting Class Variables, Properties and Methods. To use these variables, properties and methods, you must append "Starfish." to the beginning of variable, property or method AND you must use the exact capitalization as ...

    • Examples of using Connect Creatio Class Functions In Javascript

      This code was used to lookup a value inside of SugarCRM using the Sugar REST connector. function scriptedField() { var res = ""; res = vals["Branch Name"]; if (res) { Starfish.LogMessage(res.toString()); } res = ...

    • Clarizen Connector

      Connecting to Clarizen Set up your Clarizen connector using your normal Clarizen username and password. You'll be able to select which environment you want to connect to if you have access to multiple Clarizen sites. Origin Queries Origin queries use ...

    • Convert Time Zone

      object ScriptedField() { String source = Starfish.OriginData["DATETIMERECEIVED"].ToString(); DateTime dt = Convert.ToDateTime(source); TimeZoneInfo timeInfo = TimeZoneInfo.FindSystemTimeZoneById("Pacific Standard Time"); return ...

    • Interact with the local file system

      Read files from a folder Function ScriptedVariable Dim fso, folder, files, sFolder, res Set fso = CreateObject("Scripting.FileSystemObject") sFolder = "C:\Results" Set folder = fso.GetFolder(sFolder) Set files = folder.Files For each folderIdx In ...