ImageNow

Imagenow previous queue

We are in the process of doing a complete revamp of how we use ImageNow in AP. We’re moving to a new Workflow process and new Document Types, which is a big deal. We have been using the same setup/process that we designed with Perceptive five years ago. A lot has changed since then and we’ve been putting on bandaids for the last year or so, but we finally decided it was time to pull the trigger and start over.

As part of the redesign, we’re going from seven invoice document types (I’m not sure why we ever thought that was a good idea) to two: PO Invoice and Non-PO Invoice. The business requirements are that all Non-PO Invoices are audited for accuracy by a separate person. PO Invoices do not require auditing because we’re using Lawson, which utilizes a three-way match process between PO-Receiver-Invoice. We are going to have one “Processing” super queue, separated out into several sub-queues. When a clerk completes a document, they need to route PO Invoices to a Complete queue, but the Non-PO Invoices need to be routed to an Auditing queue. After the Auditing queue, the document is routed to Complete.

There are four ways to do this that I can think of:

  1. Assume the users will always route correctly
    Yeah right. Everyone makes mistakes. Even if we only had one mistake, Murphy’s Law says it will be a $1M+ payout that will get picked for audit.
  2. Have separate processing queues
    We’re shooting for less queues, so not really an option.
  3. Set up an outbound script on the queue to verify that they are routing the correct document type to the correct place
    I’m not sure if it’s possible, but I sure don’t like the idea of dynamically changing the route with an outbound script. It’s entirely possible that this is the intent of the outbound action; someone from Perceptive would need to weigh in on that – but the only thing I’ve ever seen them used for is updating values (like setting a value on a custom property).
  4. Set up an inbound script on the Auditing and Complete queues to route back Doc Types as necessary.
    The full requirement is: PO Invoices sent to Auditing should be routed back to where they came from and Non-PO Invoices sent to Complete should be sent back to where they came from UNLESS they came from Auditing. (In case you’re wondering, we’re routing back instead of to the correct place for two reasons 1: teaching tool 2: metrics)

So there’s the rub. In ImageNow 6.5, there is no iScript method to get the previous queue name. There is a method to route back to the previous queue, but I don’t really have the luxury of blindly routing back as it relates to the Complete queue since both the Processing queue and the Audit queue route there. However, there is an iScript method to get the routing history of a workflow item. The basic idea is to work backwards through the history (ignoring the current queue) and look for the last queue that the item was routed from. We can do this by looking for the last “Routed Out” action and examining the Queue Name.

Here is my previousQueue function:
It is expected that you will pass in a INWfItem object. The function will return either a Queue Name as a string or false.

// -------------------------------------------------------------------
// Function: 	getPreviousQueue
// Purpose:	    Finds the most recent queue that the item was in
//              excluding current
// Args(type):	wfitem (INWfItem object)
// Returns:	    Queue Name - String / false - Boolean
// -------------------------------------------------------------------
function getPreviousQueue(wfitem)
{
    var ROUTED_OUT_STATE = 5 /* Finished */; var ROUTED_OUT_STATE_DETAIL = 2 /* Routed out */;
    var queueName = false;
    if (!wfitem)
    {
        printf("no item found\n");
        return queueName;
    }
    var history = wfitem.getHistory();
    //work backwards through history to find last routed out queue
    for (var i=(history.length-1);i>=0;i--)
    {
        if (ROUTED_OUT_STATE == history[i].state && ROUTED_OUT_STATE_DETAIL == history[i].stateDetail)
        {
            queueName = history[i].queueName;
            break;
        }
    }
    return queueName;
}

All in all, pretty simple. Here’s how it’s used as an inbound script (Note that I have one script that is used on both queues).

var wfitem = INWfItem.get(currentWfItem.id);
var wfCurrentQ = currentWfQueue.name;
var msgReason;
var Q_Complete = "AP-Complete";
var Q_Complete_DocType = "PO Invoice";
var Q_Audit = "AP Audit";
var Q_Audit_Doctype = "Non-PO Invoice";
var wfStatus = 1;  //Idle condition

//Verify that a wf item was picked up
if (wfitem == null)
{
    printf("Failed to get item. Error: %s\n", getErrMsg());
    return;
}

//Set document based on wf item
doc.id = wfitem.objectId;

//Get document
if (!doc.getInfo())
{
    printf("Failed to get item. Error: %s\n", getErrMsg());
    return;
}

// snipped bunch of other code

msgReason = "Document sent to wrong queue";

//Route back from Audit Queue
if (wfCurrentQ == Q_Audit && Q_Audit_DocType != wfitem.docTypeName)
{
    return routeItemBack(wfitem,msgReason);
}

