Upland OL User community

Query a column and validate characters

#1

I would like to have Watch look down a specific column range on a page of inbound data and send me an email if ANYTHING other than a predefined list of valid characters appears in that column. As an example: if the data looked like this below and I had defined 1,4,6,7,11,15,21,22 as valid characters in columns 1-2, I could branch to a PP Image process and send the data file to an Email since 5 appeared in that column and it was not defined as valid.
GL LOC’S OPEN ORDER
GL LINES
== ======
1 1630
4 995
5 1547
6 1058
7 21
11 246
15 149
21 23
22 1415

best way to approach this?
Duane

#2

Use a script to check the file.

First, create a process variable named ValidChars that stores all valid values in a pipe-delimited format:
image
Make sure that this value starts and ends with a pipe character (|) and that there are no blank spaces in there.

Then, use the following JavaScript code:

var validChars = Watch.GetVariable("ValidChars");
var fso = new ActiveXObject("Scripting.FileSystemObject");
var file = fso.OpenTextFile(Watch.GetJobFileName(),1);
var result = true;

var LINES_TO_SKIP = 3;
var index = 0

while(!file.AtEndOfStream && result){
  var oneLine = file.ReadLine();
  index++;
  if(LINES_TO_SKIP) {
    LINES_TO_SKIP--;
  } else {
    var prefix = oneLine.substring(0,2).replace(/\s/gi,"");
    result = validChars.indexOf("|"+prefix+"|")!==-1;
    if(!result) Watch.log("Found invalid char: "+prefix+" on line "+index,2);
  }
}
file.Close()
Watch.SetJobInfo(8,result?"OK":"NOT OK");

The script reads the file line by line. It first skips the number of lines specified in the LINES_TO_SKIP variable, then it checks if the line prefix is found in the value of the ValidChars variable. If it is, the next line is processed. If not, it logs a message and stops processing the file.

The script then sets the value OK or NOT OK in JobInfo 8, which you can use later in your process to determine whether to send an email.

#3

Phil, thank you VERY MUCH! I found a few missing Semi Colons, but other than that the script worked perfectly! Thank you again for your help.

#4

Ok, I am trying to get a little creative here and grab the Invalid Character and assign a Job Variable, but I obviously am doing it incorrectly. I have tried a few different ways and seem to come up with the same result. Here is my latest attempt. Please direct me in the correct method of grabbing the offending character.

var validChars = Watch.GetVariable("ValidChars");
var fso = new ActiveXObject("Scripting.FileSystemObject");
var file = fso.OpenTextFile(Watch.GetJobFileName(),1);
var result = true;

var LINES_TO_SKIP = 2;
var index = 0;

while(!file.AtEndOfStream && result){
  var oneLine = file.ReadLine();
  index++;
  if(LINES_TO_SKIP) {
    LINES_TO_SKIP--;
  } else {
    var prefix = oneLine.substring(0,2).replace(/\s/gi,"");
    result = validChars.indexOf("|"+prefix+"|")!==-1;
    if(!result) Watch.log("Found invalid char: "+prefix+" on line "+index,2);
    if(!result) Watch.SetJobInfo(9, "+prefix+");
  }
}
file.Close();
Watch.SetJobInfo(8,result?"OK":"NOT OK");
#5

It would help if you told us what result you’re getting…

#6

Sorry about that. So, what I am trying to pull is the value of the Invalid Character the script finds. Your line
if(!result) Watch.log("Found invalid char: “+prefix+” on line "+index,2);
produces “Found invalid character 29 at line 4” in the Watch log. So I would be trying to pull 29 in this case.
What ends up in the Job Info Variable %9 is +prefix+. I have tried it a few different ways and a few different places in the script, but I seem to always end up with the same result.
Sorry, I am trying to learn, but at this point I am not very good with Java.

#7

Change the line

if(!result) Watch.SetJobInfo(9, "+prefix+");

to

if(!result) Watch.SetJobInfo(9, prefix);
#8

GREAT. That worked. If you could help me learn from this, why are the + signs needed for the expressions in the previous line, but screw things up in the next line, seemingly pulling the same data point? I could not find anything in the Java Documents that reference bracketing a variable with + signs.

#9

First of all, this is JavaScript, not Java. They are two completely unrelated languages, other than using a similar syntax.

Second, the + sign is used to concatenate strings together (it’s also used to add numbers together, but that’s beside the point here).

So for instance, in the following piece of code, the variable sentence is a concatenation of three discrete values:

var something = "some value";
var sentence = "There is " + something + " to that statement"
// sentence now contains "There is some value to that statement"
  1. "There is "
  2. The value in variable something
  3. " to that statement"

But in the following code, the + sign is part of one of the discrete values (i.e. is is just another character inside the string), so it is not being used as an operator:

var something = "some value";
var sentence = "There is + something + to that statement"
// sentence now contains "There is + something + value to that statement"

So essentially, anything that is inside quotes is not evaluated by the engine, while elements outside quotes are language tokens that each have their own meaning.

#10

Thank you very much for the correction and the lesson. That helped alot.