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



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}




John O'Mahony




John O Mahony




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




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


Any Help would be greatly appreciated ???