IBM App Connect (Cast Iron) function to add/subtract minutes

Posted by jack on October 21, 2016

One common requirement in integration is to set threshold dates based on a configuration setting for an offset from an existing dateTime value – either the current dateTime or one read in data. Unlike other languages and tools, neither App Connect / Cast Iron nor JavaScript have convenient functions to for example subtract 10 minutes from a given dateTime. I am sure many JavaScript developers have their own functions to do this, but App Connect only supports a sub-set of the JavaScript language (IBM are a bit coy about this!) but on the other hand it does provide date format functions we can take advantage of to reduce the amount of code lines required.

Here then is my custom JavaScript function code to create such a function – this one works in minutes, but you could extend to seconds or even milliseconds if you require….

I call this function addMinutesToDateTime_yyyyDDDHHmm

App Connect developers (&others) will recognise the suffix as a format string for date time entries. I chose that format because it converts to/from a string with a fixed length, and avoids handling months – DDD in App connect means “The day number in the year expressed as 3 digits, with 001 being 1st Jan” . NOTE – HH is the 24 hour clock – do not use hh wich is 12 hour clock.

The inputs to the function are an integer (positive or negative)  that I named  minsToAdd_mayBeNegative, and a date time represented as a string called inputDateTime_yyyyDDDHHmm – as the name suggests, to convert an actual dateTime for input to the function use a standard Formal Date String function with the format entry….. well I think you know by now.

The output of the addMinutesToDateTime_yyyyDDDHHmm function then is passed to the Read Date String function – yes, with the same format string.

Here is the code within my custom function. Note I am not bothered about leap years, so possibly if you use over a period of more than 366 days you will be a day out…..if anyone wants to spend time elaborating to handle leap years, feel free.

var returnValue;
var outputYear =Number( inputDateTime_yyyyDDDHHmm.substr(0,4));
var outputDays =Number( inputDateTime_yyyyDDDHHmm.substr(4,3));
var outputHrs =Number( inputDateTime_yyyyDDDHHmm.substr(7,2));
var inputMins =Number( inputDateTime_yyyyDDDHHmm.substr(9,2));
var outputMins = inputMins + minsToAdd_mayBeNegative;
while(outputMins < 0){
outputMins =outputMins + 60;
outputHrs = outputHrs -1;
}
while(outputMins >=60){
outputMins =outputMins – 60;
outputHrs = outputHrs +1;
}
while(outputHrs<0){
outputHrs =outputHrs + 24;
outputDays = outputDays -1;
}
while(outputHrs>=24){
outputHrs =outputHrs – 24;
outputDays = outputDays +1;
}
while(outputDays<=0){
outputDays =outputDays +365;
outputYear = outputYear -1;
}
while(outputDays>366){
outputDays =outputDays -365;
outputYear = outputYear +1;
}
var outputDaysString=outputDays.toString();
while(outputDaysString.length <3){
outputDaysString = ‘0’ + outputDaysString;
}
var outputHrsString=outputHrs.toString();
while(outputHrsString.length <2){
outputHrsString = ‘0’ + outputHrsString;
}
var outputMinsString=outputMins.toString();
while(outputMinsString.length <2){
outputMinsString = ‘0’ + outputMinsString;
}

returnValue = outputYear + outputDaysString + +outputHrsString + outputMinsString;

return returnValue;

Comments Off on IBM App Connect (Cast Iron) function to add/subtract minutes
Categories: Cast Iron

Ensuring numeric input to Salesforce

Posted by jack on September 3, 2016

A useful javascript snippet I used in a Cast Iron custom function to check any string is a number string. This is useful where the input is a string format user input that might includes invalid characters and the Salesforce entry is either a numeric field type or has validation that causes non-integer input to be rejected.  The function returns a zero if the input is not a valid integer string, to avoid errors inputting a null into a number field in salesforce – but in other circumstances it could return an empty string as indicated.

var returnValue;
var regex = new RegExp(‘^[0-9]$’);
if(regex.test(inputString)==true)returnValue=inputString;
else returnValue=’0′;  //alternatively  else returnValue =”;

return returnValue;

Comments Off on Ensuring numeric input to Salesforce
Categories: Cast Iron,Salesforce

Setting test classes to handle disabled triggers

Posted by jack on September 3, 2016

Credit: http://stackoverflow.com/questions/10809867/salesforce-check-if-apextrigger-is-active-in-test-method

I discovered this as a result of a recent incident updating a client Org. The problem was due to the change set including a SOQL query that selected using an unindexed field – this worked fine in test scripts & sandboxes, but came up with an error in Production owing to the larger (than 20k) records involved in the selection.

Because the test method covering the class/method and trigger, making the trigger inactive in the sandbox and redeploying caused the test method to fail. Initially I wrote a different test method that covered just the class method, and commented out the original test method, but then i realised that if the test could detect whether the trigger was active, I could make the test class so that it works with trigger active or inactive like this:

@isTest
private class testApp {

   /* tests the appClass and object__c triggers */
   /* this method tests both class/method and trigger together if the trigger is active…… */
   public static testMethod void appClassMethodTriggerTest(){
      ApexTrigger appClassMethodTrigger =
                                [Select Id, Status from ApexTrigger where name=’appClassMethodTrigger’];
      // only run the test if the trigger is active…
      if(appClassMethodTrigger.Status == ‘Inactive’){
        return;
      }

 else {

      //<test code for active trigger here >

   }
   //…. other test methods…
}

 

 

 

 

Comments Off on Setting test classes to handle disabled triggers
Categories: Salesforce

Dynamic XPath filtering in Cast Iron

Posted by jack on March 7, 2016

If you want to select a sub-set of records from an XML variable with recurring child records using a static filter, then use an XPath filter  in a Map Variables activity

  • Right-Click on the root of the recurring node in the destination
  •  Enter the XPath predicate like variableName=’literal’

So much so straightforward – but what if you want to filter on a value you calculated earlier in the orchestration rather than a fixed value? Well, then you can reference a variable in the XPath predicate as

variableName=bpws:getVariableData(‘myFilterVariable’)

where myFilterVariable is the name of a primitive variable.

 

 

Comments Off on Dynamic XPath filtering in Cast Iron
Categories: Cast Iron

Repeatability & Reliability in Copying Data

Posted by jack on July 26, 2015

This post centres on T-SQL development but I apply similar principles in other environments – in Force.com Apex classes for example.

The principles can be summed up as :-

  • If the procedure is not run at the expected time/point in a process, then it can be run later without causing a problem
  • If a procedure is run repeatedly, the second and subsequent runs do not cause a problem.
  • Make as few assumptions as possible about the external environment

At this point I suspect most experienced developers will stop reading – if you do not start with these principles, you quickly learn them the hard way. For others, the following is an example of what I mean.

BUSINESS REQUIREMENT: In table SOURCE we have new rows arriving every day, with a single dateTime stamp dateLoaded recording their arrival. That column and the other attributes in the table are non-unique – ie we have a “heap” with no identifiable primary key. We need to copy these rows from there to an identically structured TARGET table. There are a lot of rows in both table, making a complete copy each update an inefficient process. Both SOURCE and TARGET table structures are a given and may not be amended.

The simplest T-SQL that might achieve this aim daily could be

INSERT INTO TARGET SELECT * FROM SOURCE WHERE dateLoaded > getDate()-1

[In T-SQL the getDate() function returns the current dateTime; T-SQL is not case-sensitive] 

Using this would be a bad idea for these reasons:-

  1. If you ran it twice in succession, it would load the same set of records into TARGET again.
  2. If you missed running it one day, it would not recover – the missed records would remain missed for all time, or at least until the TARGET is re-loaded from scratch
  3. If the server clock is adjusted or the job server delays or brings forward a job, then missed/duplicated rows might arise without notice.
  4. Using * in the query to represent all columns will work but is bad practice – if the columns are later amended in either or both tables, or are in a different order in the two tables, then either the query will fail or (worse) could map incorrectly between tables. for the sake of brevity the remainder of this post uses COLUMNLIST but always substitute the actual column names.

Here is an improved code that could overcome these objections:-

INSERT INTO TARGET(COLUMNLIST )

SELECT COLUMNLIST  FROM SOURCE WHERE dateLoaded > (SELECT MAX(dateLoaded) from SOURCE)

This revision will not load the same rows twice, and if it is not run for a while then the next time it runs it will catch up. The drawback is that the sub-query  (SELECT MAX(dateLoaded) from SOURCE) will slow up operation – especially if there is no index on dateLoaded in the two tables.  Some options present themselves here

