EONE BLOG

Tech Thursday: Referencing a SmartConnect Calculated Field


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.

VendorID

Document Date

Amount

DocumentNumber

DocumentType

 

ACETRAVE0001

5219

100

ACE0001

Invoice

 

 

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/

Leave a Comment




RECENT POSTS


US Memorial Day eOne Office Hours - May 27th, 2019
SmartConnect Bootcamp in St. Louis - Don't Miss It!
Directions North America 2019: We Loved It!
Tech Tuesday: Creating a Popdock endpoint to use with Power BI
SmartConnect Office Hours Recap for May 17th, 2019

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
New Releases of Extender and SmartList Builder
2017 SmartConnect Integration Bootcamps

CATEGORIES

ARCHIVES

open all | close all

TAGS

CRM Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News eOne Spotlight ERP Events Excel Excel Report Builder Extender Extender Enterprise Flexicoder GP GPUG integration integration manager Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Office Relationships partners Popdock release Reporting SalesForce SalesForce.com scripting SmartConnect SmartConnect.com SmartConnect Office Hours SmartList SmartList Builder SmartList Designer SmartView SQL Support Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.