In this post we’re going to explore how you might go about tackling a typical mass data maintenance problem using one of our most popular applications: Vince Excel (VXL).

In Norway, postal codes change on a yearly basis as politicians redraw community borders. The change report is released in October of each year, and businesses need to be on top of address updates to ensure packages and letters make it to the right destination. Failing to make these updates can be a costly mistake, with carriers charging fees for incorrect deliveries, and customers unhappy with the delay in receiving their items or letters.
In this article, we’re going to explore how to tackle this problem simply and efficiently with VXL.

Download new postal codes

In Norway, this is done at Bring.no in the form of a CSV file.

Vince Excel postal codes

Import all postal codes to Excel

Next, we must import the raw data into Excel using the import function (note, we’ll be creating sheets 2-5 later on in the process):

Excel sheet

Invalid and valid postal codes sheet

In the second sheet (titled postal codes), we list out all possible Norwegian postal codes (valid and invalid) from 1 to 9999. By comparing this list with the data we have from the CSV, we can create a list of invalid postal codes. We can then import the invalid postal codes directly to CUGEX3 (check out this article for how to use CUGEX3 and other custom tables in Infor M3(M3)) with VXL to validate future user updates to postal codes.
But we also want to include all possible postal codes and past valid postal codes so that we can validate existing addresses in the system – that’s what we’ll do next.

Note: for the ‘codes’ sheet, I added a legend that includes simpler definitions of some basic codes.
NORMAL postal codes are valid, the others are not:

Excel sheet

Next, we need to prepare the data for import to M3. Scroll down to see the Microsoft Excel functions I used for each of the columns in the image below.

Excel sheet

Column A Static: POSTNR

Column B Static numbers from 0001 – 9999

Column C Function to get data from sheet: “Import postalcodes”

=HVISFEIL(FINN.RAD(Postalcodes!B2;’Import postalcodes’!A:E;2;USANN);
“Ugyldig postnummer”)

Column D Boolean interpretation of column C, valid postal number or not:
=HVIS(C2=”Ugyldig postnummer”;”false”;”true”)

Column E Human readable of code in column F: =HVISFEIL(FINN.RAD(F2;Codes!A:B;2;USANN);”UNKNOWN”)

Column F Also, data from sheet: “Import postalcodes”

Column I We will use this column to populate sheet “VXL execution”. Excel functions

Column J =HVIS(OG(TALLVERDI(L10) <= TALLVERDI(K11);E11 = “NORMAL”);L10;””)

Column K =HVIS((E11 = “NORMAL”);B10;B11)

Advanced filtering to our VXL execution sheet

The VXL execution sheet is the sheet we will eventually import using our VXL Expand function, which will allow us to lookup all incorrect customer addresses in Infor M3 (M3). For the filter we will use column I, titled ‘Filter’. If necessary, we can reduce the input range to M3 to a single city (AL30).

Vince excel sheet

Select advanced filter in sheet: “VXL execution”

Vince Excel sheet

Define the correct input and click OK:

Excel sheet

Voila! We now have exactly what we want – all invalid delivery postal codes from our sheet: “Postalcodes”. Column M is populated automatically by a pre-defined Excel function:

Excel sheet

An introduction to my Infor M3 setup and Vince Excel function

Next, we need to build the API that VXL needs to make the import to M3.

We will drive our output list API of Customer Addresses (OCUSAD) with a custom API defined in CMS010/CMS015 and executed in CMS100MI (for more information on custom APIs, check out this article series).
Unfortunately, the OCUSAD master table does not support our sorting order as standard. Instead, we can use Infor Enterprise Search (IES). IES will combine its search result with the standard index of OCUSAD and return that to VXL via API. It is a quick (if search indexed), agile, and user-friendly solution, especially when compared to the overhead of creating a new database index, which will need approval from the IT department as it would impact the performance of the whole system.

The one disadvantage of IES, however, is that the API caller will need to first prepare the input into a single string. Luckily, we already did this – see the IES syntax in Excel sheet column M above.
Note: Infor Enterprise Search must be installed and configured correctly in the first place for this to function correctly. Contact me [author] for consulting on this – I have a lot of experience!

Setup of our function in VPM.

vince Excel
Vince Excel function
Vince Excel

Run the Vince Excel function on the Excel template

After running the VXL client, all customer delivery addresses with invalid postal codes will be outputted. We see that many of these delivery addresses have the postal code 0915, which is a valid postal code, but only a postal box address, meaning it can only to be used to send letters, and not for the delivery of goods.

Vince function
Vince Excel excel sheet

Correct adresses and uploade the changes to M3

After correcting the addresses in Microsoft Excel, we can import them back into M3 with another VXL function using a standard M3 API transaction in CRS610MI.

Conclusion

We’ve seen how to harness the power of enterprise search in retrieving M3 data, and how it can solve many potentially time-consuming master data problems simply and efficiently. You need only use a little creativity and spend a little time mastering VXL and Microsoft Excel. By doing so, you can solve all sorts of problems for your organization!

 

If you have any questions feel free to contact us here.

 

Download content as .PDF

An applied use of Vince Excel Correcting postal Codes

Share...