  • If permitted, add indexes to the dateLoaded columns on the two tables
  • Retain a relative age limit on the oldest date you expect to include
  • Create a table to retain the last time the job was run and reference that in place of the sub-query

To expand on the second of these – amending the query by adding a final clause AND dateLoaded > getDate()-5 will not invalidate the process provided you are 100% sure that within 4 days you will pick up any issues and get them fixed.

Better, provided you have the ability is to retain a dateTime entry (simplest for our example is in a control table in the  SQL database  but it can be another type of persistent storage).

EG a single row table SYNCHLOG with a single dateTime column lastUpdate

UPDATE SYNCHLOG SET lastUpdate = (SELECT MAX(dateLoaded) FROM TARGET)

INSERT INTO TARGET(COLUMNLIST )

SELECT COLUMNLIST  FROM SOURCE WHERE dateLoaded > (SELECT MAX(dateLoaded) from SYNCHLOG)

UPDATE SYNCHLOG SET lastUpdate = (SELECT MAX(dateLoaded) FROM TARGET)

Notice here I have included what might be considered some redundancy – if at the END of this run we are setting the value of SynchLog.lastUpdate why do I need to repeat the process on the next run? My reason is – I cannot be 100% sure that in between runs the value in my control table is not amended by some other process – so just to be sure I am setting it again before the data load.

In practice I would create and drop a temporary table for this purpose – unless I wanted to elevate the control table to be a permanent log table instead – for example holding a record of the number of rows added each run.

CHECKING YOUR WORK

I would also consider putting in a check to verify all is working as expected – for example, in this case we might do this check

DECLARE @countTargetRecords bigint;

DECLARE @countSourceRecords bigint;

DECLARE @timeCutoff dateTime;

[HERE RUN THE LOAD PROCESS….]

SELECT @timeCutoff = Max(dateLoaded)  FROM target

SELECT @countTargetRecords = COUNT(*)  FROM target

SELECT @countSourceRecords = COUNT(*)  FROM source WHERE dateLoaded <= @timeCutoff

IF  @countSourceRecords <> @countTargetRecords

BEGIN

[…. raise the alarm….. ]

END

Notice that I included a time cutoff – this is to cover the situation where the SOURCE table is continuously getting new records – without a cutoff the alarm might be raised incorrectly due to these new records.

Comments Off on Repeatability & Reliability in Copying Data

Things to do with the Map Variables Activity

Posted by jack on July 12, 2015

The map Variables activity superficially looks like the simplest of all Cast Iron activities – there are no end-points to connect or templates to select, just inputs and outputs. Yet it is one of the most versatile of Cast Iron activities. Here are a few things I use this activity for

  • Filtering using XPath
  • Counting records in an XML variable
  • Accumulating rows in an output variable when they meet specified criteria
  • Converting a ragged hierarchy to a flat record (for example from a cross-object salesforce query)
  • Merging values from one variable into matched values of another (payload) variable.

Filtering Using XPath like other Cast Iron features, is not immediately obvious in the Studio user interface – furthermore there are important limitations in how it works.

To use XPath filtering, right-click on the root of the recurring element to filter into and select Filter Recurring Nodes from the pop-up menu. The XPath Predicate is a statement like Status = ‘Cast Iron to Convert’  – only records meeting this criteria will be mapped. In this case, Status is a string value – XPath also works fine for Booleans  – eg boolEntry = true()  , You can also use for numbers eg numberValue = 0.5  – however, take care if you have nulls when using XPath filtering, particularly of dates and numbers.

Cast Iron XPath filtering does NOT work with data as formatted out of a Salesforce Query – the structure of the output variable with multiple levels including object, is not catered for in the Map Variable XPath filter, which basically only handles simple XML variables with a flat single level data structure. To filter data extracted from Salesforce therefore you must map the data to such a variable first – I create my XSD using XML Spy Pro but you can use any tool that suits – just make sure that the resulting variable has one level and does not include the object level. Here is an example of such an .xsd:

<?xml version=”1.0″ encoding=”UTF-8″?>
<xsd:schema xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>
<xsd:element name=”users” type=”user”/>
<xsd:complexType name=”user”>
<xsd:sequence>
<xsd:element name=”rows” type=”row” minOccurs=”0″ maxOccurs=”unbounded”/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name=”row”>
<xsd:sequence>
<xsd:element name=”userName” type=”xsd:string”/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>

You can filter from one input variable to multiple output variables in one Map Variables activity, using a different XPath extression for each – but if you do this do not filter from the variable to itself but use entirely new variables on the output.

Accumulating rows in an output variable when they meet specified criteria Sometimes I have to go through this process

  1. Pick up some records
  2. Check & classify each one
  3. Accumulate into groups of similar records for further processing

Accumulating records uses the map variables activity. Both recordToAdd and similarRecords are input variables in this case, with just similarRecords as the output. To add in recordToAdd, before mapping  SimilarRecords, right-click on the root of the recurring element in the output, and select Expand Occurrences – them map the input similarRecords  to the many records element of the output, and recordToAdd to the single additional occurrence.

You must ensure that  similarRecords is initialised when used in this way to accumulate records.

Merging values from one variable into matched values of another (payload) variable – an approach I worked out to avoid having to write XSLT  templates all the time – it is much quicker to code, easier for others to understand and does not seem to impose much of a performance hit.

Here is a typical problem. I have updated versions of records coming into the data warehouse and I want to update the existing values in Salesforce to match, but have not set the record identifier as an external key in Salesforce. Of course, it would be much easier if I had set that key, but there are circumstances where that is not possible. So – what I want to do is

  1. Query the data warehouse to extract the up-to-date records
  2. Query Salesforce to get existing matched values
  3. Match the 2 sets together, putting the salesforce Id into the matched up-to-date records so the7y can be used to update Salesforce
  4. Identify records not already in Salesforce, and insert those.

One approach is to use 2 for each loops for each up-to-date record (loop1), find the matching  salesforce record (loop2) – fine if you have 10 records (max 100 loops), not so good when you have 25,000 (max 625 million loops) – you might wait a long time….

My solution uses one loop, one custom javascript function and one map variables activity to do the same  job. The variable with the values to merge in (in our example the Salesforce records) is looped, and the payload variable amended to merge in the required values (in our example the Salesforce Ids)

The javascript custom function mergeMatchedValue has these input variables – payloadKey, payloadValue, matchKey, matchValue. The javascript code in the function is

var returnValue;

returnValue=payloadValue;

if(payloadKey == matchKey) returnValue = matchValue;

return returnValue;

The for each loop variable salesforceRecord and the payload variable payload form the input to a map variables activity, and payload forms the output. Drag to link the root of payload, then un-map the mapping for the value for (salesforce) Id.

Now pull the mergeMatchedValue function into the activity, and link the business key and (salesforce) Id from the payload variable to function inputs payloadKey and payloadValue. Then map the business key and (salesforce) Id from the loop variable salesforceRecord to the function inputs matchKey and matchValue. Map the output from the function to the output field (salesforce) Id in Payload.

Voila! when run the salesforce Ids get merged into the payload variable. You can then use map Variables activity with XPATH filtering downstream of the loop to filter the Payload into two new variables payloadWithExistingSalesforceRecords and payloadNoExistingSalesforceRecords

The equivalent XSLT template runs to about 80 lines of not easy to understand code.

Comments Off on Things to do with the Map Variables Activity
Categories: Cast Iron

Polling in the Cloud and reliability

Posted by jack on July 11, 2015

Salesforce is a very reliable SAAS application, but that is not the case for all cloud based software – and even Salesforce is subject to occasional non-availability or time-out failures. so when designing integration allowance must be made for unexpected events and failures.

Salesforce applies API activity limits, so I want to avoid running queries too frequently, and yet still enable users to get their records updated from the data warehouse via Cast Iron in close to real time. An example I just completed involved the user inputting a reference number that creates a stub record, and the integration looking in the data warehouse for a matching record and updating the stub record with the actual values. It is possible for the user to input an invalid value (in which case a matching record is never found) or to input a reference that has not got into the date warehouse yet due to processing delays elsewhere.

The obvious approach to processing the user input is to use polling – so that a change in Salesforce is picked up at a short polling interval. This is fine provided service availability for all the components ( Salesforce, Cast Iron and the client’s data warehouse) are 100% available whenever users make the triggering change in Salesforce, and the matching record does already exist in the data warehouse  – but in practice that may not be the case, and we do have failures – one way to pick up the missed synchronisations is using a scheduled query as a backup.

So I generally couple a polling triggered orchestration with a twin “tickle” orchestration – all this does is look for records that  remain in the state that should have triggered the processing, and update them without changing any business values to cause the polling to pick them up again – that way I avoid having to replicate the business logic in two orchestrations.

Of course, to make this work  requires that the standard processing changes the processed record in a way we can detect, and also that there are some limits – generally I use 2 dateTime entries in salesforce to limit the records to reprocess 1. A “most recently last tried” limit to avoid retrying every unprocessed record  on every schedule – and 2. An “oldest record to process” limit to eliminate older records that have never processed correctly so we do eventually give up on them.

 

 

 

 

Comments Off on Polling in the Cloud and reliability
Categories: Cast Iron,Salesforce

Cast Iron – returning SQL query results that match multiple key values

Posted by jack on July 7, 2015

This post concerns an annoying limitation of the Database Query activities when compared to the Salesforce equivalent Query Objects – namely that the latter allows the input of a multi-row selection parameter using a SOQL IN clause, whilst the Database Query SQL activity does not. This is particularly obvious when (as in my case) your task is to take a bunch of Salesforce records and check them against the database for updates.

Other Cast Iron users refer to the data Quality  Lookup activity for a work-round, but I have a better approach using Stored procedures. That solution is to have a stored procedure that accepts the input as a comma separated list of values in single string, then internally converts this input to the key values to match in the query, and use a database Call Procedure activity to collect the matching rows.

To created the input parameter string for the Call Procedure input I used a For Each loop upstream, accumulating the key values from the Salesforce data (in my case I actually used a SPLIT of that data to handle larger data volumes). Remember to remove the initial comma (or ending one, depending how you build) from the string after the for each loop.

Derivation of a suitable stored procedure is documented at http://www.sommarskog.se/arrays-in-sql-2005.html#XML  – this is specific to Microsoft SQL Server 2005 (which is what I am using) – for 2000 there is a (slower, worse) alternative and for later versions more elegant and quicker options. No doubt there are similar approaches for Oracle & DB2.

Since I already have a view used in an earlier Cast Iron implementation where each individual salesforce record was matched using a For Each loop to one records each time (ie very slow), all I had to do was include that view in my stored procedure and return the matched rows (see this post on use of Copy Of function to get the data from a stored procedure)

The net result of this is now 2 datasets for comparison:

