Boundaries using 2 columns from CSV/XLSX, but only the first 5 character

OL Connect Professional DataMapper
1

Boundaries using a2 columns from CSV/XLSX, but only the first 5 characters from the first column.
Using the example, but can’t seem to only use the first 5 characters.

/* Read the values of both columns we want to ​check */
var zeRef = boundaries.get(region.createRegion("Ref#"));
var zeShip = boundaries.get(region.createRegion("Shipment Category"));

/* Check that at least one of our variables holding previous values has been initialized already, before attempting to compare the values */

if (boundaries.getVariable("lastRef")!=null) {
	if (zeRef[0] != boundaries.getVariable("lastRef") || zeShip[0] != boundaries.getVariable("lastShip") )
	{
	boundaries.set();
	}
}
boundaries.setVariable("lastRef",zeRef[0]);
boundaries.setVariable("lastShip",zeShip[0]);
2

You don’t have any code that look at the first 5 characters.

3

If I perform the substr within the IF, I get mostly what I’m looking for.
So partially there, but not getting the second bit correct though.

If the Ref# value (only the first 5 characters) match and the Shipment Category match, then they would all be in a record.

/* Read the values of both columns we want to ​check */
var zeRef = boundaries.get(region.createRegion("Ref#"));
var zeShip = boundaries.get(region.createRegion("Shipment Category"));

/* Check that at least one of our variables holding previous values has been initialized already, before attempting to compare the values */

if (boundaries.getVariable("lastRef")!=null) {
	if (zeRef[0].substr(1,5) != boundaries.getVariable("lastRef").substr(1,5) && zeShip[0] != boundaries.getVariable("lastShip") )
	{
	boundaries.set();
	}
}
boundaries.setVariable("lastRef",zeRef[0]);
boundaries.setVariable("lastShip",zeShip[0]);
4

If you are looking at 2 columns, it stands to reason that you want it to change if either is different…right?

Then it should by || not &&

if (boundaries.getVariable("lastRef")!=null) {
	if (zeRef[0].substr(1,5) != boundaries.getVariable("lastRef").substr(1,5) || zeShip[0] != boundaries.getVariable("lastShip") )
5

I want to set the boundaries if the first 5 characters of the Ref column are the same and then only the rows that also have the same secondary column.

6

Uomo,

You could also consider using a preprocessing script to add a “key” column to your data and then use regular On change boundaries on that new column.

For instance, the following code creates a new “KEY” column that contains the first five characters from the first field of the CSV.

var oneLine, key;
var inFile = openTextReader(data.filename);
var outFile = openTextWriter(data.filename+".csv");
var isHeader = true;
while(oneLine = inFile.readLine()){
  if(isHeader){
    isHeader=false;
    key = '"KEY",';
  } else {
    key = '"'+oneLine.slice(1,6)+'",';
  }
  outFile.write(key + oneLine +"\n");
}
inFile.close();
outFile.close();
copyFile(data.filename+".csv",data.filename);

You could adjust the script so that the KEY column contains a concatenation of both columns that need to be looked at.

7

Would this work the same way with an Excel *.xlsx file?

8

Nope. Sorry, forgot to mention that.

9

I will be receiving an excel file that I need to create a series of documents from.

Will eventually be separating them out and emailing the pieces to different recipients using a database query to get the email address.

10

Ok, so sticking with the scripted boundaries, then…

You don’t have to compare two values separately. Concatenate them and store the concatenated value into a single variable. That’s what you compare the next values to for each line of the file:

var fld1 = boundaries.get(region.createRegion("FIRSTFIELD"));
var fld2 = boundaries.get(region.createRegion("SECONDFIELD"));

var newRef = fld1[0].substr(1,5) +fld2[0];

if (boundaries.getVariable("lastRef")!=null) {
  if(newRef!==boundaries.getVariable("lastRef") ) {
    boundaries.set();
  }
}
boundaries.setVariable("lastRef",newRef);
1 Like
11

This seems to work perfectly with my small sample excel file.
I scared me for a second as initially it didn’t return any data.
I closed the datamapper and reopened it and it worked as expected.

I was in the process of attempting the same notion after your preprocessor post gave me the idea.