Perceptive Content INOW Database Querying

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 single time a document gets created, if that document type has 20 custom properties assigned to it, 20 new rows get inserted into this database.

That’s why it’s really important from an ImageNow design perspective that we reduce the number of custom properties to the very bare minimum needed. I have clients with very high document volume that this table has gone to hundreds of thousands of rows. SQL server can handle very large data sets, but there does come a point where scaling up that large just for one table can be really problematic to your query. It causes things like workflow queue views and just normal document views to not really execute very well anymore.

But that notwithstanding, I have to join the IN Instance table to this one in order to fetch my custom property value.

Back to my query, I’m going to do a left outer join because I can’t guarantee that the document type in this document list actually has the custom property. So, Left outer join IN Instance Prop Inp On. I’m going to join it to the Instance table… Actually, I need to join it to the IN Prop table…no, I’m sorry, Instance. Okay.

Then, finally, I need to add a condition in here because, if I do this, it’s going to pull back all of the custom properties for each document. That’s actually going to make my data set pretty big, 498 rows. I want to reduce this just to one custom property. I’m going to use the property ID… No, I’m sorry, Prop ID. I fetch out of that IN Prop table. Okay.

Now I have correct number of rows. I need to add in the actual column that I want to pull back. I’m going to do that the IN Instance Prop table dot string value because it’s a… I know it’s a string custom property because it’s my vendor name. I’m going to name it, so it’s clear. And re-execute. You’ll see that it populates the vendor name for these records.

Now the challenge here is that every new custom property that you want to add to a query, you have to do another join back to the IN Instance Prop table with a different unique ID. This can make the queries really messy because having to create table and one of the largest tables in the database over and over and over is very expensive creation. All right.

Let’s start with that question that we got in. If you have any questions about what I just demonstrated, please draw them in. We’ll get to as many as we can through this session. The first question was, “Is there a way to pull a list of users who have private filters on a view in ImageNow workflow?”

Okay. There’s a few components here. There’s going to be tables for the workflow, there’s going to be tables for the views. Actually, when I first heard that question, I thought it was for the just the views. If it’s in a workflow view, we’re going to have to see if this is even in the database anywhere. Let’s start with the view tables and see if there’s anything in there.

Your normal view tables for your document views start with IN View. You’ll see if I do a quick select on there, there’s a bunch of views set up, view name, unique ID for the view. This actually contains the filter names inside of them. By the way, I’m doing mostly investigation. I’ve never actually tried to seek with data out before, so we’re learning together.

You’ll see that there are view types. It looks like zero is a view and one is a filter. First of all, I think I would start by just narrowing this down to filters. View category is looking interesting. Let’s see if I can figure that out. Seven. Six. Probably something I want to do is actually make sure that in my system I have a private filter set up on workflow queue. Let’s try that real quick.

Any questions we’re getting while this loads up? Sunil is sticking notes in the application where users put notes. How do I get that to the information in the database? The actual sticky note information may not be in the database? We’ll find out. Is there a way to create a list of users so it can be compared to a list of AD active users so I can remove the term users? Yes. Querying AD is actually the more challenging part. I guess if you already have a list, then, yes, you can absolutely do a list of users out of the database.

How can we get to the path of the OSM file for a given document? Unfortunately, that is encrypted inside of the database and we can’t get to it. However, there are some tricks to get it through the API. We’ll talk through that a little bit more, but to answer your question directly, the image path in the OSM is not in the database at all, or not accessible in the database.

Quickly, I’m just going to set up a private filter on the workflow queue. Actually, here’s one already. It’s called Recently Routed and it’s under my accounts payable workflow. It’s not under a queue, it’s actually just under workflow, but it should effectively do the same thing. I’m going to pop back to my list here. I see that actually on this list there’s one called Recently Routed has a View Category of six.

Without being 100% sure yet, I’m guessing that six indicates it’s on a workflow process; possible that seven is on a queue. So, I’m going to go back to here and add one to a queue real quick. John1, saving that private filter under the AP Approval Error queue. Okay.

John Marney: Back to here. I’m going to rerun this query so I can see that new filter. Okay. It looks like Recently Routed, which was the one on the workbook process with view category of six, and the one I created on the queue is also six, so that doesn’t indicate whether it’s on a workflow or a queue.

