Using Email Variables from body text to set Catalog item

[EricG]

I finally got help  with retrieving variables form the email body text. eg. Cost_Center:2258

I've set my logs to verify the value e.g.2258

My Inbound action will set the catalog item I'm using with the value.

YEAH!!!

Now, the issue is I can't find the related assignment group based on the value.

I've tried this in the workflow and also in the inbound action. Here is what i'm doing in my inbound action.

I tried this in the workflow as well with the same results (only changed the set values accordingly)

	var splitext=email.body_text;
	var splittoget=splitext.split('Cost_Center:')[1];
	var splittogetfinal=splittoget.split(' ')[0]; //This will give final result
	var fmcCC = splittogetfinal.toString();
	var fmcG = '';
		gs.log('DEBUG - the final answer is '+splittogetfinal);
		gs.log('DEBUG - the fmc is '+fmcCC);
	var assgn = new GlideRecord('u_fmc_list');
	assgn.addEncodedQuery('u_active=true^u_fmc_codeLIKE'+fmcCC);
	assgn.query();

	if(assgn.next()){
		fmcG = assgn.u_user_name;//reference field to groups table

	}else{
		fmcG = 'e7571637db412b807317323b7c9619a6';
	}

[Allen Andreas]

Hello,

Your post was a little difficult to follow, mostly because you're using a custom table and you have custom fields, of course, so we don't know what any of that is or means.

With that said, use an example, such as 2258 and query your u_fmc_list table and see if using filter as: active = true and fmc code CONTAINS 2258 actually results in anything.

It's always best to conduct your query as far as you can, then right-click the last piece of the breadcrumb trail and choose: "copy query", then paste that between double quotes " " in your addEncodedQuery line.

Now that you've tested things to ensure you actually get a record in your query, add troubleshooting log statements to your script so you aren't coding in the dark. Are you entering the if statement for assgn.next() or is it resulting in else.

What is being set now? What should be set?

You haven't really provided thorough explanation to us other than it doesn't work.

Try reviewing what I've mentioned above, see if that helps, if not, please provide more information such as screenshots showing field names, what a record looks like, what your inbound email looks like, what your logs look like, etc.

Right now, all we can do is review your code for formatting...that's it and not all of it is even here. Like where are you using fmcG after you assign a value to it?

You should also be using getters and setters, such as fmcG = assgn.getValue('u_user_name');

u_user_name is also a weird name for a group referenced field, so I'd double-check that.

Please mark reply as Helpful/Correct, if applicable. Thanks!

[EricG]

Thank You Allen

I've updated my custom table so that the name reflects what is is.

Table Name: u_pl_fmc_list

Field: u_fmc_code

Type: string

Field: u_support_group 

Type: Reference

Qualifier: u_active = true

My Inbound action is attached.

I was thinking that maybe the inbound would set or update the catalog, so put in a "catch" in my cat item workflow. A run script activity.

var me = current.variables.fmc_cost_center;
gs.log('Eric - the varible value is '+me);
var assgn = new GlideRecord('u_pl_fmc_list');
//u_active=true^u_fmc_codeLIKE2518
assgn.addEncodedQuery("u_active=true^u_fmc_codeLIKE"+me.toSting());
	assgn.query();
	
	if(assgn.next()){
		current.variables.assignment_group = assgn.getValue('u_support_group');
		
		gs.log('Eric - the group is '+me+' and the sys id is '+assgn.u_support_group);
	}else{
		current.variables.assignment_group = 'e7571637db412b807317323b7c9619a6';
		gs.log('Eric - the WF group assignment failed');
	}

Please note the commented out query, as I copied this and verified it would.

If i use the cat item directly and enter the 2518 value in the u_fmc_code field.

the assignment lookup in the form will retrieve the expected value and return PL - Lone star group. 

Querying my custom table likewise returns the same value.

Now when i run the inbound action, both cases (inbound action and cat_item WF) both fail and return the ELSE value.  

Ticket values

Form Variable

I'm not real good at adding Error messages or capturing, but what i have in the logs indicate to me that the variable captured from the email value is not a string or what is expected to query from.

Logs attached.

I'm hoping this is more clear.

[Allen Andreas]

Hi,

This line has a typo:

assgn.addEncodedQuery("u_active=true^u_fmc_codeLIKE"+me.toSting());

it's toString().

Also, gs.log() doesn't work for scoped applications (this may be global, but it's best to always use something like gs.info() instead for your logging).

So to give a bit more information, when you're emailing into the system, who are you emailing it from? An unknown/Guest user or from your admin account email address?

