Solved

Postal Code validation

  • 17 December 2019
  • 4 replies
  • 539 views
L
Rank
Badge +2

We are getting ready for an IFS 10 go-live in October of 2020.  I’m interested in learning what others have done to facilitate Postal Code and Address validation in IFS. 

It looks as if IFS requires that States, Counties, Cities, and Countries be set up before Postal Codes can be created.  Is this data entry typically done manually?  Is there an import capability for this relatively static data?  Does IFS have any APIs that facilitate address validation?  Are there recommended 3rd party serices from which to obtain US and international state/county/city/country/zipcode data to import?

Thank you for any insight you can provide in how best to approach this task.

icon

Best answer by alanbclark 19 December 2019, 00:12

View original

4 replies

A
Rank
Userlevel 7

Hi, 

you could use the migration tool to import post codes. As for validation your best bet would be a custom LU holding postal codes and state combos and an event validating against that on save. We don't have any standard APIs for it, but you sure could integrate with some 3rd party software if you want to go that route. 

durette
Rank
Userlevel 7
Badge +18

1. Start with a text file "in.csv" with your addresses separated by a line break. The allowable format is pretty loose. (Think of what a Google Maps query allows.)

2. Sign up for the Geocoding API through the Google Maps APIs on Google Cloud. You'll need your own API key for billing purposes.

3. Use this Python 3 script:


import sys

import urllib.request

import urllib.response

import xml.etree.ElementTree as ET

fo = open("out.csv", "w")

apikey = 'AI..you need to edit this with your own key...K-o'

urlbase = "https://maps.googleapis.com/maps/api/geocode/xml?key=" + apikey + "&address="

with open('in.csv') as fi:

    for line in fi:

        argraw = line.replace('\r', '')

        argraw = argraw.replace('\n', '')

        arg = argraw.replace(' ', '+')

        url = urlbase + arg

        request = urllib.request.Request(url)

        response = urllib.request.urlopen(request)

        htmlBytes = response.read()

        htmlStr = htmlBytes.decode("utf8")

        root = ET.fromstring(htmlStr)

        zip_code = ''

        for child in root.iter('address_component'):

            if child.find('type').text == 'postal_code':

                zip_code = child.find('long_name').text

        fo.write('"' + argraw + '","' + zip_code + '"\n');


4. You'll end up with a file "out.csv" containing the validated ZIP codes appended to your lines.

If you find this forum useful, please consider paying back to the community in your own areas of expertise. -- Kevin Durette - https://www.linkedin.com/in/kevindurette
alanbclark
Rank
Userlevel 5
Badge +7

I would suggest a series of migration jobs. The spreadsheets are easy to work with and the objects are fairly basic to migrate data into. IFS Apps 10 already has the objects created (Application Basic Setup → Enterprise → Basic Data

  1. Find data from a trusted source. This data does change. If you’re looking for free data you’ll need to decide how often you need to updated and if the free site you got it from provides updates.
  2. Countries and States should already be available.
  3. Create the migration tables.
  4. Add Counties to States
  5. Add Cities to Counties
  6. Add Zip Codes to Cities

If you would like help please send me a private message and I can provide some sample data to get you started populating this data into IFS.

-Alan
alanbclark
Rank
Userlevel 5
Badge +7

As an update to this question, I have recently updated a code set to validate US address using some custom packages, a couple tables, and Oracle UTL_HTTP calls to a services provided by YAddress (https://www.yaddress.net/). There is a very minimal charge to use their service (1 cent per address validation request).

 

Shown below is a sample query you can run. There are many possibilities of how you can use the available functions. Please review the web site and let me know if you are interested in using the code set. Note: You need to be running an Oracle database version that supports JSON data types and functions available. For my sample I am running 12c.

 

select a.*,
replace(trim(BOTH chr(10) from (trim(BOTH chr(13) from esi_yaddress_api.Get_Address_Validation(a.address1, a.address2, a.city, a.state, a.zip_code)))), chr(13) || chr(10), ', ') address_validation
from (select customer_id, address_id,
address1, address2, city, state, county, zip_code, country
from ifs2004.customer_info_address
where country = 'UNITED STATES'
order by customer_id, address_id
) a;

 

-Alan

Reply


Terms & ConditionsCookie settings