From within VBScript operations (ScriptedVariable, VBScriptProcedure, ScriptedField) in additional to normal VBScript functions, the developer has access to a custom “Starfish” class. The tables below define its usage. This class may also be used with C#, Javascript and Python scripting. For VBscript you can access the method directly. For other language, you must prefix the property and method names with "Starfish.", such as Starfish.JobName.
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 | Mostly used in an After (Repeat Each Stage) Operation script. It will return the action that was taken for a given stage. Returned values can be "Update", "Insert", "Skip", or "Ignore". When using in VBScript, you can reference as "@@STG:0,#Action@@" for the first stage. For use in C#, Javascript & Python use Starfish.GetStageVariable(0, "#Action"). example C# script which tests the stage Action to see if it resulted in an Insert, if so it calls another stage - otherwise it skips to the next row:
|
#ID | In the result of a Stage action "Insert" or "Updated", the ID returned is ID or Primary Key of the record that was created or updated in this stage. The format of this ID depends on the connection type used for your Destination and is returned by the underlying API being called. example C# script which logs the the first stage's ID and writes it to an Xref list:
|
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.
Property Name | Description |
---|---|
string ProjectName | Read-only.
Returns the name of the Project that the currently running Job belongs to. |
string JobName | Read-only.
Returns the name of the currently running Job. |
string CurrentStageName | Read-only. Returns the name of the current stage. Useful during “Repeat Each Stage” Exec Operations to determine path of execution/next steps. |
string TotalRows | Returns the total number of Origin Rows in the job. |
string CurrentRow | Returns the current row number. |
string ReturnValue | Sets the return value to the calling application. Useful for passing an output back when the Job was called with an argument. |
string Origin (string fieldName) | Returns the current Origin row’s value for the field specified as a string. NULL values are treated as an empty string. |
object OriginData [string fieldName] | Returns the current Origin row’s value for the field specified in it's native data type. |
bool 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... |
These values can be seen and modified on the Variables tab under at the Project level. The values are persistent across jobs and runs, and are project-specific.
Method Name | Description |
---|---|
string GetSetting ( string name ) | Retrieve a project variable by name. example: var lastOrderDate = Starfish.GetSetting("LastOrderDate") |
void SaveSetting ( string name, string value ) | Save a project variable by passing in the name and value. If a variable already exists by the same name, it is overwritten. example: Starfish.SaveSetting("LastOrderDate", Starfish.Origin("OrderDate")); |
string DeleteSetting ( string name ) | Deletes the project variable by name. |
Method Name | Description |
---|---|
void GotoStage ( string stageName ) | Directs Starfish 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. GotoStage Parameters:
example C# to run different stages, depending on an Origin row field:
|
void GotoJob ( string jobId ) | Directs Starfish Engine to go directly to a Job. Pass in the GUID of the Job found on the Settings Tab, "Current Job ID". Please use with caution, without checks, it would be possible to get a job stuck in an infinite loop. GotoJob Parameters:
|
void GotoNextRow () | Skips all Stages and moves to the next Origin row. example C# to skip a row, depending on the value of an Origin row field:
|
void EndJob ( string reason, [bool treatAsFailure] ) | Cancels the Job execution immediately, ending with the reason provided. By default, calling EndJob is treated as an error/failure - and will result in failure email notifications being sent. If calling EndJob is a part of normal operation, and you don't want it to be treated as a failure, pass in false for the 2nd parameter. EndJob Parameters:
example: Starfish.EndJob("no file available for processing", false); |
Method Name | Description |
---|---|
object ExecScalar ( string sql, [string connection] ) | Executes a SQL SELECT statement against the Destination database. Returns the first column of the first row returned by the query. Compatible with SQL-compliant connections. Default connection is the Destination. To query the origin instead, use the second parameter of "ORIGIN". ExecScalar Parameters:
example: Starfish.ExecScalar("select id from company", "ORIGIN") |
object ExecScalarCache ( string sql, [string connection] ) | 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. ExecScalarCache Parameters:
|
void ExecSQL ( string sql, [string connection] ) | Executes a non-query statement, such as UPDATE or DELETE against the destination database. Returns number of rows affected. Compatible with SQL-compliant connections. ExecSQL Parameters:
|
object SmartLookup ( string tableName, string fieldName, string queryFilter, [bool enableCache], [string orderBy], [string connection] ) | Performs a lookup against the destination database, and returns one field value based on supplied filter. SmartLookup Parameters:
|
object SmartQuery ( string tableName, [string queryFilter], [string fieldNames], [string connection] ) | Performs a lookup query against the destination database, returns all columns and all rows from query as a multi-dimensional array. SmartQuery Parameters:
If the filter and columns parameters are left blank, you may pass a full SQL statement into the first parameter for SQL-compliant connections. example: arr = SmartQuery("team_sets_teams","deleted = 0 and team_set_id = '@@ORG:team_set_id@@' and team_id <> '@@ORG:team_id@@'","team_id","ORIGIN") |
object SmartQueryDT ( string tableName, [string queryFilter], [string fieldNames], [string connection] ) | 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". SmartQueryDT Parameters:
example C# to retrieve lines for a given Order and concatenate some simple information together as a string:
|
Method Name | Description |
---|---|
void LogMessage ( string message, [MsgBoxStyle classification] ) | Appends a custom message to the Log which will always be displayed (even if Logging Level is set to None). LogMessage Parameters:
example: Starfish.LogMessage("foo"); |
string SendEmail ( string toAddress, string subject, string body, [string fromAddress], [string fromDisplay], [string ccAddress], [string bccAddress], [bool bodyHtml], [string attachments] ) | Sends an email directly from within the VBScript function/procedure. SMTP Server settings are pulled from the values entered on the General tab. SendEmail Parameters:
Returns: String if there was an error, will contain the mail server error message. If the return string is empty, the email was sent successfully. example C# script to send an email containing information for the Origin row:
|
string ParseName ( string fullName, string namePart ) | Parses as string containing a person’s name, and returns the part of the name requested. ParseName Parameters:
Returns: String with the part of the name requested. If this part couldn't be found within the full name, a blank string is returned. This function expects name in "First Last" format. Names passed in "Last, First" format will not be parsed correctly. example C# to extract parts of a person's name:
|
string RemoveIllegalXMLCharacters ( string xmlString ) | 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. RemoveIllegalXMLCharacters Parameters:
Returns: String with the illegal characters removed. |
string GetStageValue ( int stageIndex, string fieldName ) | Returns the destination value used on a previous stage. GetStageValue Parameters:
Returns: The value of the field from the stage requests. See the "Stage Values" section above for use in retrieving the stage #Action and #ID. |
void SetLastRunDate ( [DateTime lastRunDate] } | This will write the Last Run Date/Time to an Xref List within your project named "lastrundates". Note that this Method MUST be run in an After Operation and Executed as "Once After Conn". Use this to Retrieve Records Modified After Last Run DateTime. 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. SetLastRunDate Parameters:
|
bool IsEmailValid ( string emailAddress ) | Returns True if supplied email address is in a valid email format; otherwise false. IsEmailValid Parameters:
Returns: Boolean indicating if the email address if formatted correctly. |
string FormatDate ( string date, string formatString ) | Returns date in supplied format. 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 FormatDate Parameters:
Returns: String of the date in the new format. If an invalid date or format is provided, function results in a blank string. C# example: var newdt = FormatDate(olddt, “yyyy-MM-dd hh:mm:ss”) |
string GetMD5Hash ( string source ) | Returns an MD5 encoded hash of the provided source string.
|
string GetSHA256Hash ( string source ) | Returns an SHA256 encoded hash of the provided source string.
|
Method Name | Description |
---|---|
bool FileExists ( string fileName ) | Returns boolean indicating whether the file by the provided name exists. These file operations are performed within your instance's "Files" area. FileExists Parameters:
Returns: Boolean - true if the file exists, false if not. |
bool MoveFile ( string sourceFile, string destinationFile ) | Moves a file on the server. MoveFile Parameters:
Returns: Boolean indicating if the operation was successful. |
bool CopyFile ( string sourceFile, string destinationFile ) | Copies a file on the server. CopyFile Parameters:
|
bool DeleteFile ( string fileName ) | Deletes file on the server. DeleteFile Parameters:
|
string ReadFile ( string fileName ) | Reads a file in text mode and returns the contents as a string. ReadFile Parameters:
|
bool WriteFile ( string fileName, string contents ) | Writes the supplied contents to a file in text mode. If the file doesn't exist, it gets created. If the already exists, it is is overwritten. WriteFile Parameters:
|
Method Name | Description |
---|---|
bool ExtractZipToDirectory ( string fileName, string path ) | Extracts a zip archive to the specified path. ExtractZipToDirectory Parameters:
Returns: Boolean indicating if the operation was successful. |
bool CreateZipFromDirectory ( string path, string fileName) | Creates a new zip archive with the contents of the specified folder. CreateZipFromDirectory Parameters:
Returns: Boolean indicating if the operation was successful. |
Method Name | Description |
---|---|
void XRefWrite ( string listName, string oldId, string newId ) | Writes a entry to a Xref list. XRefWrite Parameters:
example C# script which writes a Stage #ID result to an xref list:
|
string XRefRead ( string listName, string oldId ) | Reads an ID entry from an Xref list. (Returns ID 2 from above) XRefRead Parameters:
example C# script which reads from an Xref list based on an existing ID.
|
void XRefClear ( string listName ) | Deletes all of the values within a Xref list. Useful to call this function in a Pre-Process script to clear a list. XRefClear Parameters:
|
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 |
---|---|
void XrefSetFlag ( string listName, string oldId, bool flag ) | Sets the flag value for a given entry within a list. XrefSetFlag Parameters:
|
bool XrefGetFlag ( string listName, string oldId ) | Gets the current Boolean flag value (True/False) for a given entry within a list. XrefGetFlag Parameters:
Returns: Boolean with the flag value. |
bool XrefClearFlag ( string listName ) | Sets the flag to False for all entries within a given list. XrefClearFlag Parameters:
|
Method Name | Description |
---|---|
string ConvertRTFToText ( string rtfString ) | Converts the supplied Rich Text string to Plain Text. ConvertRTFToText Parameters:
Returns: Plain text string, with all formatting removed. |
string ConvertHTMLToText ( string htmlString ) | Converts the supplied HTML string to Plain Text. ConvertHTMLToText Parameters:
|
byte[] ConvertBase64ToBytes ( string base64String ) | Converts the supplied Base64 string to a byte array/binary. ConvertBase64ToBytes Parameters:
|
DateTime ConvertTimeZone ( DateTime sourceDate string sourceTimeZone string destinationTimeZone ) | Converts the given DateTime from one Time Zone to another. ConvertTimeZone Parameters:
Returns: DateTime converted to the new Time Zone. |
Method Name | Description |
---|---|
bool ParseJson ( string json ) | Loads the passed in string into the JSON parser. For use in subsequent calls to GetJSON. ParseJSON
values do not get reset between each row. You have to run
ParseJSON("{}") in a before operation if you want to reset the JSON for
GetJSON back to empty. ParseJson Parameters:
Returns: Boolean indicating if the JSON was valid and loaded. |
string GetJSON ( string item1, [string item2], [string item3], [string item4], [string item5], [string item6], [string item7], [string item8], [string item9] ) | Returns a given section from the JSON supplied in the last ParseJson call. The optional strings are for accessing nested values, you can pull data up to 9 levels deep. GetJSON Parameters:
Returns: A string containing the JSON portion requested. GetJSON Usage Example For example, if you had this JSON, which was borrowed from https://adobe.github.io/Spry/samples/data_region/JSONDataSetSample.html {
"id": "0001",
"type": "donut",
"name": "Cake",
"ppu": 0.55,
"batters":
{
"batter":
[
{ "id": "1001", "type": "Regular" },
{ "id": "1002", "type": "Chocolate" },
{ "id": "1003", "type": "Blueberry" },
{ "id": "1004", "type": "Devil's Food" }
]
},
"topping":
[
{ "id": "5001", "type": "None" },
{ "id": "5002", "type": "Glazed" },
{ "id": "5005", "type": "Sugar" },
{ "id": "5007", "type": "Powdered Sugar" },
{ "id": "5006", "type": "Chocolate with Sprinkles" },
{ "id": "5003", "type": "Chocolate" },
{ "id": "5004", "type": "Maple" }
]
}
And you wanted to get the type of the 2nd batter, you would do this with 0-based indexing: var val = Starfish.GetJSON("batters","batter",1,"type"); val would equal “Chocolate”. |
int JSONArrayCount () | Returns the number of elements within the JSON array loaded using ParseJson. |
Method Name | Description |
---|---|
string GetQueuedJSON ( [string dataset] ) | Retrieves the data registered by queued stages in JSON format. GetQueuedJSON Parameters:
Returns: JSON Array containing the data that was loaded during the Queued stage execution. |
string GetQueuedXML ( [string dataset] ) | Retrieves the data registered by queued stages in XML format. GetQueuedXML Parameters:
|
string GetQueuedDT ( [string dataset] ) | Retrieves the data registered by queued stages as a .NET DataTable object, for use in C# scripting only. GetQueuedDT Parameters:
|
void SetQueuedJSON ( string json, [string dataset] ) | Sets the queued data by taking a JSON Array input, which can be used for "Repeat" stages. Used to make a Stage iterate over multiple sub-records per row. In the subsequent stages which pulls out this data, you use the Queued("fieldName") function to retrieve data at the field-mapping level. SetQueuedJSON Parameters:
|
void SetQueuedDT ( DataTable dt, [string dataset] ) | Sets
the queued data by taking a .NET DataTable object which can be used for "Repeat" stages. Used to make a
Stage iterate over multiple sub-records per row. In the subsequent
stages which pulls out this data, you use the Queued("fieldName")
function to retrieve data at the field-mapping level. For use in C# scripting only. Useful for when queuing data from the result of a SmartQueryDT() function call. SetQueuedDT Parameters:
|
object Queued ( string fieldName ) | Retrieves a single field value's from the Queued dataset, typically for use when a Stage's Queued Behavior is set to "Repeat". Queued Parameters:
Returns: String containing the data for the requested field. |