//Check if queue is AP Complete
//we only want to route back if it didn't come from Audit
var previousQueue = getPreviousQueue(wfitem);
if (wfCurrentQ == Q_Complete && Q_Audit_Doctype == wfitem.docTypeName && previousQueue != Q_Audit)
{
    return routeItemBack(wfitem,msgReason);
}

For completeness, here is the routeItemBack function referenced in the script.

// -------------------------------------------------------------------
// Function:      routeItemBack
// Purpose:       Routes an item to previous queue
// Args(type):    wfitem (INWfItem object), msgReason (string) - routing reason
//                Note: assumes Idle state of wfStatus set in calling script
// Returns:       Boolean
// -------------------------------------------------------------------
function routeItemBack(wfitem,msgReason)
{
    //Perform Routeback
    if (wfitem.routeBack(msgReason))
        return true;
    else
    {
        printf("Couldn't route item. %s\n", getErrMsg());
        wfitem.setState(wfStatus, "Release Hold");
    }
    return false;
}

HTH

Advertisements

ImageNow external messaging agent

Is ImageNow External Messaging Agent the best free bundled product ever? The answer depends on whether you’re using it or not. If you are, then your answer is yes. If you’re not, WHY AREN’T YOU?

ImageNow External Messaging Agent is one of those products that begs for use-cases. Based on the delivered documentation, it’s hard to conceptualize what it can do for you or even how to use it. There is a nice technical paper on the Perceptive support site, but it’s not easy to find. I’m going to give you two actual use cases using Lawson and Accounts Payable.

What is External Messaging agent? Simply put, it is a way to pass information between ImageNow and any external system that can process the messages. External Messaging Agent is table-based unlike other messaging systems like Websphere MQ or JMS. The upside is that if your external system doesn’t support true messaging, you can still use External Messaging Agent (assuming you can read from and write to the tables). There are two tables (IN_EXTERN_MSG:header and IN_EXTERN_MSG_PROP:detail) that contain the messages, and a service on the ImageNow machine monitors the tables. Iscript methods are used to both read and write messages on the ImageNow side, and SQL is used from the external system. Using External Messaging Agent is pretty simple. Write a message from the ImageNow side with your information for the external system to pick up, or read a message written by the external system and do something with it in ImageNow. Once you’ve successfully processed a message, you mark it as complete (or one of the other “processed” statuses – see the documentation for details) and External Messaging Agent cleans out the completed messages based on your config.

Use Case #1 – Messages to ImageNow – Lawson Vendor Merge
The business case is that our keys for AP Invoices in ImageNow are: Vendor, Company, Invoice, PO, Invoice Amount. When we merge vendors in Lawson (because of acquisition or duplicate vendors) Lawson gets updated and the “old” vendor number essentially goes away. However, unless we update ImageNow, we will have a hard time of finding the Invoice in the future and any “Vendor” queries in ImageNow will be incorrect. How we handle this is to have a design studio screen in Lawson where the AP staff can submit a request to merge vendors in ImageNow. The Design Studio screen triggers a Lawson Process Flow that creates the appropriate message records that are then processed in ImageNow by the External Messaging Agent.

Because of the Header-Detail structure, we have to create the header record first, then add the detail, then update the Header to a “Ready to Process” status. As I mentioned, we do this with Lawson Process Flow, but a perl script, PLSQL, etc should all accomplish the same thing.

First we need to generate unique ID’s for the messages. The best way to do this is to use the full date (including time). If you have sequencing turned on for your DB, you might be able to use that as well.

dtDate = new Date();
STRID = 'VendorMerge' + String(dtDate).replace(/ /g,"_");

Here are the SQL statements (my ImageNow is on SQL server, so I’m using their specific date/time functions). Each is executed individually and if any one of them fails, the next is not executed.

--Create Header Record
INSERT INTO IN_EXTERN_MSG (EXTERN_MSG_ID, MSG_TYPE, MSG_NAME, MSG_DIRECTION, MSG_STATUS, START_TIME)
	VALUES ( '<!STRID>', 'MERGEVENDORS','MERGEVENDORS', 1, 0, CURRENT_TIMESTAMP)
	
--Insert Old Vendor ID message property
INSERT INTO IN_EXTERN_MSG_PROP (EXTERN_MSG_ID, PROP_NAME, PROP_TYPE,PROP_VALUE)
	VALUES ('<!STRID>', 'OLDVENDOR', 0,'<!OLDVENDOR>')

--Insert New Vendor ID message property
INSERT INTO IN_EXTERN_MSG_PROP (EXTERN_MSG_ID, PROP_NAME, PROP_TYPE,PROP_VALUE)
	VALUES ('<!STRID>', 'NEWVENDOR', 0,'<!NEWVENDOR>')

--Update Status of Message Header
UPDATE IN_EXTERN_MSG
SET MSG_STATUS = 1
WHERE EXTERN_MSG_ID = '<!STRID>'

On the ImageNow side, an Iscript processes the messages it receives. In this case, we do a document move on any documents that have the “old” Vendor number to the “new” Vendor number. Apologies in advance, I have a handler function for VSL queries that I’m too lazy to “undo” for the demonstration. If you’re interested, drop me a note and I’ll post the code. VSL is a real bear to deal with and it seems to change from version to version, which is why I prefer my handler function.

//Global Variables
//=============================================================
var hasMore;
var limit = 1000;
var externalMsgObj = getInputPairs();
var strOldVendorID = externalMsgObj["OLDVENDOR"];
var strNewVendorID = externalMsgObj["NEWVENDOR"];
var boolSuccess = true;

var strVSL = "([drawer] = 'Normal_Invoices' OR [drawer] = 'Vendor_Maintenance') AND [folder] = '" + strOldVendorID + "'";

//Run Query
//=============================================================
var results = vslQuery(strVSL,limit,false,false);
if (!results)
{
    printf("No Vendor Records found.");
    setSuccess(false);
}

//Check for additional records and log
if (results.more)
    printf("There are more documents.  Not all were selected.");

//Loop through result array
for (i = 0; i < results.length; i++)
{
    //Build keys for new document
    var docid = results[i].id;
    var tokey = new INKeys(results[i].drawer, strNewVendorID, results[i].tab,
        results[i].f3, results[i].f4, results[i].f5, results[i].docTypeName);

    //Move document to new key values and append if file with keys exists
    if(INDocManager.moveDocument(docid, tokey, "APPEND"))
    {
        printf("Old Doc: %s To Doc: %s",docid,docmt.id);
    }
    else
    {
        printf("Could not move document: %s\n",getErrMsg());
        boolSuccess = false;
    }
}

//Set message status based on process
setSuccess(boolSuccess);

Use Case #2 – Messages from ImageNow – Document Notifications
The business case is related to Employee Expenses. Because Employee Expenses are “self-service”, the receipts for the expense are not necessarily recorded when the expense is entered. We do not want to send expenses out for approval until the receipts have been stored in ImageNow. The first step in the Employee Expense approval flow is an Inbasket for receipts. Once the receipts are received by ImageNow, a message is written out. The message is picked up by a separate Process Flow that performs the approval process and allows the expenses to continue in the approval process. I will post separately on how the full Employee Expense process works, but for background, once a linked expense receipt document is routed to the “complete” queue, the following script is triggered.

#define MSG_TYPE "EEX"
#define MSG_NAME "ApproveReceipts"

function main ()
{
    var wfItem = INWfItem.get(currentWfItem.id);
    if (wfItem == null)
    {
        printf("Could not retrieve workflow information.  Error: %s",getErrMsg());
        return;
    }

    //Set document based on wf item
    var doc = new INDocument();
    doc.id = wfItem.objectId;
    if (!doc.getInfo())
    {
        printf("Could not retrieve document information for workflow itemID: %s.  Error: %s",wfItem.id,getErrMsg());
        return;
    }

    printf("Approving Doc: Vendor: %s Comany: %s Invoice: %s DocID: %s",doc.folder, doc.tab, doc.f3, doc.id);


    //Build Message Object
    var msg = new INExternMsg();
    msg.type = MSG_TYPE;
    msg.name = MSG_NAME;
    msg.direction = ExternMsgDirection.Outbound;
    msg.status = ExternMsgStatus.New;
    msg.startTime = new Date();
    msg.addProperty("DocumentID", ExternMsgPropType.Undefined, doc.id);
    msg.addProperty("Vendor", ExternMsgPropType.Undefined, doc.folder);
    msg.addProperty("Company", ExternMsgPropType.Undefined, doc.tab);
    msg.addProperty("Invoice", ExternMsgPropType.Undefined, doc.f3);

    if (!msg.send())
    {
        printf("Could not send message for doc: Vendor: %s Comany: %s Invoice: %s DocID: %s.  Error is: \n",doc.folder, doc.tab, doc.f3, doc.id,getErrMsg());
    }
}

On the Process Flow side, we get the messages intended for us, then update the work units. First, the SQL to get the messages. Get the message Header, then get the details of the message.

--Get message
SELECT EXTERN_MSG_ID
FROM IN_EXTERN_MSG
WHERE MSG_DIRECTION = 2
  AND MSG_TYPE = 'EEX'
  AND MSG_NAME = 'ApproveReceipts'
  AND MSG_STATUS = 1

--Get Message details
SELECT PROP_NAME, PROP_VALUE
FROM IN_EXTERN_MSG_PROP
WHERE EXTERN_MSG_ID ='<!SQLQueryMsg_EXTERN_MSG_ID>'

Because we get several records back for the details, we parse them like this. If anyone else has any better ideas (for Lawson Process Flow), I’m all ears.

switch (SQLQueryProps_PROP_NAME)
{
    case 'Company':
        strCompany = addLeadingZeros(SQLQueryProps_PROP_VALUE, 4);
        break;
    case 'Vendor':
        strVendor =addLeadingSpaces(SQLQueryProps_PROP_VALUE, 9, false) ;
        break;
    case 'Invoice':
        strInvoice = SQLQueryProps_PROP_VALUE;
        break;
    case 'DocumentID':
        strDocID = SQLQueryProps_PROP_VALUE;
        break;
    default:
        break;
}

We perform the actions on the workunits and then update the message as necessary. See this post for how to perform Inbasket actions with Process Flow.

UPDATE IN_EXTERN_MSG
SET MSG_STATUS = 4, END_TIME = CURRENT_TIMESTAMP
WHERE EXTERN_MSG_ID = '<!SQLQueryMsg_EXTERN_MSG_ID>'

The possibilities for using ImageNow External Messaging Agent are practically endless which is what makes it so powerful. I hope you’re convinced to start using it.

HTH

Modifying Lawson screens for ImageNow LearnMode

A question was posed to me recently regarding how to effectively and efficiently use ImageNow LearnMode on a Lawson screen. Specifically, the problem revolves around fields that exist on Lawson tabs that are not displayed when the page is loaded. This means that no DOM objects are built and ImageNow cannot read them. Take HR11, for example, where my company links the Process Level to a Custom Property. The problem is, the Process Level for an employee is on the ‘Assignment’ tab which is not the default tab. Because of this, Lawson does not build the DOM for the tab until you click on the ‘Assignment’ tab even though the data exists for it (the AGS transaction returned all data, even if the form hasn’t been populated).

From an ImageNow perspective, the tradition is to use the getAppData VB function in LearnMode to parse through the entire hierarchy to find the data you want. This presents several problems as each user in Lawson may have different bookmarks, search bars, etc and thus a different hierarchy. The exact location of the element that you want to get data from (like the process level field) will move it’s relative position on a per user basis. I have used this method myself and I know that a lot of Perceptive implementation consultants do it too, but that doesn’t make it right.

Note: I’m not discussing getAppData here because it could be a whole post itself. If you’re interested, leave a comment and I may do a post on it. After all, I’m doing this post because of a comment that was left.

There are only three reasons to use getAppData in ImageNow LearnMode for Lawson.

  1. You don’t own Design Studio
  2. You’re trying to link on a Detail screen with many lines and you don’t know which line to link (PO64 comes to mind)
  3. You don’t know what you’re doing

My opinion is that #1 is the only valid reason and even that is tenuous. As far as I know, you can customize any Lawson form and Portal will render it for you, Design Studio just provides a development environment. You can create customized form xml without Design Studio in the same way that you can write Java code without an IDE, it’s just a lot easier when you have a development environment.

Let me restate the problem: There is data on a Lawson form that ImageNow IE LearnMode cannot read. This data is on the form, but it is on a tab that is not the default. Therefore, this data is not available to ImageNow until the tab is selected. Once it is selected, it is always available until the user leaves the screen. Make Sense?

There are three relatively easy ways to do this, two using Lawson Design Studio, that I am aware of. Each has it’s pros and cons.

  1. Have the user click on the tab in question after they load the form (and any subsequent time they access it if they go to another form). This is probably the least desirable as you rely on the users to remember to do this. If you’re linking into Custom Properties in ImageNow, chances are good that you’ll wind up with a lot of “default” values.
  2. Create a new field (or fields) that is hidden from the user. Any event or field change, update the field(s). If the field(s) is created on the ‘header’ section of the form, it is always visible to ImageNow and can be linked on. The downside is that you have to control updating your fields on both Form events and on changes to the field itself. This can be especially tricky if the field you need is auto-populated by Lawson as a default (like a name or description).
  3. Activate the tab and then activate the original tab. This is the automated version of #1. I have tested this, but never used it in a production setting, so I can’t guarantee it works. Aside from that, it’s the easiest to implement. However, if you need data from four or five different tabs, you may find it easier to just create the fields as in #2.

Keep in mind, if you need data that is not on the form you are linking, then you MUST use option #2.

Enough of the jibber-jabber, how about some code? All of these examples will use AP20. For our pretend requirements, I need ImageNow to link the “Cash Code” that is located on the “Payment” tab and is the third tab on the AP20 (aka not the default).

Option 3.

function FORM_OnAfterFrameworkInit()
{
		lawForm.setActiveTab("TF0-2");
		lawForm.setActiveTab("TF0-0");
}

Here we activate tab 3 (using the tab name) and then re-activate the default tab.
Add this as early in the event hierarchy on a form load as you can. I believe the earliest you can add it to be the OnAfterFrameworkInit, as demonstrated here. The XML for the form has been loaded, the initial DOM has been built, and any script actions CAN affect DOM elements. Keep in mind that data is not available at this point, even if you have an initial form action set.

Option 2.
First create a empty text field. In this case, I created “text80”.

FIELD_NAME = "text80";  //Global field declaration - Note this is the Name on the field Properties
//Hide field
function FORM_OnInit()
{
	var fElem=lawForm.getFormElement(FIELD_NAME);
	fElem.style.visibility = "hidden";
}

//Populate our field every time an action is performed
function FORM_OnAfterTransaction(data)
{
	var strCashCode = data.getElementsByTagName("_f96")[0].firstChild.nodeValue;  //Pull our data out of AGS return
	lawForm.setFormValue(FIELD_NAME,strCashCode,0); //Update field to be cashcode
}

//Update the field if the user changes it.  This means we can point ImageNow at a single field and not miss any updates
function TEXT_OnBlur(id, row)
{
	if (id == "text38") //ID of the cash code field
		lawForm.setFormValue(FIELD_NAME,lawForm.getFormValue(id,row),row);
	return true;
}

The OnInit event hides our element as the form is loaded. This could be done by directly manipulating the XML that constructs the form. I don’t like to manipulate the XML if I can help it, not that I’m above it, but bad things can happen. I’ve been there, don’t let it happen to you.
The FORM_OnAfterTransaction event is triggered after any event on the form (Inquire, Add, etc). It may not be appropriate to evaluate this on every action, but when in doubt, do it always.
The TEXT_OnBlur event is executed as the text loses focus. Basically, this is after a user changes the field directly. THIS IS IMPORTANT. If the user changes the data in the field, we need to capture it because they may not execute a Lawson action prior to performing the ImageNow link, which means it won’t be trapped by the FORM_OnAfterTransaction event.

All this event talk makes me think I might need to do a primer on the Lawson Portal Event hierarchy. But I digress.

Regarding my previous statement about why you would use getAppData, I’ve basically addressed #1 and #3. #2 is a little trickier, but not much. (Sorry for the confusion, but WordPress doesn’t seem to support non-numbered ordered lists, so it’s difficult to differentiate the lists.) I would propose that you basically use the second #2 and have the users put an X (select) in FC detail field. You would then use the TEXT_OnBlur for the FC field as a trigger. When the even is triggered, have it write the data to fields at the header record that ImageNow can read. This solves problems like linking from AP90 or GL90. Yes, if the user enters X in multiple fields, only the last row where an X was entered would be linked. This is a training issue. You can program around it, but it has to stop somewhere.

Thanks to Matt from Perceptive for the question.

HTH

ImageNow license monitoring

Today is going to be a bonus day of sorts.  This post is specifically going to be about reporting on ImageNow licensing, but if you read carefully, there may be a few hidden gems that you can use for other things.

It’s budget time for us right now.  To me that doesn’t mean a whole lot, except for the fact that my boss (and director) are going to start asking for license usage.  Perceptive has done a great job of giving us an overview of the licensing within the ImageNow Server Administrator (ISA).  What about if you’re not on the server?  What if someone else wants to know the licensing situation, but they don’t have access to the server?  (For those of you who submitted the idea of making ISA available via WebNow, I upvoted it).

Here’s a little bit of SQL (SQL Server version) that will more or less give you the ISA Current usage License view for your ImageNow client and WebNow license usage.  I’ve excluded everything else because they shouldn’t be an issue.

--Report of connected users
SELECT USR.USR_NAME, USR.USR_FIRST_NAME, USR.USR_LAST_NAME,
  CASE WHEN LIC.LIC_TYPE = 0
    THEN 'CLIENT' ELSE 'WEB'
  END AS LIC_TYPE,
  LIC.COMPUTER_NAME,
  LIC.LAST_UPDATE,
  DATEDIFF("n",LIC.LOGIN_TIME,GETUTCDATE()) AS ACTIVE_TIME,
  CASE WHEN LIC.IS_ACTIVE = 1
    THEN 'ACTIVE' ELSE 'INACTIVE'
  END AS STATUS
FROM IN_LIC_MON LIC
INNER JOIN IN_USR USR
ON LIC.USR_ID = USR.USR_ID
WHERE LIC.LIC_TYPE IN (0,9)
ORDER BY LIC.LIC_TYPE, USR_NAME

The output looks like (depending on your db version):

