Perceptive Content / ImageNow iScript Part 2

Back by popular demand, RPI Consultants offers this second installation of Office Hours for Perceptive Content / ImageNow iScript. During this recording, you will find live demonstrations and hear interactive Q&A related to iScript, Perceptive Content’s custom automation engine.

Transcript

Speaker 1:
Hello, welcome to Office Hours, everyone. This is RPI’s Monthly Office Hours where we just try to show you cool stuff and try to give you an opportunity to ask us questions about what we’re presenting or if you have questions that are unrelated, feel free to ask those as well. We’re just trying to keep an open dialogue with everybody to be an open resource. Before we get started with my presentation, one thing I want to point out is in the webinar Menu, there should be a drop-down option for handouts, the Handouts Section. There’s a Word document that’s included with this webinar. It’s called RPI iScript Resources.

That document is the same document that we use in part one of this series, but it’s still relevant to what we’re talking about today. If you missed the first part of this, please open that up. I’m actually going to share and go through a couple things here, but definitely hold on to this because it’s a great resource to just walk you through the different things that you can use on your end to help yourself with iScript. Last time we did this, we spent a little bit of time going through the basics of iScript and some of the basic things that you can do with it. This time, what we’re going to do is focus more on database queries.

We’re going to look at how do we connect to a database, how do we run a query. I specifically have a reindex iScript that I’m using. It’s like an example of HR script. I have an employee ID that I’m going to query against a database table to find employee information and index the document. We’ll be indexing index keys like field one through five and custom properties so that we can show you those differences. In the last, if you missed the first part of this, I definitely recommend going back and taking a look at that. I’m not going to cover all of the little basics like I did last time. It’s really helpful if you go back on that one. It was a little bit longer because we tried to cover a lot of stuff at iScript. Definitely go back and look at that because it was a lot of information.

Let me show you what I’m talking about here. This is the important links and resources for iScript document that’s in the Handout Section. The main things we’re going to be talking about today and really that you talk about all the time when we’re discussing iScript are the STL Files and we’ll be talking about the INDocument object. The STL Files, if you aren’t familiar with them, is just the standard template library of functions that are available for you to use within iScript. They’re generally included with your system. If you just go to your Scripts Directory, you’ll see the STL File in there. If you don’t have them, you can grab them off of Hyland’s website.

The really nice thing about that group of STL Files is the index. Inside of the STL Folder, you’ll find an index HTML and it takes you to this page. Let me get this thing out of here. Minimize this. There we go. On the STL Index page, we just have kind of a group on the left side, groupings of functionality. For example, if I break out document, it’s going to show me a whole bunch of different functions that are available to me that are specific to documents within Perceptive Content. For example, if I want to reindex a document like what we’re going to do today, I can find that Reindex Document function here, click on it and it’s going to show me not only example code but also like what it returns, what I need to pass into it.

Here’s an example of the method where I can break this out, I know that my function is Reindex Document, and it shows me the different items that I need to pass in into that function group to run properly. It also outlines like our optional items. You’ll see specifically optional called out in here for certain items in that function. This is definitely super handy. You can save tons and tons of time if you utilize STL instead of trying to just go willy-nilly inside of the iScript. It really speeds everything up because anything, pretty much anything that is done a lot in an iScript, they’ve covered in the STL. Creating documents, routing documents, even archiving or even things not related to documents like date conversions.

If I want to break out the date section, I have my Convert to Date String. If I have a data type, but I require a string data type in what I’m passing it to, I can use this function to convert that data type to a string to pass that over which is another example of something that we’ll do today. I don’t want to go too deep into this. If you want any more information about this, again go back to the part one video that we should be hosting on the RPI website and it will cover in depth a lot of this other stuff. Let me just jump straight into what we have built. I have a test environment, our internal test environment for ImageNow and also my test SQL Server.

I’m going to show you the table that I built that we’re going to be querying against and the script that I put together and how I put it together inside of workflow. First, let’s take just a look at this table. This is a really tiny table. There isn’t a whole lot of data in it. I just put it directly in the INow database. Really, all we have here is an employee ID column and then some employee information like first name, last name, middle name, date of birth, their start date or hire date, their position number and their supervisor. These are going to be the data elements that we want to capture inside of our iScript. To give you kind of a scenario for what we’re trying to build here, let’s say that you had HR documents that you were wanting to read into the system, and at time of import, maybe all you had was the employee ID.

What we’re doing is we’re recreating that with our Doc Import Queue here. I have some documents currently sitting in this workflow queue that don’t have any data in it other than the employee ID. This is a really simple workflow where all I’m doing is just moving them to my Doc Indexing Queue that kicks off my iScript, my Indexing Script, and then it will either move it to complete or to the error queue depending on whether or not it’s successful.

