Service Portal Form Fields, Part VI

“If at first you don’t succeed, call it version 1.0”
Charles Lauller

Well, that was an adventure! Even though I was just about 100% sure that it wouldn’t work, I went ahead and defined yet another type for my snh-form-field tag to experiment with embedding an sn-record-picker. It seemed highly unlikely to me that AngularJS was smart enough to run through the evaluation process on a tag that was rendered in a template that itself was resolved in the evaluation process. But, I like to try things even when I’m pretty sure that they won’t work, so I added reference to my list of supported types and added this to my little test page widget:

        <snh-form-field
          snh-form="form1"
          snh-model="c.data.reference"
          snh-name="reference"
          snh-label="Reference Field"
          snh-type="reference"
          table="'cmn_department'"
          display-field="'name'"
          display-fields="'id'"
          value-field="'sys_id'"
          search-fields="'id,name'"
          default-query="'u_active=true"
          page-size="100"
          snh-required="true"/>

To my utter amazement, it actually worked! I did not expect that. I was pleasantly surprised to see that it worked perfectly fine, and I was very happy that I had decided to give it shot before trying to figure out some other way to incorporate this capability in my own directive. There were, of course, a few little adjustments needed here and there, but hey — it was pretty cool to see that it actually worked without me having to do much of anything. I was both astonished and elated. Elation, though, is a temporary state, and things really started to go down from there.

To start with, it just looked a little funky. It was like a field within a field, but not completely, with one element bleeding over the bottom edge of the other. Something just wasn’t right somewhere.

First attempt at setting up a reference field

That one wasn’t that hard to figure out though. I had added the snh-form-control class to the sn-record-picker tag, but the sn-record-picker generates its own class attribute on a different level, so the two were redundant as well as conflicting. I removed my class attribute and that solved that problem. But that was really an insignificant problem compared to what I noticed next: the field wasn’t validating.

The required field indicator was grey instead of red, no matter whether or not you had selected anything from the drop-down. And no error messages appeared no matter what you did. It was as if validation wasn’t turned on for that field at all. Then I remembered that the model for an sn-record-picker is not a string containing the value selected, but an object containing a property that contains the value selected, appropriately named value. Where the value of every other type of field was <form-name>.<field-name>, the value of an sn-record-picker is <form-name>.<field-name>.value. That meant putting in some conditional code just for the reference type fields, which I had not really had to do for any other types up to this point.

Unfortunately, things still were not working after all of that. Now I was starting to get a little frustrated because the picker itself just worked like a charm right out of the gate, which I absolutely did not expect, but the field validation, which I thought would be the one part to work just fine, still wasn’t working at all. I kept digging, though, and eventually I figure out that when the sn-record-picker renders the name attribute, it throws in an extra little trailing space at the end of field name. Instead of name=”reference” I ended up with name=”reference “. Well, that little extra, unwelcome, trailing space means that <form-name>.<field-name> doesn’t reference that field after all. You need to use <form-name>[‘<field-name-plus-one-space>’] because you can’t represent that trailing space without using the square bracket notation instead of the dot notation. So, I ended up adding yet more type-specific code. I don’t know if that extra trailing space is just an unfortunate mistake or it was put in by design, but if it was a mistake, I hope they don’t fix it one day, because now I am relying on it being there for things to work.

After all of those changes, though, it still wasn’t validating. Things were starting to progress from frustration to irritation. Every time that I thought that I had found and fixed the problem, I would try it out expecting things to be fixed, and it still wouldn’t be working. After a lot of head scratching and debugging alerts and trial and error, I finally was able to figure out that the validation messages only appear once the field had been $touched, and the sn-record-picker fields are never $touched. They can get $dirty, but for some reason, they are never $touched. So I modified my condition on showing the validation errors be $touched or $dirty, and finally, everything worked as it should. Finally.

So here is the way things look so far:

