Tuesday, May 12, 2015

The DataIterator

I like the DataIterator, but it isn't always the best solution to use for iteration of data. To explain I will tell about a recent flow I needed to develop and the solution I came up with, part of which used the DI.

My client receives an item master file from GHX and they use it to update the Item Master (IC11) & Vendor Item (PO13) records, to update unreleased Requition line items (RQ10), and to update pending PO Interface line items (PO23).

We will update the description values, manufacture values, and vendor item number values for the items in the file. We are also updating the GL account on IC11 and the Purchasing Major and Minor Class values from another file.

The value joining the two files is the UNSPSC code value.

I used a File Access to load each file, and the first was a combination of line and tab separated values. Each item was separated by line but each data element of the item was separated by tab stops. It was recommended that I use a DataIterator to first parse the lines and then to parse the tab separated data elements (basically a nested pair of DI's).

If you're familiar with this, it works pretty much like a query to cycle through the data elements and I thought that was a bit clumsy. 

My solution? I used an Assign node - JavaScript Expression and treated the file's data like an array. It performed the exact same function, but was much faster. I first 'extracted' the data I needed and assigned it to a new variable (line and CSV data separated).

Remember, I had two files that I need to merge via UNSPSC codes before I could begin my updates in Lawson. One file had this value separated by the 4 parts while the other lumped them together. 

The 2nd file contained 49,023 lines of data and the thought of using the DataIterator to read through all of those records concerned me. I thought of the amount of time required and again decided to use the Assign - JavaScript Expression.

This process, reading through 49k lines, parsing and rewriting the output to a new variable took less than 4 minutes. That comes out to better than 200 lines of data per second. Remember, it had to iterate each record by line and then each line by its data element.

Now that I had two sets of records, ready to be merged (by UNSPSC value) I used another JavaScript Expression to compare, match and write the merged data into another new variable.

Then for each set of Lawson records to be updated I used a SQL query to retrieve my item records and wrote that to a new variable, which I then compared to the merged data to determine which had to be updated.

You guessed it, with an Assign - JavaScript Expression. So if the item description was different, or the vendor item, manufacturer info, etc. was different then I wrote which records required updating to a variable.

Now I had, for example, a list of items that needed updates done on IC11 in Lawson. That's where the DataIterator now came into play. I could iterate this final output by line and then create a transaction expression to use in an AGS update.

Using the DI at this point made sense since I needed to perform an update after each item record was parsed out. Within the DI loop was an Assign and a Lawson Transaction. 

My conclusion? If you need to perform a function while looping through the data, use a DataIterator. If not then using a JavaScript Expression is much more efficient.

Monday, April 13, 2015

What is change?

I've settled on a quick definition that lays out the four stage process that is change; "required, desired, intruding & improving."

Employees often perceive that change is being implemented without regard to the impact it will have on them. When this occurs, it is usually due to poor communication. 

We have not communicated the reasons, goals, costs and benefits because we don't think it should matter - they work for the company and this is the way it is.

I've worked with employees who have not "bought in" and have seen bad attitudes develop because they don't understand the reason for the extra work, longer hours and stress that often accompanies change.

It is not only our responsibility to promote change but to justify it to those who are impacted the most by it. There is a good reason for the change being made. It's not just "change for change's sake."

So, maybe the first thing that needs to occur is to change how change is done.

Wednesday, April 08, 2015

Updating a DME string used by MS Add-Ins

If you create a DME (Query) in MS Add-Ins with a status field and want to convert the numeric value to the actual description, save your DME file and then open it in TextPad (or Notepad, etc.) and edit the .dme file to add ,xlt to the status field. Save it and when you open it within MS Add-Ins and run it, the results will come back with the status description.
DEVEL2B~PO~PORECEIVE~2~1~600~Created with Query Wizard 10.0.109~0~1~0~F
CO         PO-NUMBER
6000         60000000000301139         9
6000         60000000000300138         9
6000         60000000000274137         1
6000         60000000000275136         1
600060000000000301139Archived / Closed
600060000000000300138Archived / Closed