[TUTORIAL] The OnDataChange Event Handler in Pervasive Map Designer

This tutorial forms a convenient identifier from a business key. The business key is taken from a spreadsheet of unique names. The convenient identifier is an alpha-numeric string of 6 characters which is capitalized and stripped of special characters and whitespace.

Brand           Hotel Name                  Street Address 1
------------------------------------------------------------------------
Maclean           Maclean Hotel Miami          18 Beach St.
Maclean           Maclean Hotel Fort Lauterdale  123 Market St.
Maclean           Maclean Hotel Mirimar          8080 Pleasant Rain Rd.
Devizes           Devizes Suites South Beach  65 Sand Rd.
Devizes           Devizes Suites North Beach  18 Windy Dunes La.
Devizes           Devizes Suites Fort Lauterdale 156 Sea Breeze Dr.
Camberwell Inn   Camberwell Inn - Coral Gables  75 Sunny Day Rd.
Kensington Inn   Kensington Inn - Fort Lauterdale 222 Palm Tree Rd.
Kensington Inn   Kensington Inn - Miami  156 Dune Rd.
St. Monica Hotels St. Monica Hotel - Vancouver  768 Border La.

And the resulting ids, stored in a lookup table would be

mysql> select * from ATD_CLIENT_HOTEL;
+--------+-------------------+
| CID    | HOTEL_BRAND       |
+--------+-------------------+
| MACLEA | Maclean           |
| DEVIZE | Devizes           |
| CAMBER | Camberwell Inn    |
| KENSIN | Kensington Inn    |
| STMONI | St. Monica Hotels |
| CHANCE | Chancellor        |
+--------+-------------------+
6 rows in set (0.00 sec)

The key to processing the Excel file is to use a ClearMapPut Record action on the OnDataChange1 event to write out a new record every time a new brand is encountered. You'll also need to add an ClearMapPut on the AfterFirstRecord event to capture the first record (before a change occurs). Also, some cleanup may be needed via a Target Filter to skip an empty record. NOTE: It's crucial that the input file is sorted for OnDataChange events to work. My Excel file was, but if it weren't, I would use a Sort in Map Desginer.

The algorithm (some simple string manipulation functions) used to form the identifiers isn't particularly rigorous. An improvement would be to bounce the computed id against the database to check for uniqueness. The computation ought to also offer a unique suggestions, say by replacing the last character with an index.

This post is replicated from http://my.opera.com/walkerca/blog/2010/12/06/ids-from-the-data-notes.