USR_NAME USR_FIRST_NAME USR_LAST_NAME LIC_TYPE COMPUTER_NAME LAST_UPDATE ACTIVE_TIME STATUS
jdoe1 Jane Doe CLIENT GW310GHB1 2010-08-27 18:32:02.0 422 ACTIVE
adoe Amy Doe CLIENT GWGX17HB1 2010-08-27 18:01:35.0 92 ACTIVE
...
ldoe2 Lawrence Doe WEB UH45Y4JK1 2010-08-27 18:08:28.0 25 ACTIVE


The intent is that you would put this into a Crystal report and share on your BI dashboard.

**Note that all dates/times stored in the ImageNow DB are UTC.  In order to get an accurate representation of the logged in time, you have to do a UTC comparison.  For other db users, this field/calculation (including datediff) should be the only thing you have to change.   I’m also not including combo licenses because we don’t have any, so I don’t know the license number.  If anyone does and can share, I’d be more than happy to update the SQL.

I sure some of you are saying, “But Jim, that’s nice if I want to see what it is right now, but what about trends?”.  Okay, so basically we do something similar and capture it to a file.   Personally, I have a script that runs every 5 minutes and captures the output to a flat file that we can then review.

Here’s the SQL for it (SQL Server version again):

--Graph of used licenses by type 
SELECT GETDATE(), 
CASE TMP.LIC_TYPE 
  WHEN 0 
    THEN REPLICATE('C',TMP.LIC_USED_COUNT)+'&nbsp;'+CAST(TMP.LIC_USED_COUNT AS VARCHAR)+'/'+CAST(TMP.LIC_COUNT AS VARCHAR)
  WHEN 9 
    THEN REPLICATE('W',TMP.LIC_USED_COUNT)+'&nbsp;'+CAST(TMP.LIC_USED_COUNT AS VARCHAR)+'/'+CAST(TMP.LIC_COUNT AS VARCHAR)
  ELSE REPLICATE('X',TMP.LIC_COUNT) 
END AS GRAPH 
FROM ( 
  SELECT MON.LIC_TYPE, 
    COUNT(MON.USR_ID) LIC_USED_COUNT, 
    LIC.LIC_COUNT 
  FROM IN_LIC_MON MON 
  INNER JOIN 
    (SELECT TYPE3, SUM(NUM_USRS) LIC_COUNT 
    FROM IN_LIC LIC 
    GROUP BY TYPE3) AS LIC 
  ON MON.LIC_TYPE = LIC.TYPE3 
  GROUP BY MON.LIC_TYPE, LIC.LIC_COUNT) TMP

The output looks like:

2010-08-27 15:05:13.097 CCCCCCCCCCCCCCCCCCCCCCCC 24/101 
2010-08-27 15:05:13.097 WWWWWWWWWWW 11/79 
2010-08-27 15:05:13.097 XX 
2010-08-27 15:10:39.47  CCCCCCCCCCCCCCCCCCCCC 21/101 
2010-08-27 15:10:39.47  WWWWWWWWWWWWW 13/79 
2010-08-27 15:10:39.47  XX
...
2010-08-27 17:10:39.47  CCCCCCCCCCC 11/101 
2010-08-27 17:10:39.47  WWWWWWWWWWWWWWWWWWWW 20/79 
2010-08-27 17:10:39.47  XX

Notice the graph that it’s producing?  This makes it easy to spot when you have peaks (and how much your server is actually being used after hours).  This is one of the gems.  You don’t have to dump data and put it into Excel or Access or whatever to “see” your data.  Other db folks will have to convert the replicate function, but as far as I’m aware, all the ImageNow supported db’s have this capability.  Just make sure you view it in a fixed width font. You may also want to consider outputting your license types to different files.

For the final bit, here’s a fully functional set of Iscript code that is very similar to the script I  run.  I have a windows scheduler task that runs it every 5 minutes.

// ------------------------------------------------------------------- 
// Function:    licUsageGraph 
// Purpose:     record license usage 
// ------------------------------------------------------------------- 

#link  
#include  

//Declare constants 
#define LOG_DIR         "e:\\inserver6\\log\\"                  //logfile directory 

// Set and open logfiles 
var doclogfile = LOG_DIR + "licensing_" + getDateString("FileName") + ".log"; 
logfile = Clib.fopen(doclogfile, "a"); 
licfile = Clib.fopen(LOG_DIR + "licenseusage.txt", "a"); 


