SmartConnect On-premise utilizes .NET scripting for creating data transformations that are more complex than can be used for the Translation Table, Date Translation or Rolling Column additional columns. However, while it is not capable of creating functions or methods to be used between integrations, calculated fields can be referenced and used within the same map. I will show a few examples of where we can reuse calculated columns.

In this example, we will create a calculated field to parse a date value within a script and use it for the Document Date on a payables map. We will also use that same calculation to define the batch number but instead of writing the logic again, we’ll just reference our original calculation.

Here is a quick reference to our final mapping showing the DOCUMENT_DATE calculation and the BATCH_NUMBER_CALC calculation.

 

Calculated Field

First, we’ll create a calculated field that gets the date value from a source column. In this case, my source data is as follows. The document date is in a format that is not able to be translated easily by SQL since there is only a single digit for the month and day. We need to create a calculated field to get this column into a date format that can be used by our destination, in this example, Dynamics GP.

 

In the destination mapping, click on Additional Columns->Calculated

Name the Calculated Column DOCUMENT_DATE_CALC

We will parse the date string to create and return a formatted date to be mapped to the Document Date field in our destination.

 

if _DOCUMENTDATE.Length = 4 then

‘Assume one digit month, one digit day, two digit year

Month = substring(_DOCUMENTDATE,0,1)

Day = substring(_DOCUMENTDATE,1,1)

Year = substring(_DOCUMENTDATE,2,2)

else if _DOCUMENTDATE.Length = 5 then

‘ Assume either one digit month, two digit day and two digit year or

‘ two digit month, one digit day and two digit year

Month = substring(_DOCUMENTDATE,0,2)

if Int.Parse(Month) > 12 then

Month = substring(_DOCUMENTDATE,0,1)

Day = substring(DOCUMENTDATE,1,2)

else

Day = substring(_DOCUMENTDATE,2,1)

end if

Year = substring(_DOCUMENTDATE,3,2)

 

else if _DOCUMENTDATE.Length = 6 then

‘Assume two digit month, two digit day and two digit year

Month = substring(_DOCUMENTDATE,0,2)

Day = substring(_DOCUMENTDATE,2,2)

Year = substring(_DOCUMENTDATE,4,2)

end if

 

return new DateTime(Year,Month,Day)

 

Batch Number Calculation

For our Batch Number, we want to use the Document Date value with a string prefix so we need to concatenate the prefix to the date.

Instead of duplicating the code above, we can just call the document date calculation we just created.

The only way to reference a calculated column in script is to make sure the referenced calculation is created first, as we have done.

View Additional Columns

To see if an additional column would be available to be used in a Calculation, you can expand the Additional Columns node to view them.

Either type the displayed column or click and drag it into the Calculation pane.

Reorder Additional Columns

If the columns are not in the correct order, meaning you don’t see the additional column, you can change the order by highlighting the additional column and moving it to the top of the list, bottom of the list, previous or next.

 

Other Uses for Referencing Additional Column

You can reference Additional Columns within other additional columns that are not necessarily Calculated Columns at the destination mapping level.

Restriction

You can reference any map Calculated columns in a node restriction as well. You can see those calculations available to be used in our restriction script by expanding the Calculated Fields node

Dynamics GP Rolling Column

You can use a Calculated Column as the Document Type or Document Id in a GP Rolling Column

MSSQL Lookup

You can use a Calculated Column or Translation Table or Date Calculation Column as the Source for an MSSQL Lookup when comparing it to the target field.

Date Calculation

You can use a Calculated Column or Translation Table as the Source for a Date Calculation Column

Entity Lookups

You can use other additional columns for comparison within Entity Lookups/Page Lookups for integrating to Dynamics BC, Dynamics NAV, Dynamics BC and Salesforce.com.

Additional Columns in Tasks

You are not able to reuse Additional Columns at the Task level. Those items are executed within a different context, so the Additional columns are not available to be referenced.

If you find you are writing the same pieces of .NET logic multiple time or need similar logic in multiple maps, consider using script templates for quicker implementation of the duplicate logic.

https://www.eonesolutions.com/tech-tuesday-creating-and-using-smartconnect-script-templates/