How is transcript field populated on the interaction table?

lonesoac01 — Thu, 06 Apr 2023 13:33:37 GMT

Hello all,

I am wondering if there is a table in Servicenow that contains each and every chat message sent. Here is what I am thinking. On the interaction.transcript field, all of the chat messages are in one field. What I am looking for are those same chat messages in their own individual records on a different table.

Thank you.

Re: How is transcript field populated on the interaction table?

LearnerSubho — Fri, 28 Jul 2023 12:38:00 GMT

@lonesoac01 - All conversations are stored in 'sys_cs_conversation' table and it has associated records in 'sys_cs_message' table.

So, if you can figure out a script to identify the required conversations and able to map the data properly, it might be possible to achieve what you want.

See if it helps.

Re: How is transcript field populated on the interaction table?

RodGallegos — Tue, 15 Oct 2024 21:09:42 GMT

@LearnerSubho is correct @lonesoac01 . When I did this, I had to insert an on insert BR on the interaction_log table which would trigger when the document table was sys_cs_conversation_task.

Code snippet

// Get the value of Document. This is how we identify the relevant conversation task record var docID = current.getValue('document_id'); gs.info('RG testing - docID = ' + docID); // Get the value of Interaction. This is how we will locate a related HR case var ims = current.interaction.toString(); gs.info('RG testing - ims = ' + ims); // Identify the conversation task record. This will give us access to the conversation sys_id // The Conversation sys_id will be what we use to gather all relevant messages var taskGR = new GlideRecord('sys_cs_conversation_task'); taskGR.addQuery('sys_id', docID); taskGR.setLimit(1); taskGR.query(); // If the query from the conversation task table returns a value, code continues if (taskGR.next()) { gs.info('RG testing - taskGR.next = true'); // Store the conversation sys_id var conversation = taskGR.conversation.toString(); gs.info('RG testing - conversation = ' + conversation); // Identify the related interaction in order to get to the related HR Case. var imsGR = new GlideRecord('interaction_related_record'); imsGR.addQuery('interaction', ims); imsGR.addEncodedQuery('document_tableISNOTEMPTY^taskISNOTEMPTY'); imsGR.setLimit(1); imsGR.query(); // If the query returns a related Interaction record, code continues if (imsGR.next()) { gs.info('RG testing - imsGR.next = true'); // Store the HR case sys_id var relCase = imsGR.task.toString(); gs.info('RG testing - relCase = ' + relCase); // Identifity the hr case var caseGR = new GlideRecord('sn_hr_core_case'); caseGR.addQuery('sys_id', relCase); caseGR.setLimit(1); caseGR.query(); // If the query returns an HR Case, code continues if (caseGR.next()) { gs.info('RG testing - caseGR.next = true'); // Identify all relevant messages from the relevant conversation var msgGR = new GlideRecord('sys_cs_message'); msgGR.addQuery('conversation', conversation); msgGR.addEncodedQuery('message_type=text^sender_profileISNOTEMPTY'); msgGR.orderBy('sys_created_on'); msgGR.query(); // For each message found, insert it as a comment to the relevant case while (msgGR.next()) { gs.info('RG testing - msgGR.next = true'); // Store message var message = msgGR.payload.toString(); gs.info('RG testing - message = ' + message); // Store sender profile var messageSender = msgGR.sender_profile.getDisplayValue(); gs.info('RG testing - messageSender = ' + messageSender); // Create the comment caseGR.comments = messageSender + " said: " + "\n" + message; // Insert the comment to the case caseGR.update(); } } else gs.info("No related case found"); } else gs.info("No related interaction record found"); } else gs.info("No conversation task found");

question How is transcript field populated on the interaction table? in ITSM forum

How is transcript field populated on the interaction table?

Re: How is transcript field populated on the interaction table?

Re: How is transcript field populated on the interaction table?