Infor CRM Contour Geocode mapping

Starting in Infor CRM 8.3.04 they added a feature called Contour which can utilize either Google or Bing mapping APIs to geocode the ADDRESS table in Infor CRM and then allow you to map addresses and find other close records. Scott Weber has a nice demo of all the features here.

I wanted to get into a couple of gothchas and technical details of what this all means.

In the ADDRESS table there are now some new database fields:

• GEOCODEPROVIDER
• GEOCODELONGITUDE
• GEOCODELATITUDE
• GEOCODEFAILED

When the job service “GeoCodes” addresses it performs the following logic.

First it uses the following underlying SQL query to find the ADDRESS records it attempts to process:

select * from sysdba.ADDRESS
WHERE 
(
    (                    
		(
			GEOCODEPROVIDER is null or 
			GEOCODELATITUDE is null
		) 
        or 
		GEOCODELONGITUDE is null
    ) 
	and 
    (
		GEOCODEFAILED is null or 
		GEOCODEFAILED = 'F'
	)
) 
and not (ENTITYID is null)

Essentially it looks for any records where one of three fields are blank: GEOCODEPROVIDER, GEOCODELATITUDE, or GEOCODELONGITUDE. In addition the GEOCODEFAILED must either be null of “F”. Finally the ADDRESS record has to have a value in the ENTITYID column.

Using this recordset it then gets in an attempts to work with things.

There are 2 outcomes when geocoding:
FAILURE

1. The GEOCODEFAILED column is set to T

SUCCESS

1. The GEOCODEFAILED column is set to F
2. The GEOCODEPROVIDER is set to “Bing” or “Google”.
3. The GEOCODELONGITUDE is set to found Longitude from the service.
4. The GEOCODELATITUDE is set to found Latitude from the service.

First it looks to see if the DESCRIPTION field is empty. If it is empty then it looks to see if this empty DESCRIPTION record is tied to a User record (The ENTITYID contains a USERID). If it does then it attempts to Geocode the address using the address service.

If the DESCRIPTION field is empty and it is not a user linked Address, then it skips this record altogether and does not attempt to geocode it. This is problematic because DESCRIPTION should not determine the validity of an address.

If the DESCRIPTION field is not empty, then it attempts to Geocode the address. It takes the component parts of the Address (Address1-Address6, City, State, Postalcode, Country/CountryCode) and if there is at least one data element for the address, it then passes this into the Geocode service API. If none of the attributes have a value then if FAILS the Geocoding without passing it to the service.

Both mapping APIs have threshold limits to how many addresses you can do daily. These limits depend on the API type purchased. If during processing, the job service detects you have reached the limit then the job exits.

In the compiled BusinessRules assembly, for the Address entity there is now also code in the OnBeforeUpdate event that if one of the following is changed:

• Address1
• Address2
• City
• State
• PostalCode
• Country
• CountryCode

Then the GEOCODEFAILED is set to F. It then immediately calls Geocoding on this address.

Note: that it does not include a check for just Address3-Address6. If one of those changed it does not set the GEOCODEFAILED=F, nor does it attempt to GeoCode the Address.