function() {
	var SUPPORTED_TYPE = ['choicelist', 'date', 'datetime-local', 'email', 'inlineradio', 'month', 'number', 'password', 'radio', 'reference', 'tel', 'text', 'textarea', 'time', 'url', 'week'];
	var RESERVED_ATTR = ['ngHide', 'ngModel', 'ngShow', 'class', 'field', 'id', 'name', 'required'];
	var STD_MESSAGE = {
		email: "Please enter a valid email address",
		max: "Please enter a smaller number",
		maxlength: "Please enter fewer characters",
		min: "Please enter a larger number",
		minlength: "Please enter more characters",
		number: "Please enter a valid number",
		pattern: "Please enter a valid value",
		required: "This information is required",
		url: "Please enter a valid URL",
		date: "Please enter a valid date",
		datetimelocal: "Please enter a valid local date/time",
		time: "Please enter a valid time",
		week: "Please enter a valid week",
		month: "Please enter a valid month"
	};
	return {
		restrict: 'E',
		replace: true,
		require: ['^form'],
		template: function(element, attrs) {
			var htmlText = '';
			var form = attrs.snhForm;
			var name = attrs.snhName;
			var model = attrs.snhModel;
			var type = attrs.snhType || 'text';
			var required = attrs.snhRequired && attrs.snhRequired.toLowerCase() == 'true';
			var fullName = form + '.' + name;
			var refExtra = '';
			type = type.toLowerCase();
			if (SUPPORTED_TYPE.indexOf(type) == -1) {
				type = 'text';
			}
			if (type == 'reference') {
				fullName = form + "['" + name + " ']";
				refExtra = '.value';
			}
			htmlText += "    <div id=\"element." + name + "\" class=\"form-group\"";
			if (attrs.ngShow) {
				htmlText += " ng-show=\"" + attrs.ngShow + "\"";
			}
			if (attrs.ngHide) {
				htmlText += " ng-hide=\"" + attrs.ngHide + "\"";
			}
			htmlText += ">\n";
			htmlText += "      <div id=\"label." + name + "\" class=\"snh-label\" nowrap=\"true\">\n";
			htmlText += "        <label for=\"" + name + "\" class=\"col-xs-12 col-md-4 col-lg-6 control-label\">\n";
			htmlText += "          <span id=\"status." + name + "\"";
			if (required) {
				htmlText += " ng-class=\"" + model + refExtra + ">''?'snh-required-filled':'snh-required'\"";
			}
			htmlText += "></span>\n";
			htmlText += "          <span title=\"" + attrs.snhLabel + "\" data-original-title=\"" + attrs.snhLabel + "\">" + attrs.snhLabel + "</span>\n";
			htmlText += "        </label>\n";
			htmlText += "      </div>\n";
			if (attrs.snhHelp) {
				htmlText += "    <div id=\"help." + name + "\" class=\"snh-help\">" + attrs.snhHelp + "</div>\n";
			}
			if (type == 'radio' || type == 'inlineradio') {
				htmlText += buildRadioTypes(attrs, name, model, required, type);
			} else if (type == 'choicelist') {
				htmlText += buildChoiceList(attrs, name, model, required);
			} else if (type == 'reference') {
				htmlText += "      <sn-record-picker field=\"" + model + "\" id=\"" + name + "\" name=\"" + name + "\"" + passThroughAttributes(attrs) + (required?' required':'') + "></sn-record-picker>\n";
			} else if (type == 'textarea') {
				htmlText += "      <textarea class=\"snh-form-control\" ng-model=\"" + model + "\" id=\"" + name + "\" name=\"" + name + "\"" + passThroughAttributes(attrs) + (required?' required':'') + "></textarea>\n";
			} else {
				htmlText += "      <input class=\"snh-form-control\" ng-model=\"" + model + "\" id=\"" + name + "\" name=\"" + name + "\" type=\"" + type + "\"" + passThroughAttributes(attrs) + (required?' required':'') + "/>\n";
			}
			htmlText += "      <div id=\"message." + name + "\" ng-show=\"(" + fullName + ".$touched || " + fullName + ".$dirty) && " + fullName + ".$invalid\" class=\"snh-error\">{{" + fullName + ".validationErrors}}</div>\n";
			htmlText += "     </div>\n";
			return htmlText;

			function buildRadioTypes(attrs, name, model, required, type) {
				var htmlText = "      <div style=\"clear: both;\"></div>\n";

				var option = null;
				try {
					option = JSON.parse(attrs.snhChoices);
				} catch(e) {
					alert('Unable to parse snh-choices value: ' + attrs.snhChoices);
				}
				if (option && option.length > 0) {
					for (var i=0; i<option.length; i++) {
						var thisOption = option[i];
						if (type == 'radio') {
							htmlText += "      <div>\n  ";
						}
						htmlText += "        <input ng-model=\"" + model + "\" id=\"" + name + thisOption.value + "\" name=\"" + name + "\" value=\"" + thisOption.value + "\" type=\"radio\"" + passThroughAttributes(attrs) + (required?' required':'') + "/> " + thisOption.label + "\n";
						if (type == 'radio') {
							htmlText += "      </div>\n";
						}
					}
				}

				return htmlText;
			}

			function buildChoiceList(attrs, name, model, required) {
				var htmlText = "      <select class=\"snh-form-control\" ng-model=\"" + model + "\" id=\"" + name + "\" name=\"" + name + "\"" + passThroughAttributes(attrs) + (required?' required':'') + ">\n";
				var option = null;
				try {
					option = JSON.parse(attrs.snhChoices);
				} catch(e) {
					alert('Unable to parse snh-choices value: ' + attrs.snhChoices);
				}
				htmlText += "        <option value=\"\"></option>\n";
				if (option && option.length > 0) {
					for (var i=0; i<option.length; i++) {
						var thisOption = option[i];
						htmlText += "        <option value=\"" + thisOption.value + "\">" + thisOption.label + "</option>\n";
					}
				}
				htmlText += "      </select>\n";
				return htmlText;
			}

			function passThroughAttributes(attrs) {
				var htmlText = '';
				for (var name in attrs) {
					if (name.indexOf('snh') != 0 && name.indexOf('$') != 0 && RESERVED_ATTR.indexOf(name) == -1) {
						htmlText += ' ' + camelToDashed(name) + '="' + attrs[name] + '"';
					}
				}
				return htmlText;
			}

			function camelToDashed(camel) {
				return camel.replace(/([a-zA-Z])(?=[A-Z])/g, '$1-').toLowerCase();
			}
		},
		link: function(scope, element, attrs, ctls) {
			var form = ctls[0].$name;
			var name = attrs.snhName;
			var fullName = form + '.' + name;
			if (!scope.$eval(fullName)) {
				fullName = form + '["' + name + ' "]';
			}
			var overrides = {};
			if (attrs.snhMessages) {
				overrides = scope.$eval(attrs.snhMessages);
			}
			scope.$watch(fullName + '.$valid', function (isValid) {
				var elem = scope.$eval(fullName);
				elem.validationErrors = '';
				var separator = '';
				if (!isValid) {
					for (var key in elem.$error) {
						elem.validationErrors += separator;
						if (overrides[key]) {
							elem.validationErrors += overrides[key];
						} else if (STD_MESSAGE[key]) {
							elem.validationErrors += STD_MESSAGE[key];
						} else {
							elem.validationErrors += 'Undefined field validation error: ' + key;
						}
						separator = '<br/>';
					}
				}
			});
		}
	};
}

I’m still not happy about having to specify the form name, still need to do something about the wrong validation message showing up in certain circumstances, and I still haven’t tested all of the support types just yet, but I think it just might be complete enough to go ahead and throw an initial version into an Update Set for anyone who might want to play along at home. There is still a lot that I would like to do before I’m ready to call it really good, but it does work, so there is that. As the man who jumped off of the high-rise building was heard saying as he passed each floor, “So far, so good …”

Service Portal Form Fields, Part V

“There is nothing impossible to him who will try.”
Alexander the Great

After doing a lot of testing and tweaking, I decided that it was time to add a few more options to my list of supported types. Everything up to this point was still a basic text or textarea variation that didn’t require any special coding to produce the INPUT element. Now it is time to try something a little more sophisticated that will require a little bit of extra work. Both radio buttons and select statements work off of a list of choices from which you select your value, so I came up with yet another attribute to support types of this nature. I called it snh-choices, and the expectation is that you will provide a JSON array of label/value pairs in the order in which you want them to be presented. Then I added three new values to my list of supported types, choicelist, radio, and inlineradio. The only difference between radio and inlineradio is that the radio type lists the choices from top to bottom and the inlineradio type lists them from left to right. Other than that, they are essentially the same thing.

To support the new types, I created a couple of functions to render the appropriate INPUT element and the associated choices:

function buildRadioTypes(attrs, name, model, required, type) {
	var htmlText = "      <div style=\"clear: both;\"></div>\n";

	var option = null;
	try {
		option = JSON.parse(attrs.snhChoices);
	} catch(e) {
		console.log('Unable to parse snh-choices value: ' + attrs.snhChoices);
	}
	if (option && option.length > 0) {
		for (var i=0; i<option.length; i++) {
			var thisOption = option[i];
			if (type == 'radio') {
				htmlText += "      <div>\n  ";
			}
			htmlText += "        <input ng-model=\"" + model + "\" id=\"" + name + thisOption.value + "\" name=\"" + name + "\" value=\"" + thisOption.value + "\" type=\"radio\"" + passThroughAttributes(attrs) + (required?' required':'') + "/> " + thisOption.label + "\n";
			if (type == 'radio') {
				htmlText += "      </div>\n";
			}
		}
	}

	return htmlText;
}

function buildChoiceList(attrs, name, model, required) {
	var htmlText = "      <select class=\"snh-form-control\" ng-model=\"" + model + "\" id=\"" + name + "\" name=\"" + name + "\"" + passThroughAttributes(attrs) + (required?' required':'') + ">\n";

	var option = null;
	try {
		option = JSON.parse(attrs.snhChoices);
	} catch(e) {
		console.log('Unable to parse snh-choices value: ' + attrs.snhChoices);
	}
	htmlText += "        <option value=\"\"></option>\n";
	if (option && option.length > 0) {
		for (var i=0; i<option.length; i++) {
			var thisOption = option[i];
			htmlText += "        <option value=\"" + thisOption.value + "\">" + thisOption.label + "</option>\n";
		}
	}
	htmlText += "      </select>\n";

	return htmlText;
}

To test out these new types, I modified my little test page yet again to add some additional form fields to try out each of the three new types. The HTML for that page now looks like this:

<snh-panel title="'${Form Field Test}'">
  <form id="form1" name="form1" novalidate>
    <div class="row">
      <div class="col-xs-12 col-sm-6">
        <snh-form-field
          snh-form="form1"
          snh-model="c.data.firstName"
          snh-name="firstName"
          snh-label="First Name"
          snh-type="text"
          snh-required="true"
          snh-messages='{"required":"You must enter a First Name"}'/>
        <snh-form-field snh-form="form1"
          snh-model="c.data.middleName"
          snh-name="middleName"
          snh-label="Middle Name"
          snh-type="text"
          snh-required="false" />
        <snh-form-field
          snh-form="form1"
          snh-model="c.data.lastName"
          snh-name="lastName"
          snh-label="Last Name"
          snh-type="text"
          snh-required="true"
          minlength="5"/>
        <snh-form-field
          snh-form="form1"
          snh-model="c.data.email"
          snh-name="email"
          snh-label="Email Address"
          snh-type="email"
          snh-required="true"
          snh-messages='{"required":"You must enter an Email address", "email":"You must enter a valid Email address"}'/>
      </div>
      <div class="col-xs-12 col-sm-6">
        <snh-form-field
          snh-form="form1"
          snh-model="c.data.textarea"
          snh-name="textarea"
          snh-label="Text Area"
          snh-type="textarea"
          maxlength="20"
          snh-help="This is where the field-level help is displayed."/>
        <snh-form-field
          snh-form="form1"
          snh-model="c.data.choicelist"
          snh-name="choicelist"
          snh-label="Choice List"
          snh-type="choicelist"
          snh-required="true"
          snh-choices='[{"value":"1", "label":"Choice #1"},{"value":"2", "label":"Choice #2"},{"value":"3", "label":"Choice #3"},{"value":"4", "label":"Choice #4"}]'/>
        <snh-form-field
          snh-form="form1"
          snh-model="c.data.radio"
          snh-name="radio"
          snh-label="Radio"
          snh-type="radio"
          snh-required="true"
          snh-choices='[{"value":"1", "label":"Choice #1"},{"value":"2", "label":"Choice #2"},{"value":"3", "label":"Choice #3"},{"value":"4", "label":"Choice #4"}]'/>
        <snh-form-field
          snh-form="form1"
          snh-model="c.data.inlineradio"
          snh-name="inlineradio"
          snh-label="In-line Radio"
          snh-type="inlineradio"
          snh-required="true"
          snh-choices='[{"value":"1", "label":"Choice #1"},{"value":"2", "label":"Choice #2"},{"value":"3", "label":"Choice #3"},{"value":"4", "label":"Choice #4"}]'/>
      </div>
    </div>
    <div class="row">
      <div class="col-sm-12">
        <snh-form-field
          snh-form="form1"
          snh-model="c.data.comments"
          snh-name="comments"
          snh-label="Comments"
          snh-type="textarea"/>
      </div>
    </div>
  </form>
</snh-panel>

And here is the screen shot of the latest version of the test page with the examples of the three new field types:

Form field tester with choice list and radio fields

Of course, that still leaves a lot to be tested, and even more that could be added, but I am starting to think that we are finally at that point where there is more work that has been completed than there is left to be done. Still, there are quite a few items that still remain on the needs to be done list:

  • Fix the way validation messages are displayed when the message changes, but the valid state does not,
  • Figure out how to obtain the name of the form in the template function so that it doesn’t have to be passed in as an attribute,
  • Test all of the field types that are specified in the list of valid field types,
  • Add even more choices to the list of valid field types, and
  • Revisit the form-field-extras DIV that we left out, and see what we might be able to with that feature if we brought that back into play.

As far as additional types go, I was thinking about seeing what I could do with the sn-record-picker, but that sounds like it could be its own challenge. That’s an AngularJS provider as well, and I’m not exactly sure how that would work with a provider inside of a provider. But I guess that’s how you find those things out. But I think I better stick with some of the other loose ends that I’ve skipped over before I start out on yet another experiment into the unknown. Although, it would be interesting to try …

Service Portal Form Fields, Part IV

“All progress is precarious, and the solution of one problem brings us face to face with another problem.”
Martin Luther King, Jr.

Some things I really like, and others not so much. I like the standard look and feel that you get when you use the same template for everything, and I really like the way that the required marker works without even leaving the field, but I’m not quite sold on the way that the field validation works just yet. I think that could use a little work somewhere, although I am not exactly sure where.

This is the line that triggers the validation:

