9 Replies Latest reply on Feb 16, 2012 7:02 AM by Insight

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

    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