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
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 ???