There’s other information. There’s a parent view ID, which I’m guessing that has to relate back to either the workflow queue view or the process view. Ultimately, if you needed to join those back to the parent view, we could do that to identify where it exactly it exists. But then, you also have this private user ID. I’m going to take a stab that this is the user who set it up. But we’re going to verify that.

User IDs are sorted in a separate table. First, I’m going to filter this down to: And View Category equals six. So, these are all of our private filters on workloads. Now I’m going to join this up to the user table. Inner join IN user… I think that’s like this. It might be the… Double check with the tables. It’s called… It’d be helpful if I knew my alphabet. Here we go. Oh, IN SC USR. That’s right. IN SC USR. So, the actual user list is… SC is short-hand in this database for security. It’s actually user list that’s contained within the IN USR table. Give that a name: ISU. And we’re going to join them by that, so, ISU dot… I think it’s user ID equals, what is this table name? INV.Private USR ID. And we’re going to add a column for the ISU.USR Name. Okay, give this a roll. And here we go.

I was able to identify the user name of the individual who set up or owns this individual private filter. I think that answers that question. We just reread it real quick. Pull a list of users who have private filters on a view. So, yes, effectively, you could pair down the results in this query to get to that. If you have any follow up on that, please throw it in there and I’m going to move onto the next slide. All right.

The sticky notes questions. Annotations are interesting because the information about the annotation and a lot of the metadata for the annotation who applied it where it exists on the document does exist in the ImageNow database. The actual annotation image exists inside the OSMs. Sticky notes are kind of unique in, like I said, text annotation because there’s content within the annotation that changes based on user input. This should be pretty easy to find out. I don’t know whether the sticky note information exists in the database or not. We should be able to get to the annotation tables and find out very quickly.

I think it’s just this subob annotations. Just checking real quick to the IN. I think it’s IN Subob Annotation. Let’s start there. To follow through the table logic here, on a document you have IN Doc, then flow that as IN Instance, flow that as IN Version. Then, you go into logical objects, which are, let’s say, your pages for document. Below that are physical objects, which are the actual files that exist in the OSM, which we’re going to use as part of our next question. Related to the logical object is the sub object, and sub objects can be anything from a form to an annotation to, I guess primarily just those. Other information that’s associated with the document, but isn’t actually part of the document itself.

Let’s go to the IN Subob Annot. Again, we’re diving into a few things that I haven’t necessarily explored too deeply before. You’ll see that the information here has a lot of positioning information, lots of color information. These are probably going to be mostly things like stamps. 137 rows. Not seeing a lot in the way of, well, values.

Again, I should probably go apply a sticky note to a document, just to make sure I have data in the database directly. I’m going to open this file, maybe. I’m going to apply a sticky notes. I’ll say it’s John’s Test Sticky Notes. And just in case I need it, I’m going to grab this document ID, store it right away somewhere. All right.

This one had 137 rows before. So, it’s not in this table. I just applied a new annotation and it didn’t… Here, annotation tests. This is looking promising. Aha. This looks like it has annotation text. These are going to be stamps. I’m guessing the latest entry in here is John’s Test Sticky Notes, I’m hoping. There are some pretty interesting stuff in here. Yeah, I’m not seeing it.

About the only thing I could do here to dive into this further or get a better answer, because I really need to start with the doc ID and relate it back to the subobject table and then filter down to the individual annotation that was applied, because I’m not seeing what I just applied and I think it would be here.

Yeah. Let me knock out a couple of the easier questions and we’ll revisit that shortly. Query list of users? Yes. So, users are very easy. It’s actually that table I joined to earlier. The question was, “Is there a way to query the list of users so it can be compared to a list of active directory active users so I can remove the term users?” I mean, I would say, first of all, you should probably explore setting up the user replication agent, which can handle this automatically, deactivate users that are deactivated in your active directory.

But borrowing that, it’s really just this IN SC USR table. This contains every username, all of the information that exists in the user setup in the management console, and it includes this active column, which should just be one if active and zero if they’re not active in Perceptive. That’s really as simple as it is. This username is set up with in Perceptive, which generally should match their active directory username. All right. Let me know if there’s a follow-ups to that one.

Path to OSM file for a given document from the database? I’m going to answer this one real quick. Like I said, no, you cannot get to the path of an OSM object. Let me show you what it does have.

The IN PHSOB table is a list of every physical file that exists in the OSM1, or whatever OSM you have set up. If I do a quick select on that, you’ll see that there’s a PHSOB ID, OSM Tree ID, which you can relate back to the OSM Tree tables to see where it exists. You’ll see there’s this OSM Path column and it’s not populated for some of these. I can’t say exactly why, maybe the file is deleted. It’s tough to say, but you’ll see it is populated for at least one of these.

