Capture Data Changes with Source Field Values¶

Use Case¶

Similar to Capturing Data Changes with Timestamp-based Queries, rather than use a timestamp to pick up data to be processed, this pattern queries for the value in a field. Typically the source uses triggers or rules to identify records to be changed and changes the value in a field (like Ready To Send) from false to true. The query in Jitterbit will use that field in the condition. The advantage here over using timestamps is that the source system uses its internal business rules to determine data to move. Timestamps alone frequently require additional complex filtering to accommodate business logic.

Example¶

In this case, both source and target use a Ready_To_Sync and a Sync_Error field. If that target has a Sync_Error, then no update should be made and the source record should be updated so that it can be reviewed and fixed. As we'll see, this kind of mutual synchronization requires pre-checks before updating, success and error paths.

First, the script checks for any data to be picked up, which is the operation that contains the schedule. Next a query is executed to fetch the rest of the data and has two checks. If incoming data is invalid, an update is launched. Also, a lookup of a field in the target is made. The main success scenario is that data is updated in NetSuite, and key data is written back to the target. The error scenario is that the target record is flagged as being in a sync error status and cannot be updated, and that information is written back to the source.

First, check if there is any data to be retrieved using a lightweight SFLookup, which will return only a single record. If nothing is returned, then don't start the chain of events. From a development standpoint, it can be helpful to take this approach instead of relying on the query in the SFDC query itself, since you can capture the ids in the query and inspect the records. Moreover, the SFDC query is only activated if it has something to do. In the query, it checks if Ready_to_Sync is true and also if a Sync_Error field is empty. The RTS field is set to true with internal triggers and will be modified by the 'write-back' process.

soql =
"SELECT Id FROM Account WHERE (Account_Status__c like '%Active%' or Account_Status__c like '%Terminated%' or Client_ID__c != null) AND NetSuite_Id_original__c != Null and Ready_to_Sync__c = true and Sync_Error__c = Null and (Sync_Error_Code__c < 1 or Sync_Error_Code__c = Null)";
result = SfLookup("<TAG>Salesforce Orgs/companyname(Sandbox)</TAG>",soql);
If(Length(result)>0,RunOperation("<TAG>Operations/03 SF->NS Update Customer/0301 Query SF Accounts</TAG>"))

Assuming at least one record is picked up, then 0301 is launched. The transformation validates a field and calls 0199 using a pattern similar to Processing Target Records Conditionally. It also checks the NetSuite target record for its Ready to Sync status. Note that RTS_Status was manually added to the file format generated by the wizard.

//Check if NS record's Ready to Sync Status. If false, can update. But if true, then have a sync error. Do not update and set source SFDC record's Sync Error to '1'
RunOperation("<TAG>Operations/03 SF->NS Update Customer/0302 Check NS Customer RTS Check</TAG>");
WriteToOperationLog("NSCheckResult: "+$NSCheckResult);
//Need to know in advance the RTS status. If have any with RTS Status = false, then process the NS Update. If all records have RTS status = true, then skip the NS Customer Update and only run the Account Update.
//Use $arrNsCheckResult to store the values, then can check array to see if any are false, in which case can run the NS Customer Update
Set($arrNSCheckResult,$NSCheckResult,-1);
$NSCheckResult

The 0302 operation is passed the NetSuite Id.

The response transformation allows the operation to evaluate the query result.

Note that a simple flat file structure is used.

WriteToOperationLog("Id: "+searchResponse$searchResult$recordList$record.Customer$internalId+" RTS val: "+searchResponse$searchResult$recordList$record.Customer$customFieldList$Ready_To_Sync$value$);

If(searchResponse$searchResult$recordList$record.Customer$customFieldList$Ready_To_Sync$value$==true,$NSCheckResult=true,$NSCheckResult=false)

In the next operation we run this check. 0304 is run to perform the update to NetSuite, and 0306 is run for error conditions.

// To handle edge case if all the NS records returned are RTS = true
RunOp=false;
WriteToOperationLog("arrNSCheckResult: "+$arrNSCheckResult);
count=Length($arrNSCheckResult);i=0;
While(i<count,
If($arrNSCheckResult[i]==0,RunOp=true);
i++);

If(RunOp,RunOperation("<TAG>Operations/03 SF->NS Update Customer/0304 Update NS Customer</TAG>"));
//Run this regardless
RunOperation("<TAG>Operations/03 SF->NS Update Customer/0306 Prep RTS Error</TAG>");

In 0306, we read the source target and filter out records that do not require processing.

0304 updates the NetSuite account and uses the response to build a file with the RTS and Error code information.

The condition always evaluates to true. The main purpose is to capture the SFDC id for use later in the transformation, and also to capture NS error messages if success is false. Conditions can be used this way to enable pre-processing of individual records.

$NSCustId="";
$errormessage="";
If(jbroot$jbresponse$updateListResponse$writeResponseList$writeResponse.status$isSuccess== true,
WriteToOperationLog("Success NS Id: "+FindByPos(SourceInstanceCount(),
jbroot$jbresponse$updateListResponse$writeResponseList$writeResponse#.baseRef$1$RecordRef$internalId
));
//If success run op to update SFDC record
$SFCustId = $CustIdList[SourceInstanceCount()-1];
$NSUpdateMessage = FindByPos(SourceInstanceCount(),
jbroot$jbresponse$updateListResponse$writeResponseList$writeResponse.status$statusDetail#.message$);
$NSCustId = FindByPos(SourceInstanceCount(),
jbroot$jbresponse$updateListResponse$writeResponseList$writeResponse#.baseRef$1$RecordRef$internalId
);
WriteToOperationLog("Success SFDC Id is :"+ $SFCustId);
WriteToOperationLog("Netsuite Update Message :"+ $NSUpdateMessage);
);

__

If(jbroot$jbresponse$updateListResponse$writeResponseList$writeResponse.status$isSuccess== false,
$errormessage="NS Data Error: "+GetEnvironmentName()+" "+
FindByPos(SourceInstanceCount(),
jbroot$jbresponse$updateListResponse$writeResponseList$writeResponse#.baseRef$1$RecordRef$internalId)+ " Message: "+
SumString(jbroot$jbresponse$updateListResponse$writeResponseList$writeResponse.status$statusDetail#.message$,".",false);
$SFCustId = $CustIdList[SourceInstanceCount()-1];
WriteToOperationLog($errormessage);
);

true

And finally 0305 updates SFDC. The requirement here was to do so using a Bulk Update for fastest processing. This is a partial screenshot: