Compare two tables and update record

[Evan Duran]

I'm trying to go through each record 1 at a time( I want to make sure I'm looking at published records only, such as ('workstate_flow', published)

I want to get field called 'number' from kb_knowledge table

I will then go to the kb_use table and get a row count for that number in it's corresponding field called article

I will then go back to kb_knowledge table and insert that rowCount number into that record's field called 'u_total_views'.

I'm unsure in the direction to take on this 

var gr = new GlideRecord('kb_knowledge);

gr.addQuery('workflow_state', published);

gr.query();

var anotherGr = new GlideRecord('kb_use');

anotherGr.addQuery('gr.number', article);

anotherGr.query();

getRowCount;

//

[Bert_c1]

Hi,

Try:

var gr = new GlideRecord('kb_knowledge');
gr.addQuery('workflow_state', 'published');
gr.query();
gs.info("kb_knowledge records " + gr.getRowCount());

while (gr.next()) {
  var anotherGr = new GlideRecord('kb_use');
  anotherGr.addQuery('article', gr.number);
  gs.info("looking for article: " + gr.number);
  anotherGr.query();
  gs.info("Found " + anotherGr.getRowCount() + " kb_use records");
}

[Bert_c1 · Accepted solution]

And updated script to update the custom field on kb_knowledge table:

var gr = new GlideRecord('kb_knowledge');
gr.addQuery('workflow_state', 'published');
gr.query();
gs.info("kb_knowledge records " + gr.getRowCount());

while (gr.next()) {
  var anotherGr = new GlideRecord('kb_use');
  anotherGr.addQuery('article', gr.number);
  gs.info("looking for article: " + gr.number);
  anotherGr.query();
  var nUse = anotherGr.getRowCount();
  gs.info("Found " + nUse + " kb_use records");
  // update the knowledge record custom use count field
  gr.u_total_views = nUse;
//  gr.update();
}

you can un-comment the 'gr.update();' once you have tested.