  • The Salesforce Objects that require checking for update
  • The matching database values to use for that check and provide updates.

My next post will concern approaches to the comparison of two datasets in Cast Iron; as usual, I will avoid the use of more than simple javascript functions in the solution, making the most of built in Cast Iron capabilities.

 

 

 

 

 

Comments Off on Cast Iron – returning SQL query results that match multiple key values
Categories: Cast Iron,SQL Server

Cast Iron – using Database Call Procedure activity to collect rows

Posted by jack on June 23, 2015

I was going to write a piece about this but found this excellent IBM  DeveloperWorks guide so won’t bother….. just be aware you are unlikely to be able to work it out just by tinkering!

My comments

  • If you want to avoid a hard time with XPath filtering, map to a simple single-level variable downstream.
  • I use Altova XML Spy for all XSD design work – there are other brands, but using a simple text editor might not be the best use of your time.

 

Comments Off on Cast Iron – using Database Call Procedure activity to collect rows
Categories: Cast Iron

Accessing SOQL Aggregated Data in Cast Iron orchestration

Posted by jack on June 23, 2015

Salesforce SOQL aggregate queries allow you to summarise across objects very simply using syntax like:

Select COUNT(Id) contactCount, MAX(lastModifiedDate) lastModifiedDateContact,

Account.BillingCity AccountCity

FROM Contact GROUP BY Account.BillingCity  HAVING COUNT(Id) > 5

In Cast Iron Studio v7.0.0.2 using this query in a Salesforce Query Objects activity will return results as an AggregateResult type object – but will not let this be mapped to anything else……

Investigating this issue I discovered two blocks in this type of query

  1. Using aliases in the SOQL query
  2. Using fields from multiple Salesforce objects

Removing the aliases is simple:-

Select COUNT(Id) ,MAX(lastModifiedDate) , Account.BillingCity

FROM Contact GROUP BY Account.BillingCity HAVING COUNT(Id) > 5

Now the output has aggregated data elements named expr0, expr1, expr2…. and these will map – but Account.BillingCity will not. To handle this, I put a calculated contact field Account_City__c = Account.BillingCity in the Salesforce Org, then revise the query to be

Select COUNT(Id) ,MAX(lastModifiedDate) , MAX(Account_City__c)

FROM Contact GROUP BY AccountId HAVING COUNT(Id) > 5

Now all the required elements from the aggregate query map fine…..I can use a single tier XSD with these entries (mapped values in brackets) to collect the output.

  • contactCount (expr0)
  • lastContactModDate (expr1)
  • accountCity (expr2)

As a general point, Cast Iron does not handle multi-level xml schemas well, so whenever possible I decompose to a flat structure and then all the XPath filtering and other built-in features of Cast Iron work as expected.

Comments Off on Accessing SOQL Aggregated Data in Cast Iron orchestration
Categories: Cast Iron