Wednesday, April 8, 2015

Dynamics GP Integration Manager script to generate a fixed length number

By Steve Endow

There are some situations when importing data into GP where you want to have a consistent or fixed length to a number.

Say you are importing Employees, and your source data file has numeric employee IDs such as 123 or 2345.  But suppose that you want all employee IDs to be a consistent 5 digits, with leading zeroes, in Dynamics GP.  So instead of 123, you want 00123, and instead of 2345, you want 02345.

Here is an Integration Manager field script to "pad" the Employee ID with leading zeroes.  It uses the VB Script Right function, which lets you easily pad a field value with leading zeroes.

CurrentField = Right("00000" & SourceFields("SourceName.EmployeeID"), 5)

This could also be used if you want to prefix an alpha value at the beginning of a document number.

So if you were importing invoices, and the data file had invoice 4567, and you wanted "INV00004567", you could do something like this:

CurrentField = "INV" & Right("00000000" & SourceFields("SourceName.DocNum"), 8)

That produces a consistent 8 digit invoice number, to which you can prefix "INV".

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

No comments: