Ingve Brunvoll
In Norway, postal codes change every year 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 will explore how to tackle this problem and efficiently with VXL.
Download new postal codes
In Norway, this is done at Bring.no in the form of a CSV file.
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):
Invalid and valid postal codes sheet
In the second sheet (titled postal codes), we list 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 to use CUGEX3 and other custom tables in Infor 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 to validate existing addresses in the system – that’s what we’ll do next.
Note: I added a legend that includes simpler definitions of some basic codes for the’ codes’ sheet.
NORMAL postal codes are valid. The others are not:
Next, we need to prepare the data for import to M3. Please scroll down to see the Microsoft Excel functions I used for each of the columns in the image below.
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 the 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 look up 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).
Select advanced filter in sheet: “VXL execution”
Define the correct input and click OK:
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:
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 compared to the overhead of creating a new database index, which will need approval from the IT department as it would impact the whole system’s performance.
However, IES’s one disadvantage is that the API caller will need first to 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. Contact me [author] for consulting on this – I have a lot of experience!
Setup of our function in VPM.
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, a valid postal code, but only a postal box address, meaning it can only be used to send letters and not for the delivery of goods.
Correct adresses and uploade the changes to M3
After correcting Microsoft Excel addresses, 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!
Download content as PDF
