Retrieve Records Modified After Last Run DateTime

Retrieve Records Modified After Last Run DateTime

To only run data modified after the last time Connect Creatio was run, we need to do two things:

  1. Set the last time Connect Creatio ran.
  2. Filter the origin based on the last time Connect Creatio ran.

Set Last Run DateTime

Create an After Operation Procedure and run the built in Connect Creatio Function SetLastRunDate. Set Exec When to "Once After Conn". It is necessary to add this, otherwise the last successful run date will not get set.

Sub VBScriptProcedure
 SetLastRunDate
End Sub

Filter Origin Based On Last Run DateTime

First you must retrieve the DateTime your job was last run. This can be inserted directly into the Origin Filter by using the @@VAR:LastRunDate@@ variable. An example of that in a SQL origin would look something like this:

SELECT * FROM Contacts WHERE ModifyDate >= '@@VAR:LastRunDate@@'

In many applications, DateTimes are converted to GMT in database, so you must convert your last run DateTime to GMT before inserting it into your Origin Filter. To do this, Run a Before Operation set to Exec When "Once Before Conn". You could create any number of functions, but this is the one I've used:

Function ScriptedVariable
 Dim gmtLastRun
 gmtLastRun = CDate("@@VAR:LastRunDate@@")
 gmtLastRun = DateAdd("h", 7, gmtLastRun)
 'Sometimes, the Starfish server and the Source Server clocks are not quite in sync, so I subtract 1 minute from my last run datetime to guarantee I don't miss any records.
 gmtLastRun = DateAdd("n", -1, gmtLastRun)
 ScriptedVariable=FormatDate(gmtLastRun, "yyyy-MM-dd HH:mm:ss")
End Function

Then insert the resulting date into your Origin Filter:

cast(integration_modify_date_c as datetime) >= '@@VAR:GMTLastRunDate@@'

or

[{"date_modified":{"$gte":"@@VAR:GMTLastRunDate@@"}}]

    • Related Articles

    • Run Job Options

      Definitions/explanations of the different options for when you wish to run a job. Option Name Description Begin at Row Begin at Row/Leave blank to start at the first row. End at Row End at Row/Leave blank to process through to the end of all rows. ...

    • Using Row Hashing for Incremental Integrations

      Hashing The Whole Record Typically when setting up an ongoing integration, we only want to pull data from your origin which has changed since the last time Connect Creatio ran. This is accomplished using LastRunDate and applying it as a filter ...

    • 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 ...

    • SugarCRM REST Connector

        SugarCRM REST Origin Use JSONLint to validate JSON: https://jsonlint.com/. Sample Origin Filters See the GET /<module> filterList in the SugarCRM REST Help: https://SERVER/rest/v10/help/ Note the [{...}] surrounding the filter. This is required. ...

    • Use the same job to loop through multiple origins

      This example looks at reading from multiple mailboxes for email. Use a variable in the origin connection string. If the job needs to run for another mailbox, it calls the GotoJob function in the “Once After Conn” and pass in the Job's ID. Similar ...