Insight

How to strip out spaces and unwanted characters for two name field

Discussion created by Insight on Feb 10, 2012
Latest reply on Feb 16, 2012 by Insight

Hi

 

I have a patient database that looks up past history in other years. So 10 years of records split into 10 different FM databases. Approx 20,000 records per year

 

I have a lookup database with relationships to patient lookup field based on the patients two names and part of their address.

 

The admin need to open this database with the relationshops to the other 10 lookup past databese and find the patient history in the past years.

 

I have a script that takes the two names and first line of address and copy and pastes it into the relationship field and 10 portals of the past databases which find all similar records over the past 10 years.

 

Each database has a calculation field for the lookup

 

John O'Mahony 127 Hillsbrook Drive - > John O'Mahony 127 Hillsbrook Drive

 

List in portal row

 

This one screen looks back over ten years abd gives report overview and link to relevant record.

 

This all works fairly ok but some of the patient name have been entered over the years with slight variations to the name. eg.

 

Mike Mc Carthy

 

Mike McCarthy [With a trailing space on entry]

 

Mike mcCarthy [Mix of CAPS etc}

 

or

 

John O'Mahony

 

or

 

John O Mahony

 

etc

 

so what I have decided to do is go back on all 10 databases and creat a new field calculated on the full two names and first line of the address

 

 

------- THIS IS WHAT I AM LOOKIN FOR --------

 

So I am looking for a script calculation that will strip out the spaces carrige returns and Uppercase of all text and .... ALSO strip out characters that may have been entered some years and not other years

 

for example the ' characters in John O'Mahony or John O' Mahony but not John O Mahony

 

what I would like to end up with is

 

johnomahony127hillsbrookdrive

 

That way I can link the relationship uniquely ( or as uniquly as possible at this point in time)

 

Any Help would be greatly appreciated ???

 

Thanks

 

Bob

Outcomes