When you're ordering it with catalog, what type of user are you using?

This could be a permissions issue where in the inbound action scenario, the system is associating all of this to Guest or something and so they may not have permission to  "read" your table. Check over your read ACLs and see if that's the case.

Please mark reply as Helpful/Correct, if applicable. Thanks!

[EricG · Accepted solution]

Finally got this to work, after consulting with a local buddy of mine on the phone.

Apparently the there is an issue with getting the recommend script to work in Text fields or HTML fields

Ended up using a combination of indexOf and State/End positions to split out the desired data.

This was then usable in the gliderecord quest to pull Support Group assign ticket correctly.

function runAction( /*GlideRecord*/ current, /*GlideRecord*/ event, /*EmailWrapper*/ email, /*ScopedEmailLogger*/ logger, /*EmailClassifier*/ classifier) {
		
	var fmcCC = '';;
	var fmcG = '';
	
	//************************
	var fmcCCstart = '';
	var fmcCCend = '';
	var fmcCCtxt = '';

    fmcCCstart = email.body_text.indexOf("Cost_Center:");
	if (email.body_text.indexOf("Cost_Center:") > -1) {
		fmcCCstart = fmcCCstart + 12;
		fmcCCend = fmcCCstart + 4;
		fmcCCtxt = email.body_text.slice(fmcCCstart,fmcCCend);
	//	gs.log('Eric - fmcCCtxt with slice is: ' + fmcCCtxt);
	}

	fmcCC = fmcCCtxt;

	var assgn = new GlideRecord('u_pl_fmc_approval_list');
	assgn.addEncodedQuery("u_active=true^u_cost_centerLIKE"+fmcCC);
	assgn.query();
	
	if(assgn.next()){
		fmcG = assgn.getValue('u_support_group');

	}else{
		fmcG = 'e7571637db412b807317323b7c9619a6';
	}
	
	var sender = email.origemail;
    var other = email.origemail;
    var ritmSysID = '';
    var me = new GlideRecord('sys_user');
    me.addQuery('email', sender);
    me.query();
    while (me.next()) {
		other = me.sys_id;
	}
    var emailBody = "received from: " + email.origemail + "\n\n" + email.body_text; 
    // Implement email action here
    createRequest();

    function createRequest() {
		
		var cart = new Cart(); //calling the cart API
        var item = cart.addItem('464562161b8ec1107bf5fd951a4bcbf6'); //sys_id of the General Request catalog item I want to fire
        cart.setVariable(item, 'caller', other);
        cart.setVariable(item, 'Comments', email.body_text); //sets catalog variable to email's body
        cart.setVariable(item,'cost_center',fmcCC);
		
		cart.setVariable(item,'assignment_group',fmcG);
		var rc = cart.placeOrder(); //this launches the catalog item, and creates a request object.   rc = the request object
        updateRITM(rc.sys_id); //call a function immediately to update the ritm.   This must be a nested function, otherwise inbound actions get weird.
        //also, we're passing the sys_id of the request so we know what RITM to grab.
    }

	function updateRITM(req) {
		var ritm = new GlideRecord('sc_req_item');
        ritm.addQuery('request', req); //req is what we passed from the previous function. the sys_id of the request.
        ritm.query();
        
		while (ritm.next()) {
			ritm.description = email.body_text;
            ritm.u_original_email = sys_email.sys_id;
            ritm.short_description = email.subject;
            ritmSysID = ritm.sys_id;

			var assgn2 = new GlideRecord('u_pl_fmc_approval_list');
			assgn2.addEncodedQuery("u_active=true^u_cost_centerLIKE"+fmcCC);
			assgn2.query();
			
			if(assgn2.next()){
				ritm.assignment_group = assgn.getValue('u_support_group');
'+assgn.getValue('u_support_group'));
			}else{
				ritm.assignment_group = 'e7571637db412b807317323b7c9619a6';
			}
			
            ritm.update();
		}

        //This added attachments to the RITM and updates the sys_email instance field with the RITM #
        var emailRec = new GlideRecord("sys_email");
        emailRec.addQuery("uid", email.uid);
        emailRec.orderByDesc("sys_created_on");
        emailRec.query();

        if (emailRec.next()) {
			GlideSysAttachment.copy("sys_email", emailRec.sys_id, "sc_req_item", ritmSysID);
            emailRec.instance = ritmSysID;
            emailRec.target_table = "sc_req_item";
            emailRec.update();
        }
	}
    event.state = "stop_processing"; //stop evaluating inbound actions. This is why I keep this record's order lower than my incident from email rules.
})(current, event, email, logger, classifier);