I used to conduct a presentation at BDO’s annual user conference that promoted and encouraged the use of customization tools. With the mantra of “all businesses are unique so ERP solutions should be too…” the presentation developed an almost cult-like following. Although I’ve since been told that three people do not construe a particularly impressive cult, I thought it would be useful to extend this message to the world of integrations. Over the next few articles I will discuss a number of strategies which allow integrations to be tailored to meet specific and unique business requirements. Today, the spotlight will be on the use of calculated fields in SmartConnect.
Users familiar with SmartConnect will recognize Microsoft’s eConnect as the underlying technology. With eConnect, a level of assurance is achieved by ensuring that data meets a specific set of validation criteria. With the basics covered, we can be certain that our debits will always equal our credits and an invoice will never be created without a valid customer. But didn’t the opening blurb say something about all businesses being unique? What if that uniqueness specifies that customer invoices can only be created on partly cloudy days when the temperature is no more than 20 degrees? The general populous may scratch their heads over this one but, in someone’s world, it makes perfect sense.
As a developer, the appeal of SmartConnect includes the ability to derive the values of integration data through the use of scripts written in VB.NET (or that other “sharp” language for those so inclined). Assume there is a requirement to integrate a general ledger distribution with the hitch that its value cannot exceed a specific amount. If such a value is encountered it is communicated as an error.
On the SmartConnect Mapping window, select Additional Columns and create a new Calculated column. This column will serve satisfy the objectives of populating the required destination column and performing a validation on the source data.
Now, the fun part begins. The calculated column will contain a few lines of VB.NET code to compare the value of a source column to a designated upper limit. If it exceeds the limit a message is returned and the record is not integrated.
The magic behind this code comes from VB.NET’s use of structured exception handing (try, throw, and catch).If the validation is successful, the calculated column will return the value to be integrated. If the source column is found to be in violation of the defined business rule, an instance of the System.Exception object is created and thrown with a specific error message.
The catch block is used to perform any required processing in the event the validation failed. Examples of additional logic which may be included here include writing to an external log or the Windows event log. The simplest solution is to re-throw the exception as shown below. SmartConnect will consequently ignore the record.
In addition to ignoring the record, the SmartConnect Progress window will indicate that an error occurred. Processing of other records will continue.
The message associated with this error is – surprise – the one which we defined in the calculated column.
In the next article I will delve into the use of eConnect Pre and Post procedures to implement customized validation and data manipulation. Stay tuned!