This is an encrypted file path. Hyland has a tool that they don’t share that can decrypt this. I don’t know whether they charge for it or whatever.

There’s actually not too difficult way to get to this information, however. If you have an iScript license, which a vast majority of ImageNow users do, we can write an iScript that uses the API call to try and export a document. But you give it an invalid file path to export to. It will actually log out in the iScript log with a file, the sym file path it’s trying to retrieve it from and then it never actually exports it because you give it an invalid file path. You do that and loop through whatever documents in a view or what have you that you’re trying to get a no-sym path for and you can effectively get the information that exists behind this encrypted script. That’s just a little bit of a trick that we have run through the years.

Next question. Are there any data texts available for ImageNow like there are in Lawson? The table uses spilled information relationships to other tables. That’s a really great question. Hyland does have an ERD. For those who aren’t familiar, it’s entity relation by relationship diagram, relation diagram. Basically that defines the tables, the columns inside of them, and the way they relate to each other, generally also includes the primary keys and foreign keys in each table.

So, they do have that. There used to be some amorphous process that you could go through to get a hold of that. I don’t know whether they distribute it anymore or not. I know well enough to know that, generally at this point, I don’t really need, it so I haven’t sought it out in many years. That’s the only documentation I am aware of and the only documentation I’m aware of that has ever been distributed to any clients or partners.

What they don’t have that I that you’re really asking about was a data definition to say, “If I look at the IN DOC table, what information does each column represent?” To my knowledge, that doesn’t exist and that’s locked up in the heads of folks like me, and why we’re doing webinars like this one. Unfortunately, the answer is no. I’m pretty sure. If you have any specific questions, I’m really happy to help. Okay.

Follow up on the private filter question, basically, thank you. You’re welcome. Will the same query work for private filters under documents? Yes, it will. So if I go back to that real quick, I need to close up some of my junks here. I do have a couple them, so going to cleanup.

That’s user table, literally that one open. On this query, the only thing that was narrowing this down to workflow filters was this View Category equals six. If I don’t do that, we will actually execute across all private filters. You’ll see there’s View Category one, which is a normal document filter. but in this case, it’s private filter, not a public.

Then, I don’t know what seven is. It doesn’t have a view name. Let’s see if we can see who created it, IN owner. I’m guessing that this might be one that has been deleted. It’s tough to say. But, yeah, this is all private filters right here. All right. Next.

You can get that comment, you can get a sticky note data via iScript, if that helps. Excellent point. We’re going to revisit that to see if we can drill down and actually identify where exactly it exists. But generally, it probably is easier to get to that data via iScript just because the API calls that retrieve the document data anyway, make all of the document metadata through the document object available very easily instead of having to go join a bunch of tables.

The next question, is there a data dictionary furnished in tables? Yeah, similar question, already answered. Not to my knowledge. If it exists, I’ve never seen it. And it’s under tight security at Hyland headquarters.

What is the correct way to delete users through SQL? We have over 2,000 users we’d like to delete? Excellent question. The safest way is going to be through the API. I probably wouldn’t try to do that through SQL, truthfully. Again, if you’re verging on territory where… I’m going to caution you that deleting anything out of the ImageNow database is pretty risky. I’ve done it before. You can definitely get away with it.

Really if you delete users out of that IN SC USR table, that is the master record of users inside of Perceptive. The problem you’re going to run into is that there are so many related tables that also have user information that point back to that. I mean, look at views, look at documents, et cetera, et cetera. I think that it’s going to prevent you from deleting users very easily.

You know what? Let’s give it a shot. So, I pulled up the IN SC USR table. If this doesn’t work really easily, we’re probably going to move on because I think that it’s going end up stopping me due to a relationship constraints. Who knows which of these I can actually do without? Let’s say this one. I’m going to take that user ID. Delete from IN SC USR. I’m going to do my fully qualified. Where USR ID. So, what happens? Whoop. Sorry. So, it let me delete them.

Just to verify that that doesn’t do anything incredibly system breaking let’s go login to the management console and see if the record is gone. It should be gone here. I think probably what would happen is that if you ever tried to pull up a document, let’s say you pulled up a document with that user made created, instead of the username displaying on the created by, it would probably display the unique ID. As long as that’s okay. So users…if you process your can…so, that didn’t delete it.

