Results 1 to 9 of 9

Thread: Adding Keywords

  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding Keywords

    Okay...

    I have a database which tracks calls. Each row designates one call received by the representative.

    Each call must be filtered into 5 seperate categories. I'm thinking I can give the rep the option to select a category, then I had another idea:

    How would I setup keywords to 'read' the cell where the call notes are stored, searching for specific keywords, and if found, designate the proper call type? For example, one of our call types is Billing Question, If the word "billing" or "bill" or "bill" and "submit" or "bill" and "request" is in the body of the cell containing the notes, I want another cell, call it (AF,1) to respond by returning 'TRUE' Any Ideas?

    thanks!!!

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Adding Keywords

    Just a little something to start things.

    If Cell A1 contains text, the following formula will return TRUE if that text contains "bill", otherwise FALSE

    <big>IF(FIND("BILL",UPPER(A1),1),TRUE,FALSE)</big>

    How many keywords would you envisage per category ? If you have ideas to include quiet a few the above method might become cumbersome.

    Hope that is some help

    Andrew C

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Keywords

    It's looking like 10 keywords or more.. but i've got to build in 'rules' of sorts.. For example, if the keyword entered also applies to another one of the five types, it needs to be somehow flagged.

    drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Adding Keywords

    That last formula is better replaced by

    =IF(ISERROR(SEARCH(D1,A1)),FALSE,TRUE)

    SEARCH is not case sensitive, so is probably better than FIND(). Also the previous formula returned an error rather than FALSE if the text was not found.

    To develop the idea any further I think we would need to know the
    Rules you plan to apply.

    I think your original idea of having the rep select the category would be better. It could be implemented with a simple Data Validation with Dropdown list.

    However, It is up to you


    Andrew C

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Keywords

    The rules are, if criteria match any other category, then say, the field should say "Tech Review" as I would have to review the notes and make my own determination, as we are currently doing.

    In addition to the 'automatic' system, we'll have the rep selection system. In this case, it's a userform, upon which they click the appropriate call type. The end result will be two sets of statistics, system percieved, and representative percieved, giving me, as the one who creates the reports, the ability to see at a glance flaws or mistakes in call-type assignment.

    Hope that little novel makes sense! [img]/w3timages/icons/laugh.gif[/img]

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  6. #6
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Adding Keywords (data validation)

    Drk,

    Why don't you use Data Validation List
    You could put all the catagories in the data validation list so the rep only has to select the correct catagory from the puldown list
    Greetings,

    Patrick Schouten
    (The Netherlands)

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Keywords (data validation)

    Well, from a reps perspective, it would take too much time. On a userform, they have 5 options right there, they just click on the appropriate checkbox, and move on with life. As they become more accustomed to where those checkboxes are, they don't even have to think about it anymore. Data validation requires that they read the contents of the pulldown validation box. In the interest of saving time and increasing productivity, I prefer the checkbox method...

    Thanks!

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Adding Keywords (data validation)

    OK, so if a checkbox suits, are you still interested in parsing an ad hoc note for the same information ?. It seems a little hit and miss, especially if the same keyword can have a meaning in different categories.

    The checkbox approach seems best, and as it seems you are going to "audit" the report your self, you could make a judgemnet as to wheter the call type as checked matches the rep's notes, which i would imagine you would have to do anyway. Andrew C.

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Keywords (data validation)

    That's a good point... it's beginning to sound like a little 'good' training on my part to educate the reps on call categorization would be the best solution here, and given that I will be 'auditing' the results, I can give corrective guidelines in the future, if at months end, the rep's data is chaotic. [img]/w3timages/icons/smile.gif[/img] Thanks alot Gents!

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •