Office Hours: INOW Database Querying

This month’s Office Hours features INOW database querying for Perceptive Content (ImageNow). This presentation includes live demonstrations of common database querying that can help you troubleshoot user or content issues, build custom reports, or just get a better understanding of the relational structure of data and content inside ImageNow.

Transcript

Okay. Let’s get started. Quite a big audience today. Welcome to RPI Office Hours. This is our second monthly webinar series. This one is a little bit deeper dive into querying the ImageNow database. Let’s have a real quick slide deck and then we’ll jump right in. I’m going to really glade through these. First, just a little bit about us, almost all of you know this information already, RPI is a professional services firm specializing in the Infor products as well as Kofax and Hyland, which we are partners with Hyland. Our headquarters are in Baltimore, however, we do have additional offices in Tampa and right here in Kansas City. We offer the entire array of professional services offerings.

We are, for Hyland specifically, very experienced with Perceptive Content, which we’re talking about today, enterprise search, Brainware, and on-based across a variety of vertical decisions, so virtual tables, higher education, healthcare, manufacturing, et cetera, et cetera. We’re extensively experienced across our team.

Most of you know me. My name is John Marney. I’m the manager on our team of consultants, developers and project managers working with ImageNow for over eight years on top of the other workload solutions that we offer, Kofax TotalAgility and OnBase and others. And I am a new father, almost five months old.

Upcoming activities with RPI. In September, as part of our webinar Wednesday series, on September 4th, we have, in the morning, Migrating Your Enterprise Content Data to the Cloud. What this is going to go over is, especially for you perceptive content folks, you wanted to have your OSMs or your database, like an Azure server or something like that, the ability to set that up and integrate it with your existing environment. Then, in the afternoon, we have a presentation on What’s New in Kofax ReadSoft Online. ReadSoft Online is a subscription-based invoice processing solution, which we’ve deployed quite a few of, and it’s really cool. This summer, they did a new release with a lot of new features.

Other events that we have coming up. We are hosting a two-day workshop, it’s free, right here in our Kansas City office, October 8th and 9th for Kofax robotic process automation. It’s training and hands-on, includes some food. The only thing you have to pay for is to get here and stay here. We are already over halfway full from registrations. If you’re interested at all, please go get signed up. We’re evaluating doing a second session if we go over capacity.

Then, please come see us at CommunityLive in September. We are having a happy hour on Monday evening at Bar 32, which is at the top floor of the hotel where the event is taking place. Please go register at rpic.com/communitylive2019 and come say hello to us. I’ll be there.

Finally, for our Infor clients, RPI will be an Inforum September 23rd through 26th. If you’d like to schedule some time to meet with us, please go to our website rpic.com/inforum. There’s a handy dandy scheduler right there.

Okay. So, that’s most of my slide deck. Let’s get into this.

What are Office Hours? And what are we doing today? Office Hours are really whatever you want them to be. It can be as informal or formal as you need. It is a demonstration. It’s our training. It could be helpdesk, if you’re encountering some difficulty that you are encountering that you’d like a help with. It’s a working session. It can be fairly technical. Especially today, if you don’t have any SQL knowledge, you may get a little lost. There will be frequent rabbit trails and other topics, and that’s perfectly okay. I want to cover the things that are most beneficial to the audience. Hopefully, a little bit of silliness. I don’t take myself too seriously I don’t want you to take me too seriously. And it really can be whatever you want it to be.

Final slide, I’m not responsible for your actions. I say this because I’m going to give you knowledge to empower you to go conquer your ImageNow database, but if you take that and go crazy with it and delete a bunch of stuff that you shouldn’t, then I’m not taking responsibility for that.

Practice safety first. Select statements in your production are probably okay as long as they are returning to future data sets. Don’t run inserts. Don’t run deletes. However, if you ever get into a situation where you think you need to, I’m happy to support you in doing so. And it’s always better to go use your test environment as playground than in doing things in production. Learn from my example.

Okay. I’m going to hop over to my virtual machine where I have some stuff prepared. Okay. I’m hanging my shared screen. There we go. Okay. Please, please, please, I’m going to do a quick demonstration of a couple basic queries, but at any point please just pop in throw questions into the GoToWebinar and I’m happy to take questions as they come up. I will take a break here about 15 minutes to take questions as well.

The ImageNow database is generally INOW, or INOW6 if you’re coming from an older version. I have this here in my SQL server management studio. The basic table that almost every query is going to start with, or diagram, is your IN DOC. IN DOC keeps a record of every single document that exists inside of the system.