scope.$watch(fullName + '.$valid', function (isValid) {

Basically, we are watching the $valid property of the field, and then updating the messages based on the $errors that are present for that particular field. Unfortunately, if you have a field with multiple validations, such as a required field with a minimum length, the field is not $valid when it is empty, and it is also not $valid when it is too short. When you start typing in the field, the required field error message does not get replaced with the field too short error message because the state never changed from not being $valid. This leaves a misleading error message underneath that field, which I don’t really care for. I may have to adjust my approach on that to work around that problem. But that’s not really today’s issue, so I’m going to have to set that aside for now.

What I really want to do is test what I have built so far. To expand my testing, I added a few more supported types, but I haven’t really tried them all just yet. Right now, my static list looks like this:

var SUPPORTED_TYPE = ['date', 'datetime-local', 'email', 'month', 'number', 'password', 'tel', 'text', 'textarea', 'time', 'url', 'week'];

Although I haven’t tried testing them all, I did expand my test page just a little to try a few more things out. I added some additional attributes to make sure that those got passed in OK, I added some custom error messages to verify that that process was working as it should, and then added an email field to see how that new type rendered out as well. Here is the latest version of that test page:

<snh-panel title="'${Form Field Test}'">
  <form id="form1" name="form1" novalidate>
    <div class="row">
      <div class="col-xs-12 col-sm-6">
        <snh-form-field
          snh-form="form1"
          snh-model="c.data.firstName"
          snh-name="firstName"
          snh-label="First Name"
          snh-type="text"
          snh-required="true"
          snh-messages='{"required":"You must enter a First Name"}'/>
        <snh-form-field snh-form="form1"
          snh-model="c.data.middleName"
          snh-name="middleName"
          snh-label="Middle Name"
          snh-type="text"
          snh-required="false" />
        <snh-form-field
          snh-form="form1"
          snh-model="c.data.lastName"
          snh-name="lastName"
          snh-label="Last Name"
          snh-type="text"
          snh-required="true"
          minlength="5"/>
      </div>
      <div class="col-xs-12 col-sm-6">
        <snh-form-field
          snh-form="form1"
          snh-model="c.data.textarea"
          snh-name="textarea"
          snh-label="Text Area"
          snh-type="textarea"
          maxlength="20"
          snh-help="This is where the field-level help is displayed."/>
        <snh-form-field
          snh-form="form1"
          snh-model="c.data.email"
          snh-name="email"
          snh-label="Email Address"
          snh-type="email"
          snh-required="true"
          snh-messages='{"required":"You must enter an Email address", "email":"You must enter a valid Email address"}'/>
      </div>
    </div>
    <div class="row">
      <div class="col-sm-12">
        <snh-form-field
          snh-form="form1"
          snh-model="c.data.comments"
          snh-name="comments"
          snh-label="Comments"
          snh-type="textarea"/>
      </div>
    </div>
  </form>
</snh-panel>

… and here is how it renders out, after I triggered a few of the validation errors:

Form Field Validation Test

For the most part, I am still very pleased with the results so far, but I am definitely going to have to make some adjustments in the validation message area to clean up the way that works. There is still much to be done, but I am getting closer to having a somewhat finished product.

Service Portal Form Fields, Part III

“For the great doesn’t happen through impulse alone, and is a succession of little things that are brought together.”
Vincent van Gogh

It’s only just the very beginnings, but I am really starting to like how things are turning out, at least so far. There is a lot left to do, and so much so that it is difficult to decide on what should be tackled next, but at least things are starting to take shape, and you can begin to see where things might lead. If you were paying close attention, you will notice that my initial HTML structure left out a couple of key elements from the example that we lifted from the Incident form. One is the hidden INPUT element that contains the original value and the other is the DIV element that contains all of the “extras” — that collection of little icons to the right of the INPUT element that do things like pop up the details for a reference field or provide some other functionality related to the value of the field. The first I skipped because I wasn’t exactly sure what value that provided in the AngularJS world, and the second I skipped because I’m not exactly sure how I would support that feature at this point. I may go back to one or both of those at some point, but for now, I’ve left those out of this little experiment.

So, the question remains: where do we go from here? Although I’m seriously tempted to start adding support for additional input types beyond text and textarea, I think I will defer that for now and start digging into field validation. I’ve been doing a little reading on HTML5 form validation and AngularJS form validation, and it appears that you have to make a conscious decision to go one way or another. Not being that familiar with either one, it will be a learning experience for me either way, so I thought briefly about just flipping a coin just to get the deciding part over with. After further review, though, I decided to go the AngularJS route, mainly because I need to learn a lot more about AngularJS anyway.

As usual, I hunt down the easy parts first and get those out of the way. There are a handful of static values that I want to use for reference purposes, so I came up with those first:

var SUPPORTED_TYPE = ['text', 'textarea'];
var RESERVED_ATTR = ['ng-model', 'class', 'id', 'name', 'required'];
var STD_MESSAGE = {
	email: "Please enter a valid email address",
	max: "Please enter a smaller number",
	maxlength: "Please enter fewer characters",
	min: "Please enter a larger number",
	minlength: "Please enter more characters",
	number: "Please enter a valid number",
	pattern: "Please enter a valid value",
	required: "This information is required",
	url: "Please enter a valid URL",
	date: "Please enter a valid date",
	datetimelocal: "Please enter a valid local date/time",
	time: "Please enter a valid time",
	week: "Please enter a valid week",
	month: "Please enter a valid month"
};

The first is an array of supported types, which right now just stands at text and textarea. I plan to expand on that in the future, and this array will help make those additions a little easier. The second is an array of reserved attributes, and the purpose of that one is to allow me to copy all of the attributes from the original snh-form-field element over to the generated input element, except those that are on that list (well, those that are on that list, plus any that start with snh- or $). That way, developers can specify any additional attributes and they will be carried forward as long as they are not already used in the processing of the directive. The last is a list of standard validation messages, which I also might expand on at some point in the future. I found that initial list of potential keys out on the Interwebs somewhere, and it looked like a good place to start, so I went ahead and grabbed it and tried to come with an appropriate message for each one.

.I already included the code that uses that first one in my last installment, but let’s reproduce it here, just to show how that is supposed to work:

var type = attrs.snhType || 'text';
type = type.toLowerCase();
if (SUPPORTED_TYPE.indexOf(type) == -1) {
	type = 'text';
}

Initially, we get the type from the snh-type attribute, or if you don’t provide one, we default it to text. Then, we convert it to lower case and check it against our array of supported types. If we don’t find it on the list, then again, we default it over to text. This ensures that we are always working with a valid, supported type.

The second array contains any attributes that might be used in our template, and we maintain that list so that we don’t pass along any conflicting values for any of those. When we copy over the remaining attributes, which we do in a function so that it can be called from multiple places, we use the list in much the same was as we did this first one, but this time we are not looking for the ones to keep, but rather the ones to avoid.

function passThroughAttributes(attrs) {
	var tags = '';
	for (var name in attrs) {
		if (name.indexOf('snh') != 0 && name.indexOf('$') != 0 && RESERVED_ATTR.indexOf(name) == -1) {
			tags += ' ' + name + '="' + attrs[name] + '"';
		}
	}
	return tags;
}

Basically, if it doesn’t start with snh and it doesn’t start with $ and it isn’t found on the list, then we go ahead and pass it through to the generated INPUT field, whatever that happens to be.

The last item is not an array, but a simple object, or data map, of the standard messages to use for the known list of possible validation error key values. I created an attribute called snh-messages so that developers could override these messages, but the basic idea here that there is a default message for everything, and if that’s good enough for your purposes, then there is nothing more for you to have to do.

for (var key in elem.$error) {
	elem.validationErrors += separator;
	if (overrides[key]) {
		elem.validationErrors += overrides[key];
	} else if (STD_MESSAGE[key]) {
		elem.validationErrors += STD_MESSAGE[key];
	} else {
		elem.validationErrors += 'Undefined field validation error: ' + key;
	}
	separator = '<br/>';
}

The above code loops through all of the validation errors, and if an override value was provided, that is the message that will be used. If not, and there is a standard message available, then that is the message that will be used. If there is no override and no standard message, then we report the fact that we do not have a message to go along with that particular error, and include the error key.

So, the full thing looks like this at this point:

function() {
	var SUPPORTED_TYPE = ['text','textarea'];
	var RESERVED_ATTR = ['ng-model', 'class', 'id', 'name', 'required'];
	var STD_MESSAGE = {
		email: "Please enter a valid email address",
		max: "Please enter a smaller number",
		maxlength: "Please enter fewer characters",
		min: "Please enter a larger number",
		minlength: "Please enter more characters",
		number: "Please enter a valid number",
		pattern: "Please enter a valid value",
		required: "This information is required",
		url: "Please enter a valid URL",
		date: "Please enter a valid date",
		datetimelocal: "Please enter a valid local date/time",
		time: "Please enter a valid time",
		week: "Please enter a valid week",
		month: "Please enter a valid month"
	};
	return {
		restrict: 'E',
		replace: true,
		require: ['^form'],
		template: function(element, attrs) {
			var htmlText = '';
			var form = attrs.snhForm;
			var name = attrs.snhName;
			var fullName = form + '.' + name;
			var type = attrs.snhType || 'text';
			type = type.toLowerCase();
			if (SUPPORTED_TYPE.indexOf(type) == -1) {
				type = 'text';
			}
			var required = attrs.snhRequired && attrs.snhRequired.toLowerCase() == 'true';
			var model = attrs.snhModel;
			var value = '';
			htmlText += "    <div id=\"element." + name + "\" class=\"form-group\">\n";
			htmlText += "      <div id=\"label." + name + "\" class=\"snh-label\" nowrap=\"true\">\n";
			htmlText += "        <label for=\"" + name + "\" class=\"col-xs-12 col-md-4 col-lg-6 control-label\">\n";
			htmlText += "          <span id=\"status." + name + "\"";
			if (required) {
				htmlText += " ng-class=\"" + model + ">''?'snh-required-filled':'snh-required'\"";
			}
			htmlText += "></span>\n";
			htmlText += "          <span title=\"" + attrs.snhLabel + "\" data-original-title=\"" + attrs.snhLabel + "\">" + attrs.snhLabel + "</span>\n";
			htmlText += "        </label>\n";
			htmlText += "      </div>\n";
			if (attrs.snhHelp) {
				htmlText += "    <div id=\"help." + name + "\" class=\"snh-help\">" + attrs.snhHelp + "</div>\n";
			}
			if (type == 'textarea') {
				htmlText += "      <textarea class=\"form-control\" ng-model=\"" + model + "\" id=\"" + name + "\" name=\"" + name + "\"" + passThroughAttributes(attrs) + (required?' required':'') + "></textarea>\n";
			} else {
				htmlText += "      <input class=\"form-control\" ng-model=\"" + model + "\" id=\"" + name + "\" name=\"" + name + "\" type=\"" + type + "\"" + passThroughAttributes(attrs) + (required?' required':'') + "/>\n";
			}
			htmlText += "      <div id=\"message." + name + "\" ng-show=\"" + fullName + ".$touched && " + fullName + ".$invalid\" class=\"snh-error\">{{" + fullName + ".validationErrors}}</div>\n";
			htmlText += "     </div>\n";
			return htmlText;

			function passThroughAttributes(attrs) {
				var tags = '';
				for (var name in attrs) {
					if (name.indexOf('snh') != 0 && name.indexOf('$') != 0 && RESERVED_ATTR.indexOf(name) == -1) {
						tags += ' ' + name + '="' + attrs[name] + '"';
					}
				}

				return tags;
			}
		},
		link: function(scope, element, attrs, ctls) {
			var form = ctls[0].$name;
			var name = attrs.snhName;
			var fullName = form + '.' + name;
			var overrides = {};
			if (attrs.snhMessages) {
				overrides = JSON.parse(attrs.snhMessages);
			}
			scope.$watch(fullName + '.$valid', function (isValid) {
				var elem = scope.$eval(fullName);
				elem.validationErrors = '';
				var separator = '';
				if (!isValid) {
					for (var key in elem.$error) {
						elem.validationErrors += separator;
						if (overrides[key]) {
							elem.validationErrors += overrides[key];
						} else if (STD_MESSAGE[key]) {
							elem.validationErrors += STD_MESSAGE[key];
						} else {
							elem.validationErrors += 'Undefined field validation error: ' + key;
						}
						separator = '<br/>';
					}
				}
			});
		}
	};
}

One thing that I had to do temporarily was to pass the name of the form to the template as an attribute. I don’t think that I should have to do that, and I really don’t like it, but I spent way too much time trying to figure out how to pick that up from the element itself with no success, so I put that aside for now and just passed it in as another attribute. I don’t like that, though, and I really don’t think that that should be necessary, but for now, I have put that aside for another day and intend to go back to it at some point and make that right.

There is still a lot more to do, but it is starting to come together. Next time, I think I will do a little testing to make sure all of this works, and then maybe explore supporting a few more different type values.

Service Portal Form Fields, Part II

“The journey of a thousand miles begins with a single step.”
— Lao Tzu (Tao te Ching)

Last week, I had this idea, and today I think I have a fairly good plan. I am going to make a copy of my old sn-panel hack and make myself another Angular Provider for a generic form field and all of the associated trimmings. My strategy, as usual, is to start out small see what I can get to work, and then build it up piece by piece until it finally does everything that I want it to do. I’ll use the tag snh-form-field to invoke the provider, and then I will create my own snh-* attributes and snh-* styles, just to be sure that I don’t conflict with anything already existing in the platform. Ultimately, I want to be able to support any kind of valid HTML5 input, but to start with, I’m going to limit the initial version to just text and textarea fields. At this point, I don’t want to drive things off of the dictionary; I see that as something that would live outside of this process. Everything will be driven off of the attributes specified in the tag, and if I ever do add something driven off of the dictionary, it will simply leverage this work and use the dictionary to determine what the attribute values should be.

To begin, let’s start out with the basic template. This is pretty much the heart and soul of the entire operation, so might as well start out with the basic HTML framework to be used for all types of input. Here is my first attempt at the essential code needed to produce the simplest of structures to include all of the standard elements:

var htmlText = '';
var name = attrs.snhName;
var model = attrs.snhModel;
var type = attrs.snhType || 'text';
type = type.toLowerCase();
if (SUPPORTED_TYPE.indexOf(type) == -1) {
	type = 'text';
}
var required = attrs.snhRequired && attrs.snhRequired.toLowerCase() == 'true';
htmlText += "    <div id=\"element." + name + "\" class=\"form-group\">\n";
htmlText += "      <div data-type=\"label\" id=\"label." + name + "\" nowrap=\"true\">\n";
htmlText += "        <label for=\"" + name + "\" class=\"col-xs-12 col-md-4 col-lg-6 control-label\">\n";
htmlText += "          <span id=\"status." + name + "\"";
if (required) {
	htmlText += " ng-class=\"" + model + ">''?'snh-required-filled':'snh-required'\"";
}
htmlText += "></span>\n";
htmlText += "          <span title=\"" + attrs.snhLabel + "\" data-original-title=\"" + attrs.snhLabel + "\">" + attrs.snhLabel + "</span>\n";
htmlText += "        </label>\n";
htmlText += "      </div>\n";
if (attrs.snhHelp) {
	htmlText += "    <div id=\"help." + name + "\" class=\"snh-help\">" + attrs.snhHelp + "</div>\n";
}
if (type == 'textarea') {
	htmlText += "      <textarea class=\"form-control\" ng-model=\"" + model + "\" id=\"" + name + "\" name=\"" + name + "\"" + (required?' required':'') + "></textarea>\n";
} else {
	htmlText += "      <input class=\"form-control\" ng-model=\"" + model + "\" id=\"" + name + "\" name=\"" + name + "\" type=\"" + type + "\"" + (required?' required':'') + "/>\n";
}
htmlText += "      <div id=\"message." + name + "\" class=\"snh-error-msg\" ng-show=\"" + model + "ErrorMsg>''\">{{" + model + "ErrorMsg}}</div>\n";
htmlText += "     </div>\n";
return htmlText;

The first thing that you see is that we pull some commonly used variables out the associated attributes, mainly for the sake of clarity and brevity. The first real line of HTML is the outer enclosing DIV of everything else, which we name in accordance with the standard as “element.” plus the “name” of the field. The next line is the outer enclosing DIV of field label, which we name in accordance with the standard as “label.” plus the “name” of the field. Next is the LABEL tag, which doesn’t have a name, followed by the SPAN for the required marker, which we name in accordance with the standard as “status.” plus the “name” of the field. After that comes the SPAN for the field label, which also doesn’t have a name, but uses the snh-label attribute for the title as well as the value. Following the label SPAN is the closing LABEL tag and then the closing DIV tag for the field label elements, completing the field label block.

Just below the field label there is an optional DIV for field-level help. You don’t see that all that much, but it is an option on ServiceNow forms, so I wanted to be sure to include it. After that comes the INPUT element itself, the formatting of which is entirely dependent on the type of input. For now, just to have something to start with, I am only supporting two different types, text and textarea. We’ll add more later, but this will be enough to get us started. After the INPUT element, we have the DIV for validation errors, and then we wrap everything up with the closing DIV tag for the outer enclosure. That completes the basic HTML structure for a form field.

To try it out, we’ll need a simple widget with a few form fields defined. Just to see how things come out, let’s do a few text fields and a few textarea fields, make some required and some not required. And just for fun, let’s wrap the whole thing in the old snh-panel.

<snh-panel rect="rect" title="'${Form Field Test}'">
  <form role="form" id="form1" ng-init="checkInputs()">
    <div class="row">
      <div class="col-xs-12 col-sm-6">
        <snh-form-field snh-model="c.data.firstName" snh-name="firstName" snh-label="First Name" snh-type="text" snh-required="true" />
        <snh-form-field snh-model="c.data.middleName" snh-name="middleName" snh-label="Middle Name" snh-type="text" snh-required="false" />
        <snh-form-field snh-model="c.data.lastName" snh-name="lastName" snh-label="Last Name" snh-type="text" snh-required="true" />
      </div>
      <div class="col-xs-12 col-sm-6">
        <snh-form-field snh-model="c.data.textarea" snh-name="textarea" snh-label="Text Area" snh-type="textarea"/>
      </div>
    </div>
    <div class="row">
      <div class="col-sm-12">
        <snh-form-field snh-model="c.data.comments" snh-name="comments" snh-label="Comments" snh-type="textarea"/>
      </div>
    </div>
  </form>
</snh-panel>

Of course, we have to build a Portal Page so that we have somewhere to put our widget, but that’s all basic ServiceNow stuff that we don’t need to go into here. Let’s just assume that we did all that and get right to the good stuff, which is to see how it all comes out when you bring up in the browser.

First Form Field Test

Not bad! The required marker actually works pretty cool … the minute that you type a single letter into the field, it goes from red to grey. I’m used to that changing once you leave the field, but this does it right then and there while you are entering the data. The form doesn’t validate just yet, but hey — it’s a start. I think I am going to really like this once it has all been put together. Next time, we will add a little more and see just how much further we can get …

Service Portal Form Fields

“Everything begins with an idea.”
Earl Nightingale

Even though I tend to prefer the Service Portal environment over the original ServiceNow UI, the one thing that you just can’t beat on the original side of the house is the built-in support for form fields. As soon as you create a Table in ServiceNow, a corresponding form is also created, built using a generic infrastructure that is driven off of the embedded data dictionary. Every field on the form is formatted in a consistent way using consistent components supporting consistent features. So far, I have not seen anything comparable to that in the Service Portal environment.

On a standard ServiceNow UI form, every field on the page will have the same set of standard elements based on the same basic template, customized to the specifications of the specific field as contained in the data dictionary. For example, here is the HTML from the Incident ID field on the Incident form:

   <div id="element.incident.number" class="form-group " style="">
      <div class="" data-type="label" choice="0" type="string" id="label.incident.number" nowrap="true"><label onclick="return labelClicked(this);" for="incident.number" dir="ltr" class=" col-xs-12 col-md-3 col-lg-4 control-label"><span id="status.incident.number" data-dynamic-title="" mandatory="false" oclass="" class=" label_description" aria-label=""></span><span title="" class="label-text" data-html="false" data-original-title="">Number</span></label></div>
      <div class="col-xs-10 col-sm-9 col-md-6 col-lg-5 form-field input_controls">
         <div ng-non-bindable="" class="hidden"><input id="sys_original.incident.number" name="sys_original.incident.number" value="INC0010037" type="hidden"></div>
         <input id="incident.number" name="incident.number" aria-required="false" onchange="onChange('incident.number');" maxlength="40" value="INC0010037" style="; " autocomplete="off" ng-non-bindable="" class="form-control" spellcheck="false" type="text">
      </div>
      <div class="col-xs-2 col-sm-3 col-lg-2 form-field-addons"></div>
   </div>

Looking through the code, you can spot a number of unique standard elements, each with its own specific purpose, and many with a standard ID:

  • element.incident.number
  • label.incident.number
  • status.incident.number
  • sys_original.incident.number
  • incident.number

So, in theory, it seems as if you could use the above block of HTML as a guide and set up some kind of AngularJS component to replicate that entire concept over on the Service Portal side of the house. Unfortunately, I’m not all that familiar with AngularJS, but it sounds like an interesting challenge. Who knows … maybe someone has already put this together. Surely someone smarter than I am has already thought of doing this, and maybe has already figured all of it out. Even if that’s true, it would still be fun to try on my own. You know what I always ask myself: how hard could it be?

Portal Widgets on UI Pages

“Our life is frittered away by detail. Simplify, simplify.”
Henry David Thoreau

There are two distinctly different environments on ServiceNow, the original ServiceNow UI based on Lists, Forms, and UI Pages, and the more recent Service Portal environment based on Widgets and Portal Pages. The foundation technology for UI Pages is Apache Jelly. The foundation technology for Service Portal Widgets is AngularJS. From a technology perspective, the two really aren’t all that compatible; when you are working with the legacy UI, you work in one technology and when you are working with the Service Portal, you work in the other.

Personally, I like AngularJS better than Jelly, although I have to admit that I am no expert in either one. Still, given the choice, my preferences always seem to tilt more towards the AngularJS side of the scale. So when I had to build a modal pop-up for a UI Page, my inclination was to find a way to do it using a Service Portal Widget. To be completely honest, I wanted to find a way to be able to always use a widget for any modal pop-up on the ServiceNow UI side of things. That way, I could hone my expertise on just one approach, and be able to use it on both sides of the house.

The solution actually turned out to be quite simple: I created a very simple UI Page that contained just a single element, an iframe. I passed just one parameter to the page, and that was the URL that was to be the source for the iframe. Now, that URL could contain multiple URL parameters to be passed to the interior widget, but from the perspective of the UI Page itself, all you are passing in is the lone URL, which is then used to establish the value of the src attribute of the iframe tag. There are two simple parts to the page, the HTML and the associated script. Here is the HTML:

<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
	<iframe id="portal_page_frame" style="height: 400px; width: 100%; border: none;"></iframe>
</j:jelly>

… and here is the script:

var gdw = GlideDialogWindow.get();
var url = gdw.getPreference('url');
document.getElementById('portal_page_frame').src = url;

The script simply gets a handle on the GlideDialogWindow so that it can snag the URL “preference”, and then uses that value to establish the source of the iframe via the iframe’s src attribute. To pop open a Service Portal Widget using this lightweight portal_page_container, you would use something like the following:

var dialog = new GlideDialogWindow("portal_page_container");
dialog.setPreference('url', '/path/to/my/widget');
dialog.render();

That’s it. Simple. Easy. Quick. Just the way I like it! If you want a copy to play with, you can get it here.

Update: There is an even better way to do this, which you can find here.

Service Portal Widget Help

“Some people think design means how it looks. But of course, if you dig deeper, it’s really how it works.”
Steve Jobs

The other day I was trying to remember how to do something on the Service Portal, and it occurred to me that it would be nice to have a little pop-up help screen on all of my various custom widgets so that I could put a little help text behind each one in a consistent manner. There is already an Angular Provider called snPanel that provides a set of stock wrappings for a widget, so it seemed like it wouldn’t take too much to add a little something to that guy to put some kind of Help icon in a consistent place on the title bar, say a question mark in the upper right-hand corner:

Possible User Help Icon

Of course, placing the icon on the screen is just the start; setting it up to do something in a way that was useful and easy to maintain would be the bulk of the work. A modal dialogue seemed to be the best vehicle to deliver the content, but the content would need to come from somewhere, and be somehow linked to the widget in a way that did not require extra code or special logic. After considering a number of various alternatives, I decided to create a new Knowledge Base called User Help for the specific purpose of housing the content for my new help infrastructure. I didn’t want to have to do anything special to a widget in order for the help to be available, so my plan was to use the Title property of the Knowledge Article to store the name or ID of the widget, linking the article directly to the widget without having to store any kind of help ID on the widget itself. This would allow me to create help text for any widget by simply putting some kind of link to the existing widget in the title of the article, which would not actually appear on the screen in my proposed help text modal pop-up window.

So for now, let’s just assume that we can modify the snPanel to include a link to a modal pop-up and focus on the widget that we will use to display the help content. To make ongoing maintenance easier, in addition to the help text itself, it would also be quite handy to have a link to the editor on the screen for those with the power to edit the help text. The link would be slightly different depending on whether or not there was already some help text in existence for this widget, but that’s easy enough to handle with a little bit of standard widget HTML:

<div ng-bind-html="c.data.html"></div>
<div ng-show="c.data.contentEditor && c.data.sysId" style="padding: 20px; text-align: center;">
  <a class="btn btn-primary" href="/nav_to.do?uri=%2Fkb_knowledge.do%3Fsys_id%3D{{c.data.sysId}}" target="_blank">Edit this Help content</a>
</div>
<div ng-show="c.data.contentEditor && !c.data.sysId" style="padding: 20px; text-align: center;">
  <a class="btn btn-primary" href="/nav_to.do?uri=%2Fkb_knowledge.do%3Fsys_id%3D-1%26sysparm_query%3Dkb_knowledge_base%3D{{c.data.defaultKnowledgeBase}}%5Ekb_category%3D{{c.data.defaultCategory}}%5Eshort_description%3D{{c.data.id}}" target="_blank">Create Help content for this widget</a>
</div>

The first DIV is for the content and the next two are mutually exclusive, if they even show up at all. If you are not a content editor, you won’t see either one, but if you are, you will see the Edit this Help content link if help text exists for this widget and the Create Help content for this widget link if it does not.

We shouldn’t need any client side code at all for this simple widget, and the server side should be fairly simple as well: go fetch the help content and figure out if the current user can edit content or not. For our little example, let’s just limit editing to users with the admin role, and assume that the default knowledge base and default category are both called User Help.

(function() {
	if (!data.defaultKnowledgeBase) {
		fetchDefaultKnowledgeBase();
	}
	if (!data.defaultCategory) {
		fetchDefaultCategory();
	}
	data.contentEditor = false;
	if (gs.hasRole('admin')) {
		data.contentEditor = true;
	}
	if (input && input.id) {
		data.id = input.id;
		if (!data.html) {
			data.html = fetchHelpText();
		}
	}

	function fetchHelpText() {
		data.sysId = false;
		var html = '<p>There is no Help available for this function.</p>';
		var help = new GlideRecord('kb_knowledge');
		help.addQuery('kb_knowledge_base', data.defaultKnowledgeBase);
		help.addQuery('kb_category', data.defaultCategory);
		help.addQuery('short_description', data.id);
		help.addQuery('workflow_state', 'published');
		help.query();
		if (help.next()) {
			data.sysId = help.getValue('sys_id');
			html = help.getDisplayValue('text');
		} 
		return html;
	}

	function fetchDefaultKnowledgeBase() {
		var gr = new GlideRecord('kb_knowledge_base');
		gr.addQuery('title', 'User Help');
		gr.query();
		if (gr.next()) {
			data.defaultKnowledgeBase = gr.getValue('sys_id');
		} 
	}

	function fetchDefaultCategory() {
		var gr = new GlideRecord('kb_category');
		gr.addQuery('kb_knowledge_base', data.defaultKnowledgeBase);
		gr.addQuery('label', 'User Help');
		gr.query();
		if (gr.next()) {
			data.defaultCategory = gr.getValue('sys_id');
		} 
	}
})();

The script includes three independent functions, one to fetch the sys_id of the default knowledge base, one to fetch the sys_id of the default category, and one to use those two bits of data, plus the widget‘s ID, to fetch the help content. There are three hard-coded values in this example that would be excellent candidates for System Properties: the Knowledge Base, the Knowledge Category, and Role or Roles used to identify content editors. For now, though, I just plugged in specific values to simplify the example. That’s pretty much it for the pop-up modal dialogue’s widget. Now we just need to hack up the snPanel code to wedge in our link. We should be able to do that by inserting another bit of HTML inside of the h2 heading tag:

<div class="pull-right">
  <a href class="h4" title="Click here for Help" ng-click="widgetHelp()">
    <span class="m-r-sm fa ng-scope fa-question"></span>
  </a>
</div>

The ng-click value in the anchor tag references a scoped function, so we’ll need to add a controller to the provider so that we can insert the code for that function. This is the code that will run whenever someone clicks on our new fa-question icon.

controller: function($scope, spModal) {
	$scope.widgetHelp = function() {
		spModal.open({
			title: 'User Help',
			widget: 'snh-help',
			widgetInput: {id: $scope.widget.id},
			buttons: [
				{label: 'Close', primary: true}
			],
			size: 'lg'
		});
	};
},

The function basically contains a single line of code, which is your standard spModal open command using all of the usual parameters, plus passing in the widget‘s ID as widgetInput, which will be used as the key to retrieve the associated help text from the default Knowledge Base. This actually turned out to be a little more of a modification than it would care to make to a standard ServiceNow component such as snPanel, so I ended up creating a copy of my own and producing the snhPanel, which can now be used anywhere that snPanel can be used. All told, we created one widget and one Angular Provider to make all of this work, and then configured a Knowledge Base and Knowledge Category to house the help text content created through this new user help infrastructure. There are only a couple of parts to this one, but if anyone is interested, here is an Update Set that contains both of them.

Yet Even More Fun with JDBC Data Sources

“We have a strategic plan; it’s called doing things.”
Herb Kelleher

Recently I had the need to write to an external database on demand in much the same way as I had been reading from an external database on demand earlier. I was able to hack the Scheduled Import process to fetch a specific record, so my thought was that there must be some kind of equivalent Scheduled Export process that I might be able to bend to my will to satisfy my current requirement. Unfortunately, the Scheduled Export process is based on dropping a file somewhere, not on writing to an external database. You have a lot of options as to what type of file it is, but it is still just a file, and I wanted to write to a database table.

So, then I looked into lighting up the JDBCProbe, which I did without too much difficulty, but after digging into all of its various capabilities, I realized that it really didn’t have that much more to offer than the techniques that I had already been using. You still have to create a Data Source, you still have to configure the job to run, you still have to launch the job, and you still have to monitor the job for completion and successful execution. Since I had already pretty much worked all of that out with the Scheduled Import process, decided that I would just stick with that.

The difference, of course, was that this time I wanted to write, not read. Fortunately, that problem can be solved with SQL, and nothing else about my original hack really needed to change. Scheduled Imports expect the end result of the SQL execution to be a Result Set, which is the outcome of an SQL query. However, a query is not the only way to produce a Result Set. SQL Insert and Update statements can also produce a Result Set when you use the Output clause. The Output clause returns specified values such as a generated Identity key back in the form of a Result Set. By leveraging the Output clause, you can build an SQL statement that will both update the external database and return a Result Set all in one operation.

Consider the following example SQL:

INSERT
   EMPLOYEE,
   INCIDENT_ID,
   DATE,
   HOURS_WORKED
OUTPUT
   INSERTED.ID,
   INSERTED.EMPLOYEE,
   INSERTED.INCIDENT_ID,
   INSERTED.DATE,
   INSERTED.HOURS_WORKED
INTO
   TIME_CARD
VALUES(
   'snhackery',
   'INC0000456',
   getdate(),
   6.5)

This SQL statement will insert the specified values into the external database table, and then return the inserted values, along with the newly generated ID on the external table, back to ServiceNow in the form of a Result Set. To the Scheduled Import job, this response is indistinguishable from the response received from a standard SELECT statement. Now, you still have to have a somewhat useless table on the ServiceNow end of things to receive the returned data, even though all you really wanted to do was to write to a table in a remote database, but that table can serve as a log of all records written out to the external destination.

It is possible that we could have accomplished this without the need for the local ServiceNow table by utilizing the JDBCProbe, but this happens to work, so that’s good enough to satisfy Rule #1.

Scoped Application Properties, Part IV

“Any intelligent fool can make things bigger and more complex… It takes a touch of genius – and a lot of courage to move in the opposite direction.”
Albert Einstein

In my haste to wrap things up last week, I neglected to actually create an application-specific System Property and then verify that the convenience link and menu item that we created actually worked as intended. I also neglected to post the Update Set containing all of the artifacts related to this adventure, so I have decided that a fourth installment in this series is warranted. To begin, let’s return to where we last off, which was to push the new Setup Properties button to produce all of the application components necessary to manage properties specific to the application. We can scroll down to the bottom of the page now and see the results:

Sample Application page after clicking on the Setup Properties button

The first thing that you will notice is that the Setup Properties button is now gone. Having completed its reason for living, it can now go away permanently, never to be seen or heard from again until we create our next scoped application. Our other UI Action, the link to the System Properties maintenance page, is not visible either, though. That’s just because we have yet to create our first System Property for this application. Let’s fix that by clicking on that New button under the System Properties tab. And just to show off our Reference type System Properties hack, let’s go ahead and make it a Reference property.

Creating a new application-specific property

To create the new property, we give it a Name and a Description, select reference as the Type and then select the sys_user_group table as the Table. Once we have completed the form, we can now click on the Submit button, which will return us to the original Sample App definition page where we can see the effect of adding the first property to the application

Related Links with at least one System Property defined

Now our new UI Action appears, as there is at least one System Property to manage. Clicking on the link will take us to the page where all of the properties for this application can be valued.

Application Property maintenance page

Of course, we also put this same link on the left-hand side bar menu, so we can type Sample App in the navigation filter to bring that one up to verify as well.

Using the left-hand navigation to open up the Application Properties page

That pretty much verifies that all of the parts and pieces are actually doing their respective jobs. Now all that is left is to leave you with that Update Set.