This is my Document Import Queue. You see that I’ve got documents here. All of these documents that I’ve got inside of here are pretty much blank, except for my employee ID. See that I also have a couple custom properties here. To compare what I have in my document and my database, I have my full name. Full name in this scenario, what I’m going to do is I’m going to take last name, comma, first name, space, middle initial. This is going to require us to pull back all three, all of the data for first name, last name and middle name and then do some massaging for that data that’s returned to place it into our field one full name field.

Field two his date of birth. That’s going to match up to my date of birth column. Position number, I have this field three. Again, position number in the column there. I will be setting unique ID. This will be an example of an STL function that we can use that maybe isn’t related to the database, maybe we didn’t have a unique ID when I pulled in for some reason. We want to go ahead and add it now. The custom properties for start date and supervisor again, just more columns inside of the database here. Let’s take a look at the iScript. To start this iScript-

There we go. Is that better? To start with the iScript, I started just using my template workflow. In the first video, I showed people that I had a template workflow and a template in tool iScript shell. This is kind of just a jumping off point. It’s easier for me to just start from something like this, so I don’t have to type it all out again, but basically all this is there’s not really any functionality in here. I’m just starting my process for me, so I don’t have to retype everything out every time. If you have these and you were part of the part one video, then great. If not, I will have my Mike Hopkins send out a copy of those templates after this presentation is over because they’re pretty handy.

Again, if you missed part one, the difference between workflow and in tool that I’m talking about is essentially just, “Are we tying into a workflow queue as a workflow action or are we using it as like a scheduled task running from a batch file?” or something like that. In this scenario, we’ll be doing everything through workflow. I started with this workflow template and then just add it on to it. This is the actual iScript. Generally, what I like to do inside of these iScripts and what you should always see in the iScript is a summary of the script process. To go through that, it’s essentially all it’s doing is input. When the document enters the indexing queue, it kicks off the iScript. The iScript will check the employee ID and then run query based on that employee ID against my employees table.

The data that’s returned, returns in an object where I will take the data from each of the columns, apply it to the document and then route it forward to the indexing complete queue. If there’s an error, like if there’s no query return or if the database can’t connect for some reason or anything like that, then I’m routing into my error queue. We’ll walk through all of that step by step.

Once we get past the comment section up here, that’s just giving some basic information about the script, the next area is the STL Packages. This is just where I’m linking in the STL functions that I’m using within the STL Directory. Since we’re here already, I may as well just go ahead and show you. So, where I’m talking about the STL Files, if you go to your Scripts Directory inside of in-server, you’ll find an STL Folder right here. This is going to be where I can find my packages my documentation and everything that I can use. I go more in depth on that in the first video if you need more information.

The STL functions that I’ll be using inside of this iScript are iScript Debug which is something that you’ll pretty much always have an iScript because it’s how you log out information into the script to log. I’ll use the route item function to route documents from queue to queue. DBAccess I use to not only open the database, but to also perform the query. Reindex Document is what reindexes the fields or the index keys on the ImageNow documents, so field one through five drawer document type. Property Manager is what is used for custom properties. I can’t just run the Reindex Document function and update all of the index keys in the custom properties because they’re really separate data types. I’ll use Property Manager separately after reindexing the document to cover the custom properties.

I use Convert to Date String to convert a data type to a string data type or one of those. I’ll get into that a little bit more later on when we’re actually in the code. Then I’m using Generate Unique ID to generate one of those Perceptive Content unique IDs that you’re probably familiar with into field five. One other thing to mention too, you won’t always see this link SEDBC line here, but I have to link this in specifically for my DBAccess script. The STL DBAccess JS requires us to link this in for it to function. Let’s say you were doing this from scratch, and you forgot to add in the SEDBC, it will actually log out for you that it needs that included. It’s pretty obvious that you need to add that.

Next is the Configuration Section. This again is a standard section in an iScript where we can determine whether or not we want to do dry runs, if we want to log to file our console, split logging by thread our debug levels, which we went into in depth in the first video and the max log file size. Most of this is pretty standard, so I generally just use whatever is already in the template. Then if I need to adjust anything, I’ll adjust from there, but most of the time to stay there. The only other thing that I might adjust on the fly is the debug level after it’s done. After we know everything works and we move this to prod, we don’t really need it set to the highest debug level anymore. I’ll just flip that over to zero or one.

Next is the Global Variable Section. This is where we start moving from generic to script specific. Inside of this section, this is really the section where I’m trying to tell users, “It’s okay for you to come in here and adjust this if you need to adjust it for something reason.” Let’s say that there is a workflow change made and we had to change the error queue name or something like that. They could come in here and change the queue name to whatever it needs to be. Inside of my Global Variables, I have my error queue, my complete queue defines for routing.

