Search a string for keywords and return a value

[leahdany]

I'm trying to search a string for keywords found on another table. If found I want to know what those keywords were and update the original record that had the search string with those keyword values in addition to the category.

In this example, I want to search the string in the "Question" field on table 1 for keywords found on table 2. If found on table 2 I want to populate table 1s "Keyword" and "Category" fields with the applicable keyword(s) and category.

"Category" on table 1 is a reference directly to table 2, so the sys id of table 2.

The "Keyword" field on both tables are strings.

[Arjun31]

Hello leahdany, 

Please use below before business rule,

I have checked on personal instance and working fine.

(function executeRule(current, previous /*null when async*/) {

var question=current.u_question;
var arr=question.split(" ");


var gr=new GlideRecord('u_table_2');
for(var i=0;i<arr.length;i++)
{
gr.addEncodedQuery('u_keywordLIKE'+arr[i]);
gr.query();
if(gr.next())
{
current.u_keyword=arr[i];
current.u_category=gr.u_category;
break;
}
}

})(current, previous);

I have attached screenshot too.

Please mark it correct and helpful if it works.

regards,

Arjun Shinde

[leahdany · Accepted solution]

Thanks for all the replies. Here's what ended up working for me.

var report = new GlideRecord('u_conversation_message_report');
report.addEncodedQuery("u_categoryISEMPTY^u_questionISNOTEMPTY");
report.query();

while (report.next()) {
    var question = report.u_question.toString().toLowerCase();
    var keywords = [];
    var i = 0;
    var category = '';
    var keywordsNoComma = '';

    var catTable = new GlideRecord("u_conversation_message_category");
    catTable.query();
    while (catTable.next()) {
        i = 0;
        keywords = catTable.u_keyword.toString().toLowerCase().split(',');
        while (i < keywords.length) {

            if (question.indexOf(keywords[i]) > -1) {
                if (keywordsNoComma != '') {  // This is removing the leading comma from the keywords
                    keywordsNoComma += ',';
                }
                keywordsNoComma += keywords[i];
                category = catTable.sys_id;

                report.u_keyword = keywordsNoComma;
                report.u_category = category;
                report.update();
            }
            i++;
        }

    }

}