Requerying here. Is that the one I deleted? Oh, I did China. That’s right. So, it did delete them out of the system. And just to see, if I ever go look for a document that was created by that user, document property…where is it? User created by is equal to approcessorchina. Well, they probably never created any documents. So, it will let you delete them directly out of that table, but that’s probably not something I would advise you do to the table relationships. Again, you could write an iScript to delete a bunch of users that would respect all of those things a lot easier.

Someone commented that they asked for either the ERD or the data desk, I’m not sure, five years ago and they were not willing to share. Yeah. I actually started my career at Perceptive Software Support back in 2010, and that was a common request and usually the answer was no. However, I know that there are organizations that got it through some sort of begging or something. It is possible, I don’t know if it’s still possible.

Any suggestions on how to determine the amount of time an item spent in a queue? Yeah, totally. That’s absolutely available in the database. We have about 15 minutes here. I’m going to take a quick glance through the rest of the questions to make sure that I can hit on probably the broadest or the ones most easily demonstrated. Diving into the workflow tables is a big, big conversation. It could probably be its own full hour.

Let me look at these questions real quick. Do you know of any cons of converting your database to Unicode? I don’t know of any cons. We have done a number of Unicode conversions, there are risks, but mostly just from a project planning perspective, it can take a long time to do. I don’t know if there are any risks or cons from a good data storage perspective, but for the most part, every table, except your string table, or every column and table except your string columns are the exact same as the non-Unicode database. So, it’s really not that you’re converting a whole day to day, you’re just converting the string data types. So really fairly, fairly simple.

Another reason you don’t delete is for document history. Yes, definitely. Yes. Just to give you heads up, don’t delete users out of the database directly. Completely agree.

I’ve noticed that our database shows time in the GMT time. Yes, it does. You always have to allow for that one querying. Simulator localizations of the time fields on the database. Well, yeah. This comes up every time we get into query. You don’t want to is, unfortunately, an answer. You could probably go manipulate the data then store it in whatever local timezone, but the ImageNow server is expecting the date in GMT and it wants to display date times to users in its local time, so it’s going to perform that conversion no matter what.

There is no way around that to my knowledge. I’m 99% sure there isn’t. The system was designed to store in GMT, specifically as an enterprise application so that if you have users across multiple time zones, it respects where the locality of where they’re logging in from. I think actually probably a proper design and it just unfortunately does have to be manually accounted for during direct database screen.

A comment that says, “If you have Business Insights, they’re more likely to grant your request for the ID. They said they have it, but when we upgraded a new one we had to pay complete and ask a request again.” Yeah, I imagine. “In fact, in seven two, I think seven two is probably the last major schema change for the database. And some things did change. So, the upgrade or the database changes when seven two actually do take a while to apply to the database. Whereas, say, seven three, the most recently released are real quick.”

No. In fact, I don’t think there is a schema change for seven three. So, yes, they do change. For example, that created time that I demonstrated at the beginning has moved in the [inaudible 00:46:27], and that was not always the case. So, yes.

One comment is that, so since Business Insight was brought up, obviously, if you have a reporting tool, the goal is to use that instead of going into creating directly. I’m much more comfortable just getting into the database and I find Business Insight as a tool to be clunky. Maybe that’s understating it, for those of you who are familiar with it. I generally, even when I go write the BI reports, I still write the SDL directly instead of using the reporting objects inside BI.

I’ve done time zone report thing, not worth the effort. Noted. Yeah. Even the client is buggy. If you search today, it gives you GMT today, no local. That’s interesting. I don’t think that’s supposed to be true. If I go look back to my first career I started with and look at the created time for document, this is close enough, so I should have a creation time. No, this is not what I want. This one? I’ve got too many windows open here everyone. Coming off the rails. All right.

If I just add the creation time… It should be that these values are GMT and I can’t remember whether it displays the local client or the servers. I think it’s the server’s time zone is what ends up displaying when a user [inaudible 00:48:30]. Either way we don’t have to dive it into that exact thing, but there is a difference between what’s in the database and what it should be.

I was able to get one after filling out a few legal forms getting signatures. Okay. There is a process to get the ERD. How do you update the doc type depending on certain values and F file? Generally, for a volume of document manipulation and manipulating the metadata, we would want to use iScripts. I thought it’s safer. If you wanted to update a doc type for a document via the SQL, you definitely can, it’s really fairly low risk. Well, I take that back. If you went in and updated the document ID in this table, in the IN DOC table for document, the system would respect that and use a document ID.

However, as we saw when you create a document it adds all those custom properties whether they’re filled out or they’re null or anywhere between to the IN Instance Prop table. If you change the document type manually, it’s not going to necessarily insert all the custom properties. My guess is that they would probably freak out on you when you went to open that document. So, I would definitely use an iScript to do it. But I still continue to lay out the logic for system to do that for reindexing.

If I create the document at 5 p.m. PST, it won’t show in relative search for today. That’s interesting. Now that you say that I do recall there being issues with how timeframes are submitted to the server to retrieve documents versus what is displayed. I think you’re right there. There are some inconsistencies with what’s displayed to the user versus what’s submitted to the server versus what it’s stored on the database.

What is the full radio name stored as in the database? Can you run query for documents under a particular folder? Yes. Actually, that’s not too hard.

I do want to circle back to the sticky note question and the workflow history question because we’re running out of time. The last question in this webinar session like this for iScript, please. It’s on the list, but we end up deciding we’re going to do it this year.

Speaker 2:

Let me look at the schedule.

John Marney:

We really want to do it. We know there’s a huge hunger for it. There’s a ton of useful, to me, I think it’s useful to use cases for iScript. It is just so hard to actually condense useful knowledge down into an hour. We could do multiple sessions what-have-you. The most effective thing you can do, if you want to leverage iScript, is get a person who has some basic JavaScript knowledge, make sure they have basic ImageNow knowledge and let’s do a train. It’s not that hard once you get some great examples and once you get someone who really knows what they’re doing to show how to do it.

Our lead consultant Michael Madsen just went on site and gave to one of our clients and gave and iScript training to their entire team. We can do multiple people, I mean, we consider doing an on-site workshop here in our office for iScript training. We’re probably going to do a webinar like this for it, but I’m just not sure how much people are really going to be able to take away.

Okay. Let’s do a lot of few things here. We’re probably going to run over a little bit, but I do want to get these answered. Let me start with the workflow history question. The question was, actually calculating the amount of time some things more flow. So, there are a number of workflow tables, they all start with IN WF.

The first one is IN WF Item. This is a list of every item inside the workflow. If you do a select on this table, you’ll see that you have: an item ID, so this is a workflow item unique ID; the object ID, actually, the object ID is either the document, I believe the document ID or the folder ID, what is the item that’s actually in the workflow.

And any of other metadata, right? Like the creation, user ID creation time, more appropriately, you have some other times like queue start time. If you’re trying to build a record of the entire time the document is in a queue, you have the queue start time and you should have a queue in time, maybe not. What gets difficult is you have state start time. So, every time it’s open right it sets to a different state so you can perform calculations based on when it is open-close, open-close. Then, it actually has really a separate state for when it’s routed out.

If you end up doing is calculating time between queue start time in whichever state number is routed out. If I want to pull down the history for just one object, let’s start with that, I don’t know anything in here that would have multiple entries, but let’s just go ahead and take this one for example. Yeah. This one only has one entry, which means it was routed into a queue and never touched again. It’s a little difficult to do this example on the fly. But I believe that the state detail for routed out is going to be that 25, Just from looking at the data.

If you calculate the date diff between queue start time, or you can do states start time for when it was routed in, versus the state start time for the routed out action, you could get the time in that queue. You add all those together for the entire history of the document and you have the entire time that it’s in the workflow. Or just the oldest start time versus newest start time.

We have written many reports that do workflow efficiencies, calculating, those kinds of things. The important thing to remember is that all of your information is not right here in this table. IN WF Item is for items currently in workflow. They also have where they currently are. That’s why I believe if I didn’t find multiple results. They also have item history, they have Item Hist, which contains the history for every document.

Additionally, you also have these two archives tables. If a document has hit a complete queue and are archived out of workflow, then the data is moved out of these tables and into the archive table. If you have an archived item that has completed its workflow process, you actually have to join potentially up to four different tables to get to the complete workflow history.

And it can be kind of challenging. There’s a lot of gotchas inside of that, such as, what if a document restarted the entire workflow process? Are you trying to report on the total turn time from beginning to end? Or are you trying to get a total turn time for the lifecycle of that workflow document? Because it just depends of whether you’re trying to find out your aging items in workflow or you’re trying to find out how efficient the users are processing things through workflow.

