abupp137129

Preventing Duplicate Doc Cache Entries

Discussion created by abupp137129 on Dec 27, 2017
Latest reply on Mar 19, 2018 by abupp137129

I had the problem of figuring out how to create a doc cache that did not include duplicate entries.  In my case, the source for the cache is a record set coming from an Oracle database query.  The data returned from Oracle has an identifier which had been previously configured in Salesforce as an external identifier.  In order to used to update a target record in Salesforce, this value has to be used to lookup the corresponding Salesforce internal identifier value.

 

The purpose of the cache was to hold the set of lookup values for all the given external identifiers that come into the process from the initial database query, in order to avoid having to do a connector lookup for each record in the map shape.

 

The initial database query selects records based on a time-dependent clause, e.g.:

 

IB_ASSETS_RPT.LAST_UPDATE_DATE > (SYSDATE - 3) 

 

which gets all records which have a LAST_UPDATE_DATE that's within the last 3 days.

 

The data set returned from this query can contain any number of records, which all have a value in the external identifier field that I wanted to use as key for the cache lookup.  In order to build the cache, I wanted to construct a comma delimited list of these external IDs to pass to a second query of Salesforce.  However, often the initial data set contains records which share a given value, so simply using a message shape like this constructed a list with duplicate values:

 

 

To resolve this problem, before the Data Process shape, I added a Set Properties shape, where I set a Dynamic Document Property to the value of the profile element containing the external ID:

 

 

Then I changed Message shape to a Data Process shape which employs custom scripting to build a external ID list that contains only unique values, getting the external ID from the Dynamic Document Property, and saving the list to a Process Property.  Here's the code:

 

import com.boomi.execution.ExecutionUtil;
import java.util.logging.Logger;
import java.util.Properties;
import java.io.InputStream;

 

Logger logger = ExecutionUtil.getBaseLogger()

 

// This code creates a comma-delimited list that
// does not include duplicates

 

def componentId = "709e3e60-8c29-4c64-99c3-614074444c21"
def propKey = "223a4644-a264-4d76-a401-883cea00efa1"
def strList = ExecutionUtil.getProcessProperty(componentId, propKey)
def list = []
if (strList) {
   list = strList.toList()
}

 

InputStream is = null
Properties props = null
for( int i = 0; i < dataContext.getDataCount(); i++ ) {
   is = dataContext.getStream(i)
   props = dataContext.getProperties(i)

   def value = props.getProperty("document.dynamic.userdefined.RBC_ITEM_NUM_DDP")
   if (value ) {
      if (!list.contains(value)) {
         list.add(value)
         // logger.info(value + " added to list")
      }
   }

}

 

// build string list
list.each {
   strList += "${it},"
}

 

// remove trailing comma

strList = strList.substring(0, strList.length()-1)


// logger.info("strList: " + strList)

 

ExecutionUtil.setProcessProperty(componentId, propKey, strList)
dataContext.storeStream(is, props)

 

The resulting comma-delimited string (which could also have been saved to CurrentData in the custom script) can then passed as a parameter to the Salesforce query operation's ProductCode IN filter:

 

 

There maybe an easier way to achieve this, but coming from a Java/Groovy programming background, this seemed like a natural approach.

 

Cheers,

Albert

Outcomes