How is transcript field populated on the interaction table?

[lonesoac01]

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.

[LearnerSubho]

@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.

[RodGallegos]

@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");