That one, if you dive into deeper you probably need to reach out to us, but hopefully, that gets you started.

The final one…not so much iScript training specific to useful Perceptive Content related functions with iScript. Okay. We can potentially do that as part of an iScript webinar. The final thing we’re going to do is circling all the way back to that sticky note content. Let’s see, it’s going to be a little challenging. I’m going to pull back my query for my documents. My cursor stopped working. I think this is it. All right. First of all, let’s get the document that I just added. I had stored that document ID somewhere. Let’s just do it again.

I know people will probably have to leave as we’re approaching our end time. I’m going to try to finish up this question. If you have any ideas with other things that you’d like to see presented, please let us know What’s our next one on?

Speaker 2:

The next one will be ImageNow cap.

John Marney:

Okay. We’re doing how to use ImageNow cap next month. The month after that, I believe we’re doing the learn modes and learn mode scripting. I think we have those all available for sign up on our website; or if not, there will be soon. Please go ahead and throw those out there. Again, if you have ideas for things that you’d like to see presentations on, let us know. All right.

I have this ID. I’m just going to get rid of these conditions and say: where document ID equals this. All right. So, this is the file that I added the sticky note to. We have to go all the way down the chain to get to the logical object table to drive it up to the subobject table, I think. Just to verify that that’s true, I’m going to query back the subobject table again. PHSOB, maybe. Okay. I have scrolled all the way down to the subobject. Okay. We’ll do join IN USR dot…so we do version next, On DC version ID? DOC ID equals Inv DOC ID. I’m going to join IN LOGOB On version on account.

Hope we’ll have things related real quick. Pulled this down a million times. Version. All right. Logob on version. Version ID equals og, version ID. So close. The final thing is IN USR.PHSOB On po.PHSOB ID equals lg.PHSOB ID. Okay. This should work. Cool. I’m just going to add in a column from PHSOB, so po.PHSOB.ID. All right. So, this is the PHSOB ID for this page.

Actually, if I had multiple pages I think we could edit this query to allow for multiple results. Let me back up these custom property since we don’t really need them. Oops. Okay. Then, I need to join up to the subobject. Here’s where we were trying to identify these sticky notes, I want version like that. Subob.

What I’m going to actually do, I’m just going to query DC DOC ID, on, so PHSOB ID. I’m going to get the subobject name. This should give me list of the subobject that exists on this document. Okay. Prospect this out, maybe that’s the wrong table. Maybe I need the annotation table. Okay. It doesn’t exist in the Subob table. Let’s try the other one. Interesting. Not on this one. Maybe document actually, not the annotation. Well, I don’t know where the [inaudible 01:06:03]. Where could it be?

We’ve identified that there are no subobject listed for this physical object. Just to double check that I didn’t mess something up in the query, I’ll take this PHSOB ID and just query the Subob table. Yeah. No subobjects. The annotation is not listed here. There must be another relationship.

The other thing is that… again, the other way to make sure or to check this is, if the text isn’t stored in the database, it’s going to be in the OSM file. So, let’s just check that real quick. Subobject isn’t in OSM too. I go to the very last thing created. It’s not in here, which would make me think it is in the database somewhere. So, resort to just poking around.

Well, I may have to throw in the towel on this one for now. The thing that’s stumping me here… I feel like I have done this before, but I can’t be certain. I’m not able to relate the existence of that annotation to the document. I see other types of annotations where I would expect to find them, but I’m not finding the sticky notes. I’m wondering if sticking notes is stored on separate tables specifically because they’re a little bit different.

Just to prove that, I guess if I add a stamp to this. Let’s say stamp that’s just for x-ing out. Just like that. The query I’m using should now turn in something. Yeah. A column was added to the Subob Annotation text table. That would mean a…maybe additional. It may be the annotation actually started with physical object that were sticking up. I’m probably going to go ahead and pass them on for now. I’ll do a little more research and see if I can get back to you on it.

Last couple questions, start keyword, free form, fewer table maybe. Let’s just see. I know that there’s tables here that have a doc keyword. Okay. I think these are notes. I’m not seeing anything like that. Yeah, maybe. Well, all right. Keyword table looks like it’s your top notes.

We’ll do some more research. I’m going to go ahead and call it quits for today. Thank you everyone for attending. It’s been just so much fun. I hope you’ve gained a lot from this session and you’ll join us again for our next. Thank you everyone.