I’m specifying which index key holds my employee ID. That way, if they decide that they want to store the employee ID in a different field in the future, we can change that. There is a huge code change. I also have my database login information set here. The way that we’re going to be connecting through the database is through an ODBC connection. I have my Perceptive Content ODBC connection set up here. This is just the same one that I’m using for ImageNow because I’m hinging this table just off of the INow database. All I’m telling it is to use that DSN with this username and this password to get into the database.

I have my query. This is actually going to be the query that I’m running to find my information. All of my columns match up to the column names that are inside of the table. Again, I’m pulling first name, last name, middle name, date of birth, start date, position number and supervisor from the employees table where employee ID equals, and I have kind of a funny looking employee ID string here and the only reason that I do this is because I am intending on replacing this later on in the script. I’m adding those front and back carrots just to make it easy to make it like a unique string that’s easy for me to find and replace later on. This will be replaced with whatever value is currently stored in field four.

I have this section commented out for now. This is a little bit of advanced steps that I’ll show you later on after we’ve gone through the basic version. My execution method is workflow again because it’s an inbound action inside of the workflow queue. I’m starting up my debug variable just so that I can use it for my debugging once the actual main script body starts, which is what we get into now. In my main script body, this is where I’m just starting logging. This up to this point here, line 105, is pretty much what I have built out in the workflow template. We’re pretty much always going to start our debugging. If it’s a workflow script, I’m always going to have my workflow item.

The workflow item in this case is just the item that has entered the workflow queue. Then using that workflow item, I can use that to get the actual Perceptive Content document. Whenever you’re thinking of a workflow item inside of Perceptive Content, it’s not the true Perceptive Content document. I couldn’t get all of the document data from just the workflow item, it’s almost more like a shortcut to the document itself. When I’m reading in the workflow item here, this isn’t going to give me everything that I need for processing, I still need to get the actual INDocument object for the document.

Then, just to pause really quick now that we’re talking about INDocument, I want to go back to that handout. The INDocument is what I’m talking about right here. In this document, I’ve embedded the link in here. I’m going to open up the hyperlink for INDocument. It’s going to take us over to docs.hyland.com or should. Let’s see if my … There we go. This is really, really handy because the INDocument, it’s rare that you’ll be working with an iScript that doesn’t use the INDocument in some way. The INDocument object contains basically every little bit of information about a document inside of ImageNow. All of the index keys, the creation time, creation user, anything that you’re looking for that’s tied to a document you can generally find in the INDocument object.

This link that I provided inside of that handout really will go through all of the different keys tied into that tied into that object. You can kind of search around in here to find specifically like right here. It’s talking about doc.id, will pull back the document ID. doc.drawer pulls back the drawer name. These are all just keys inside of the greater INDocument object. If I set for example, my doc here to a new INDocument using the workflow item object ID, this is what is returned. Doc is now an object where I have keys like field one that store my field one value. If I typed out doc.field1. If I said var value = doc.field1, whatever I currently have in field one of that document will now be what value is equal to. It’s really easy to pull information back from a document using the INDocument object.

Getting the info is the next step. This is basically just running a function that then pulls in that data. Really, I should have done the doc.field1 after getInfo because getInfo is what’s populating our keys with actual values. Assuming that I don’t run into any errors, you can see I’m using my exclamation point false all over the place here. If I have an issue, pulling back getInfo, then I am now routing my workflow item to my error queue with my message failed to retrieving doc info. The reason that I know how to pass these specific variables or these specific items into the function, again is just going back to the STL function.

If I go to workflow in my STL Index, go to route item, down here is my code example. I can just copy this exact code body and drop it into my script because all it is saying is it’s showing me I need my workflow item, I need my queue name and whatever note I’m applying to the routing action. Whatever string is put here is actually what’s reflected in the workflow history of the document. If you see a document moved from queue to queue and you break out the description, it will actually show that that string in there to give you a better idea of why something routed.

If I scroll down, and I want more information, the Method Route Item specifically talks about all of those variables that read in including the optional items. Again, I talked about STL all the time and the STL Index all the time because it truly does save a lot of time from the development perspective. Now that I’ve gotten all of my info for my document object, I am going to check the employee ID. The number one thing that this script hinges off of is the employee ID. If I don’t have an employee ID, then I can’t run my query because I won’t have anything to query against. I need to make sure that I have an employee ID first.

Now, I mentioned a minute ago that now because I have my INDocument object, I can grab whatever field value I want to pull back the value inside of that field. Let’s say my employee ID here is in field four of the document. I could put here, var amp ID = doc.field4. This would return my field four value. Now my employee ID would be whatever value is entered in field four right here, right? Well, I don’t always want to hard code that stuff directly into the script body because again I want this to be something that is able to move forward and change over time is needed. That’s why we defined up at the top of the script in Global Variables, that’s where we define which field contains the employee ID. Now I know that field four is where my employee ID is stored. When I come down here to this logic, instead of just typing doc.field4 and hard code in that right there, I can say doc and then say that the key is whatever the string is tied to employee ID.

