Don't be so Sensitive! (or Dealing with Case Sensitivity when Populating the Profiles Database)

Prior to reading this post you might want to take a quick read through my previous post Lotus Connections Profiles Population Wizard, What is Really Going On Under the Covers, which provides a more in depth understanding of the database population process, this post will cover using a custom Javascript function to manipulate data when populating the Profiles Database.  In this example I will cover issues around Country Code, but this example can easily be extended to any other field in the Profiles Database. One of the fields in a users Profile is their Country, a useful piece of information for a large organization with different locations around the world.  First lets understand how the Country data is expected to display in a profile.  Each user record contains a field “PROF_ISO_COUNTRY_CODE” and expects to be populated with a 2 letter value (Connections provides the ISO Standard Country codes with the Database Population Wizard, and that is what I will use in this example).  When running the Profiles Population Wizard you have the opportunity  to load the contents of “isocc.csv” which populates the standard country codes and the country names in to the “Country” table in the Profiles Database. The default configuration for Profiles is to display the full country name when rendering a users profile (not simply the two letter ISO code), This is the line in the profiles-config.xml “<attribute appendHtml=”&lt;br/&gt;” showLabel=”false”>countryDisplayValue</attribute>” When a profile is loaded the system takes the 2 letter ISO code stored in “PROF_ISO_COUNTRY_CODE” in the EMPLOYEE table, and retrieves the full country name from the COUNTRY table, sounds simple, until you realize that it is all case sensitive.  As you can see here all of the country codes are loaded in lower case country-table Now in my example I am feeding Profiles completely out of a Domino LDAP which is completely under my control as it is my sandbox, but in the real world you might be taking data in from other sources, an HR system for example where you can not dictate how they store data, so we need a more elegant solution then simply making sure the source data is in lower case.  So in our example here is a view of a users ldap record (using LdapBrowser a very handy utility to keep around, scroll down on the page for the free client) ldapbrowser As you can see here the “c” attribute is populated with US in uppercase If we simply use the default mapping in the Database Population Wizard wizard-default The user table will be populated in uppercase as seen here keayon-table-US Which means when we go in to Profiles and view Alex P. Keaton’s Profile, we will not see his country displayed since US does not exist in the Country table (us is there but not US) alex-nocountry Not the result we were looking for.  To fix this we are going to add a function in to ‘profiles_functions.js’ which is in the TDI Solutions Directory (TDIPopulationTDISOL under the database Wizards directory) and here is the function I am adding (and bear in mind I am NOT a developer) // Convert country field to lower case function function_map_country_lowercase(fieldname) {         var result = work.getString(“c”);         if(result != null) {                 result=result.toLowerCase();                 }         return result; } Once you have saved your function, you can either manually edit the “” in the TDI Solutions Directory so it looks like this PROF_ISO_COUNTRY_CODE={function_map_country_lowercase} Or run the Wizard again and use the Function Pick list where you will see your new function as seen here function list Once the wizard completes even though our source is still providing US in upper case we are writing us in lower case to the database keaton-table-us Now when we load up Alex P. Keaton’s Profile we have his country displayed as we expected alex-country This is just one example, using a custom Javascript you can easily manipulate any source data to write it out to the Profiles database correctly to suit your needs. Additional Resources Using the Profiles population wizard Mapping fields manually Tivoli Directory Integrator V6.1 InfoCenter IBM Tivoli Directory Integrator Users Group

One Response to Don&apos;t be so Sensitive! (or Dealing with Case Sensitivity when Populating the Profiles Database)
  1. Chris Whisonant
    June 17, 2009 | 11:03 am

    Good stuff Mitch! I though AKP was the CEO, though? Emoticon