function main() 
{ 
    //Create db connection 
    var db = new Database(); 
    var err = db.connect("ODBC","ImageNow 6","user","password"); 
    if (err != 0){logging("Could not connect to db",logfile);return false;} 
    var strSQL = "SELECT CAST(GETDATE() AS VARCHAR) AS DATETIME,  " + 
        "CASE TMP.LIC_TYPE " + 
        "    WHEN 0 THEN REPLICATE('C',TMP.LIC_USED_COUNT)+' '+CAST(TMP.LIC_USED_COUNT AS VARCHAR)+'/'+CAST(TMP.LIC_COUNT AS VARCHAR) " +
        "    WHEN 9 THEN REPLICATE('W',TMP.LIC_USED_COUNT)+' '+CAST(TMP.LIC_USED_COUNT AS VARCHAR)+'/'+CAST(TMP.LIC_COUNT AS VARCHAR) " +
        "    ELSE REPLICATE('X',TMP.LIC_COUNT) " + 
        "END AS GRAPH " + 
        "FROM ( " + 
        "  SELECT MON.LIC_TYPE, " + 
        "    COUNT(MON.USR_ID) LIC_USED_COUNT, " + 
        "    LIC.LIC_COUNT " + 
        "   FROM IN_LIC_MON MON " + 
        "  INNER JOIN  " + 
        "    (SELECT TYPE3, SUM(NUM_USRS) LIC_COUNT " + 
        "    FROM IN_LIC LIC " + 
        "    GROUP BY TYPE3) AS LIC" + 
        "  ON MON.LIC_TYPE = LIC.TYPE3 " + 
        "  GROUP BY MON.LIC_TYPE, LIC.LIC_COUNT) TMP" 

    // Open cursor 
    var usrcursor = db.cursor(strSQL); 

    //Check for records in cursor 
    if (!usrcursor.first()) 
    { 
        Clib.fputs("No records returned for SQL " + strSQL + "\n", logfile); 
        return false; 
    } 

    //Get data from cursor -- print first line, then print all others with while loop 
    Clib.fputs(usrcursor.DATETIME + " " + usrcursor.GRAPH + "\n", licfile); 

    while (usrcursor.next()) 
    { 
        Clib.fputs(usrcursor.DATETIME + " " + usrcursor.GRAPH + "\n", licfile); 
    } 

    //Close Cursor 
    usrcursor.close(); 

    if (db.connected()) 
    { 
        var strSQLDXErr = db.disconnect(); 
        if (strSQLDXErr != 0) 
        { 
            Clib.fputs("Could not disconnect from db \n" 
            + "Error is: " + db.majorErrorCode() + "-" + db.majorErrorMessage() + "\n" 
            + db.minorErrorCode() + "-" + db.minorErrorMessage() + "\n",logfile); 
        } 
    } 

    Clib.fclose(licfile); 
    Clib.fclose(logfile); 
}

HTH

New Hire On-boarding with Lawson and ImageNow

This will be the first of a multi-part series on how we do automated new hire on-boarding at my company. I submitted this as a presentation topic for Lawson CUE this year titled “How {my company} leverages Process Flow, LBI and ImageNow to reduce New Hire Paperwork” and was told that they already had presentations that were similar. I talked to Perceptive (makers of ImageNow) about the idea and they told me that they didn’t know of anyone that was doing this. So this is for those of you who couldn’t make it to CUE to see the similar presentations and those of you using ImageNow.

Session Title:
How {my company} leverages Process Flow, LBI and ImageNow to reduce New Hire Paperwork

Session Description:
{my company}’s average of 12,000 new employees a year creates mounds of paperwork to key and scan. Come hear how {my company} recently reduced its new hire paperwork and data entry by over 75% using Process Flow Integrator and LBI along with Perceptive Software’s ImageNow and RecognitionAgent products.

Software Utilized:
Lawson HR and Payroll modules
Process Flow Integrator
External Website (maintained and supported by in-house web team)
Lawson Business Intelligence (LBI) Reporting Services
ImageNow
ImageNow Recognition Agent

Executive Summary:

  1. A Hire/Re-hire/External Transfer record is created in Lawson
  2. A scheduled Lawson Process Flow is run to update the database of the website with information about new employees from #1
  3. New Hire fills out “paperwork” on secure website
  4. A scheduled Lawson Process Flow is run to “pick up” completed records from the website and update Lawson
  5. Any errors have workunits created for them.  HR and Payroll staff work from an inbasket to resolve the errors
  6. A series of scheduled LBI reports are run to re-produce how the form would look on paper with barcodes
  7. The reports are imported into ImageNow and submitted to Recognition Agent
  8. Recognition Agent reads the barcodes and updates the document keys

The only involvement from the HR or Payroll staff is to fix any errors that may have occurred.

The forms currently being captured in this process are:

  • Direct Deposit Form – this includes bank account info for employee pay
  • Information form – validation of employee information like name, address, etc.
  • Personal Information form – employee demographic information (veteran status, ethnicity, gender, etc)
  • Information Security form – company form that contains the information security policy that all new hires must sign that they have read
  • Payroll Deduction Authorization form
  • Federal W4 form
  • State WH4 form

