Formating the address

We downloaded the Auto Address Correction script from TASK - It is a shared report, Solution 99 in the Reference Materials Project, created through the Washington National Opera.  Here is a brief summary of its functionality:

This procedure provides auto address correction and formatting during data entry.

Spells out most abbreviation and fixes case, punctuation etc for names and addresses.

Primarily written for data entry online by patrons, but also used in-house. An

attribute of “auto address correction” to “no” suppresses routine for a particular

patron, should the algorithms not work correctly.

Have a look at it, this has worked wonders for us (Thank you Brian!), as there was no consistent formatting that came in with records from our old system during Tessitura implementation.  The script was initially run over our whole database to make corrections on addresses already there and automatically formats new addresses upon creation.

We too have downloaded the procedure created by the Washington National Opera. However, we modified it to not correct entries made by certain security groups (development). Often times development may want to have a salutation or address done in a way outside the paramter of the script, so we let them. Overall, for our institution the script has helped to normalize our data essential for mailings etc.. I would recommend it for your institution.

Christian @ The Tech Museum

Hi,

I have succesfully downloaded the funtion,now I am unable to determine in which stored procedure ,I should run the funtion inorder to update the address in the t_address table. Also,should I schedule to run the stored produre every day inorder to update the new coustomer addresses.

kindly assist me.

Thank you,

Revanth Kumar Anne

Revanthe,

Like I said, we took the original procedure and customized it.  We have the routine set to run in a nightly job.

Here is what our job is set to do:

lp_tech_cleanup_nameandaddress_nightly
@list_no = 155,
@list_no_exclude = 166,
@update_sals = 'Y',
@increment = 1

I'll have to download the original again and see what the procedure name is. But essentially, you need to create a job and have it execute the procedure with any required parameters.

Christian

Ok Revanthe,

here are the directions on  how to use the procedure:

Run the script to create the function lf_titlecase and give permission to impusers.

There is a .SQL file called lf_titlecase. Execute it from the SQL Management Studio. Then find the function and give it permissions.

Add the attached code to lp_customer_rank.  This is the local procedure that is called by tessitura everytime an address or name is added/changed.

Your Tessitura database already has a procedure
called LP_CUSTOMER_RANK. From SQL Management
studio, open it up and add the modified code
to it. The modified code is in the download.


Setup the auto address attribute for exceptions
in t_keyword:

(data type = "string", edit mask = "none",
detailtbl = "tx_cust_keywork",
detail col = "!.key_value",
ref_tbl = "tr_gooesoft_dropdown",
ref_idcol = "short_desc",
ref_descol = "description, refwhere = "code=1")


Go to your system tables and add an entry in T_KEYWORD. The details are shown above. This is so that you can mark an account to be excluded  from auto correction. This will prove very helpful as the procedure is not meant to handle all cases properly. So in some situations, you just want it to exclude a record from cleanup.

Modify the number of the new attribute in the code
added to lp_customer_rank.

Once you have added the row to T_KEYWORD, you need to open up LP_CUSTOMER_RANK and there is a parameter value for the new keyword you created. You need to put the keyword number in there.

And, no job is required. I forgot that we have modified our significantly so that it must be called by a job. The way this routine works is that it is called by lp_customer_rank which is fired off whenever data is saved to the database.
So, when the address is created and saved, it will be auto corrected.


Hope all of this helps. Sorry for any confusion. I had forgotten that we made substantial changes to the procedure. However, it uses the same core.

Christian

Hi

We originally ran this in lp_customer_rank but after having a major problem with sql deadlocking on a perticularly busy day we moved this to an overnight job as we thought this process may be adding to the problem.

When running overnight I only correct addresses that are in all lower or upper case as they tended to be the ones that caused most problems for us.

I also had to modify the function as we had problems with it converting the word Farm to FaRoom (rm -> Room) and a few other little bits and pieces that cropped up.

Mark