I did a right-click, select top 1,000, just to get a sample. You see it lists up all of the columns that are available. But at its most basic, you’ll see that there’s a Drawer ID, and then folder tab, Field3, Field4, Field5. Those who are familiar with older versions of ImageNow, Field1 and Field2 used to be called folder intact.

Here’s your index keys. There’s your unique document ID. Here’s the document type. Right? So, this should look pretty familiar.

Let’s say, at its most basic, I’m going to trim this down and get rid of my columns. Let’s see this, I want the index keys, so I’m going to delete these. Okay. So, this query returns the index keys.

But let’s say I want that drawer name. All right. This is the unique ID for what that drawer is and inside of ImageNow. I want to pull back the actual drawer name instead of unique ID. So the first table I’m going to sync up with here is IN DRAWER. IN DRAWER contains a list of all the drawers that are set up inside the system. If I do a quick select on this, you’ll see drawer ID, drawer name, description, just like it would appear inside of your management console.

Back to my query, I’m going to do a join drawer. I need to give that a name and I’m going to relate them using that drawer ID. So, DC Drawer ID equals DR Drawer ID. IN Drawer is an acronym, so I need to provide the end user schema on each table. Okay, something like that. I’m going add the index name as well. Okay. Then, I’m going to change the Drawer ID column to Drawer Name. So, DR Drawer Name. Okay. So, I rerun this query…oh, it’s not right. I rerun this query and you’ll see that now it is populating the drawer name instead of the unique ID. I can do the same thing with document type ID if I want against the IN DOC type table to get the actual name of the document type. All right. Let’s take this then one step further.

Again, if you have any questions around what we’re doing here, please throw them out there. Any data that you want to see presented or any data you want to see how to get to, I’m happy to demonstrate.

We’re going to take this one step further. Probably the next most useful is a custom property. Custom properties in the ImageNow database are stored in a really wacky way. We’re going to walk through it. But first thing to do is pair down this data set to make it a little more usable.

I want to do Where Drawer Name equals AP and I’m going to do Where, one of my index keys, equals…how much shall I do? A wild card. All right. So, this returns about 20-some documents. Okay. So, these are some documents inside of my AP drawer. I’m going to now join this up with the custom property table in order to pull back some additional information.

What I will do is, the first thing I have to do is join this with IN Instance table. If you start IN DOC, the next layer down is IN Instance. IN Instance contains a record of almost every single item that exists in the system. Whether it’s a configuration item or a document or project or whatever, it’s going to have an IN Instance ID.

If you select, there is an Instance ID inside of the doc table. If I do this, select inside the doc table, this is the Instance ID for the document. So then, if I join that up to the Instance tail, IN Instance and I relate them using the Instance ID, now I can retrieve information out of the IN Instance table.

There’s one really important field that exists in the Instance table, IN Instance instead of IN Doc, and it’s Created Date. There is a Created Date column in the document table. If I select the document tables creation time, it’s actually going to return a value like this: 1970, January 1st, 1970, which in database peak is a blank date, basically. Actually, the date for a document when it was created is actually stored in the Instance table. If I change that to be the Instance creation time, you’ll see that that is now populated with actual value.

Speaker 2:

John?

John Marney:

Yup.

Speaker 2:

Is there a way to pull a list of users who have private filters on a view in an Image Now workflow?

John Marney:

Wow, that’s a great question. I actually don’t know and we can find out together. My guess is that there should be a way to do it because you can see the private filters from inside of the view setup and you can see the users that hopped in. So, there should be a way to do it and we can definitely figure that out. I’m going to wrap up this custom property demonstration and we’ll dive into that. So, a great question. This will just take a couple of minutes here.

Then, the next thing we need to do is identify what custom property we actually want to pull back. The list of custom properties and their unique IDs are in the IN Prop table. If I do a quick select on IN Prop, you’ll see property ID, property name, type, active, things like that. I actually want to get the vendor name custom property. I scroll through here, take this unique ID, and I’m going to come back here.

Now, there’s still one more table we need to relate and that is the dirtiest table in this database. It is called the IN Instance Prop. An IN Instance Prop is a list of every single document custom property that it exists and a column to indicate whether it’s a string, a number, or time value, and the value contained within.

What we’re looking at here is the Instance ID of the document or project, if you’re using folders, the property ID, the unique ID of the actual custom property being used, and then columns depending on what type of property of this. Now you see that these columns exist regardless of whether it’s a string, a number, or a time, custom property.

This is what is referred to in database because being non-normalized. What happens is, every sing