Stay tuned for more.

ImageNow Custom Properties for Document Types

The question came up recently on which ImageNow document types had custom properties and what they were.
Here is the SQL Server version of the query to find all active Doc Types with active Custom properties.

SELECT DT.DOC_TYPE_NAME, PROP.PROP_NAME, CP.SEQ_NUM, CP.IS_REQUIRED,
    CASE PROP.PROP_TYPE
      WHEN 1 Then 'String'
      WHEN 2 THEN 'Number'
      WHEN 3 THEN 'Date'
      WHEN 4 THEN 'Flag'
      WHEN 5 THEN 'List'
    END AS PROP_TYPE
FROM INOW6.dbo.IN_DOC_TYPE DT
    INNER JOIN INOW6.dbo.IN_CLASS_PROP CP
    ON DT.CLASS_ID = CP.CLASS_ID
    INNER JOIN INOW6.dbo.IN_PROP PROP
    ON CP.PROP_ID = PROP.PROP_ID
WHERE DT.IS_ACTIVE = 1
    AND PROP.IS_ACTIVE = 1
ORDER BY DT.DOC_TYPE_NAME, CP.SEQ_NUM

The output looks like this.

DOC_TYPE_NAME                   PROP_NAME           SEQ_NUM IS_REQUIRED PROP_TYPE
-------------                   ---------           ------- ----------- ---------
HR_Application	                HR_Effective Date	0	    1           Date
HR_Attendance_Improvement_Plan	HR_Effective Date	0	    1           Date
HR_Attendance_Improvement_Plan	HR_PIP_Type	        1	    1           List
HR_Attendance_Warning	        HR_Effective Date	0	    1           Date

HTH

More fun with Clib

In ImageNow Iscript there is a library command: Clib.system().

This will issue a command just like you typed it at the command prompt. (There is the additional P_SWAP parameter that I’m ignoring here).

Here’s how you use it:
var cmd = "time /t";
Clib.system(cmd);

On a Windows system, the result should be:
4:49a

While it’s great that you can issue DOS or UNIX commands from within Iscript, there are potential problems. Let’s say that you need to move a file to a different directory. You might issue a command like: (Windows system)
MOVE /Y C:\temp.txt C:\backup\temp.txt

But what if the move fails?

The good news is that Clib will return error codes.

So what we do is something like:
var cmd = "MOVE /Y C:\temp.txt C:\backup\temp.txt";
var response = Clib.system(cmd);
if (response != 0)
printf("Error occurred during file move.");

In this case, we try to do the file move and return the result of the command into a variable. We can then evaluate the response code and determine the appropriate action. A return code of 0 means success.

Given the above scenario, I might be inclined to try the file move again. I’m trying to move a file but can’t, and it is entirely possible that some other process I have running (or even this script) has the file locked so that I cannot move it.

Consider the following:
var cmd = "MOVE /Y C:\temp.txt C:\backup\temp.txt";
var response = Clib.system(cmd);
var i = 1;
for (response == 0 || i<5)
{
response = Clib.system(cmd);
i++;
}

In this case, we try to repeat the move command (up to 5 times) until we get a success code. In this way, we can make sure that we move the file as intended even if the initial file move was a failure.

HTH

ImageNow 6.x Iscript prompting

It isn’t publicized and even when you read the documentation, it isn’t that obvious (to me anyway).

I have a script that runs from the intool command that will merge records in ImageNow.  We use ImageNow in AP (and HR), and when vendors merge (or they were duplicates), we need a way to update all of the ImageNow records after they have been through the Lawson Vendor Merge so we can find them again.  After several months of manually updating the script to the new vendor numbers, which were declared with the #define, I needed a better way.

The answer lies in the Clib library that is part of Iscript.  The function I use is gets(), although there are several ways that have their own pluses and minuses.

//Get inputs from user and validate
//-----------
Clib.printf("Enter Old Vendor Number: ");
var oldVendor = Clib.gets();
if (!checkNumber(oldVendor))
{
printf("Old Vendor is not a valid number");
Clib.exit(1);
}
Clib.printf("Enter New Vendor Number: ");
var newVendor = Clib.gets();
if (!checkNumber(newVendor))
{
printf("New Vendor is not a valid number");
Clib.exit(1);
}
//-----------

First the script outputs the prompting text using printf, then waits for input from the command line.  checkNumber is a custom function that verifies that the input is a number.  I don’t use isNaN(), because I also need to check for length – hence the custom function.  If it fails the number test, then the script exits and returns an error code.

The script, which is called intoolVendorMerge.js, gets called from the command line:

\inserver6\bin>intool --cmd run-iscript --file intoolVendorMerge.js

So there you have it – prompting in Iscript.

HTH