This is essentially saying doc field four. This will pull back my field four value of the document. The next step is just my error handling. If my employee ID returns a blank value, then I know that there’s no employee ID and I need to route it to my error queue. Assuming that there is an employee ID, then I can move forward with the process. db = new DBAccess. This is where we’re actually starting the database access connection. DBAccess again is just an STL function where it’s told me I need to say the type, so my connection is through ODBC. I’m passing in my DBDSN name which again was defined inside of the configuration section, so this DBDSN is going to be Perceptive Content DSN. The DB user is the DB username or the database username that was defined at the top and the database password that was defined at the top.

Speaker 3:
Quick question.

Speaker 1:
Sure.

Speaker 3:
You’re using the same ODBC connection as the initial server, is that recommended?

Speaker 1:
No. Generally, what we’re going to do is if we are connecting to a database, it’s not going to be the INow database. Let’s just not say that you cannot use the INow database to store other tables. You can, but if you don’t need to, then I always say stay away from it. This is just for a test example. The functionality is there as an option if you have no other choice, but if you do have another choice, then I would say use a different table connected somewhere else or a different view and a different-

Speaker 3:
Most of the time, you’re going to be hitting a table on your HR system, your student system, which is a whole separate database anyway.

Speaker 1:
Correct.

Speaker 3:
Cool, thank you.

Speaker 1:
When I run this new DBAccess function, that’s going to instantiate my DBAccess. It’s not actually open yet. The next part is where I run the method to open the connection. It’s either going to return true or false, whether or not that connection is open. If it returns false, then I know that I did not connect to the database and I can run through my error handling. That’s what I have here. I’m able to open database, route to the error queue error connecting to database. Assuming that we connected successfully to the database, I can now update my query and run the query. Online 133 here, I have my employee query that I’m pulling in from the top section of my Global Variables.

My intention here is again to replace the employee ID string with my actual employee ID pulled from field four. I can do that pretty easily just with a replace action. This doesn’t require STL or anything. It’s just our standard replace function. I’m replacing the string that equals this exactly with my employee ID that I pulled previously from field four. Once this is done, my query no longer has this employee ID string in it. It’ll just have the actual employee ID and I can successfully run the query. Now, what I’ll do is create a cursor variable. On that cursor variable is just going to be whatever the database returns for my query.

From DBAccess, another function or a method that’s built into that DBAccess is our query. All it requires me to do is pass in the query string. Using DB, I query with my query string and whatever the query returns are applied to the cursor. That query return is going to be an object. The object keys will be whatever columns are returned in the query return and then the keys will then have the values stored in that column saved in the object. If I’m using our database, as an example here, go back over here. If I’m talking about first name, my first name column, when this query returns, I can do CSR first name. CSR first name. This will just equal whatever value is returned in that column from that query return.

After I run the query, I’m going to log the query out and I’ll show you what that looks like when we actually run the query. But this is another thing that you just tied into that DBAccess, it’s very handy. The query will show all of the different columns and all the values inside of the column. So, it’s easier easy for you as a human looking at the log to see what that query return contained. The next step is I don’t want to continue processing this if I don’t get a query return. I’m just doing a simple check of if the last name column is undefined, meaning that when I ran the query, it did not return that column, then I’m assuming that there is no query return and I can error to the error queue.

Assuming that it is not undefined, and it is populated and did return with values, now I’m going to move into my full name data massage. Basically, what I’m doing is setting full name to a blank value and then I’m checking to see if there’s a middle name because not everybody is going to have a middle name in the system. If the middle name column, if the middle name returned is blank, then what I’m going to do is build my full name variable off of csr.LAST_NAME which is the column for last name and should be the value that is stored in last name and then I’m using comma space to add as a string to it. csr.FIRST_NAME, which is the first name column, another space and I’m doing a substring of the middle name, so just to grab the first character and a period just to make it look nice. It should be last name, comma, space, first name, middle initial period.

If there is no middle name, then all I’m doing is last name, comma, space, first name. After that is complete. Now what I need to do is convert my birthdate. Let me explain why I need to do this. If we’re looking at a document inside of Perceptive Content, we have our index keys, field one through five, drawer, document type and our custom properties. These data types are going to be different depending on what they are. Index keys field one through five are always strings. If I have a date like a date of birth and I run the query, and inside of the database it’s stored as a datatype, I try to push that data type into a string like field two, it’s going to give me an error because it won’t accept that data type in that field.

What I have to do to get it into my field two field is to convert that date to a string first and then I can apply it to the index key. That is not the same for custom properties. That’s part of the reason why I have date of bir