My findings:

1. SOUNDS needs a text string. I found the quickest way was the UNION
   SELECT
2. If I used OR, performance is abismal (unindexed search, I think) but
   the union select works quickly
3. Note the favourite SOUNDS search: "JORJIA" which won't find the
   parishioner easily any other way when the user is on the phone
   hearing "GEORGIA"
4. I am seriously considering concatenating the ContactLookupName to
   include the address and phone as Dick mentioned above. Brilliant, Dick

Albert

On 2016-09-21 12:41 PM, Jim Belisle wrote:

Dick,

I will need to try that type of column. I have had trouble in the past with speed when I use multiple columns to look at.

Interesting concept.

James Belisle

*Making Information Systems People Friendly Since 1990*

cid:image001.jpg@01CD8CE2.FB048F50

*From:*rbase-l@googlegroups.com [mailto:rbase-l@googlegroups.com] *On Behalf Of *Carpet Broker, Dick Fey
*Sent:* Wednesday, September 21, 2016 10:41 AM
*To:* rbase-l@googlegroups.com
*Subject:* Re: [RBASE-L] - My solution to awkward parishioner name searches

Karen;

We use a computed column of called 'CustomerInfo' (customername, city, zipcode, customerphone) to allow an almost instant search for a customer to open an order form. The search is 'Where customerinfo contains'. Works great
You can enter any of those criteria to find the customer.


Dick Fey
Carpet Broker Inc.

On 9/12/2016 8:16 AM, karentellef via RBASE-L wrote:

    Wow, I haven't used "sounds" in a gazillion years, back to the DOS
    days.  Think I
    might play with it again!

    But something you said interests me.  You say that
    "contactlookupname" is a
    computed column concatenating all 4 names.  Now THAT is something
    I never thought
    of doing!  If someone wanted to type in a name, and have it search
    perhaps a company
    name but also a contact name, I would do a "company contains
    .vtext OR contact
    contains .vtext", as you say.  But if there's a concatenation,
    then you can do a
    contains against just one column.  I'm going to try this!

    So since you have that concatenated column, why are you doing the
    "sounds"
    against the separate pieces?  Is it because the "sounds" doesn't
    work correctly
    against the longer computed column?

    Karen

    -----Original Message-----
    From: Albert Berry <alb...@albertberry.com>
    <mailto:alb...@albertberry.com>
    To: rbase-l <rbase-l@googlegroups.com>
    <mailto:rbase-l@googlegroups.com>
    Sent: Sun, Sep 11, 2016 11:19 pm
    Subject: [RBASE-L] - My solution to awkward parishioner name searches

    I am working up a useable search function to find parishioners.
    This is how I got around the plethora of names in a Roman Catholic
    Parish
    that dates back to 1896. Each of the input name spaces allows 30
    characters.

    There are many unusual spellings here. The coal miners came from
    virtually
    every European country, and names are spelled every which way, so
    I used
    the very useful function supplied by R:Base, the SOUNDEX "SOUNDS"
    Here is my solution to the problem. It works very quickly. I
    started by using
    "OR" but it was very slow.

    My favourite spelling of them all is "Jorjia" - I don't think I
    could find it any other way.

    *****************************************************************
    SET ERROR MESSAGE 677 OFF
      DROP VIEW vwParishioners1
      DROP VIEW vwParishioners
      SET ERROR MESSAGE ON
    -- ContactLookupName is computed to concatenate all four names
    with the
    -- nickname in parentheses. Nickname is also useful as "usual name"
    -- My wife's lookupname is BERRY Marie Marguerite Claire (Claire)
      CREATE VIEW vwParishioners1 +
    (ContactID,ParishID,ContactSurName,ContactName1, ContactName2,+
          ContactNickName, ContactLookupName,ParishName) AS +
        SELECT ContactID,OrganizationID,ContactSurName,ContactName1,
    ContactName2,+
    ContactNickName,ContactLookupName,OrganizationName +
          FROM ContactOrganization t1, Contact t2,Organization t3 WHERE +
            t1.ContactID = t2.ContactID AND t1.OrganizationID =
    t3.OrganizationID
      COMMENT ON VIEW vwParishioners1 is "
      CREATE VIEW vwParishioners  +
    (ContactID,ParishID,ContactSurName,ContactName1, ContactName2,+
          ContactNickName, ContactLookupName,ParishName) AS +
        SELECT * FROM vwParishioners1 +
          WHERE ContactSurName SOUNDS "BERRY" +
          UNION SELECT * FROM vwParishioners1 +
              WHERE ContactName1 SOUNDS "BERRY" +
              UNION SELECT * FROM vwParishioners1 +
                  WHERE ContactName2 SOUNDS "BERRY" +
                  UNION SELECT * FROM vwParishioners1 +
                      WHERE ContactNickName SOUNDS "BERRY"
      RETURN

    ************************************************************************

-- You received this message because you are subscribed to the Google
    Groups "RBASE-L" group.
    To unsubscribe from this group and stop receiving emails from it,
    send an email to rbase-l+unsubscr...@googlegroups.com
    <mailto:rbase-l+unsubscr...@googlegroups.com>.
    For more options, visit https://groups.google.com/d/optout
    <https://groups.google.com/d/optout>.

-- You received this message because you are subscribed to the Google
    Groups "RBASE-L" group.
    To unsubscribe from this group and stop receiving emails from it,
    send an email to rbase-l+unsubscr...@googlegroups.com
    <mailto:rbase-l+unsubscr...@googlegroups.com>.
    For more options, visit https://groups.google.com/d/optout.



--
Dick Fey
Carpet Broker Inc
P: 913-894-9211
F: 913-894-0138
--
You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to rbase-l+unsubscr...@googlegroups.com <mailto:rbase-l+unsubscr...@googlegroups.com>.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups 
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to rbase-l+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Reply via email to