When I started working with KTA just the other day, I built many sample processes, along with those included in Kofax’ computer-based training. Well, that included the creation of many users and groups (called Resources in KTA), and assigning them to individual activities, spread over many different processes. Everything worked quite fine, until I encountered the following screen:

Well, that was my use case: I wanted to scrap an existing resource that I no longer needed. KTA thought otherwise: it told me that my resource still was being used in a process map. That was kind, what wasn’t kind: that KTA did not tell me where EXACTLY I had to look. I knew that I created quite a few processes and played around with permissions and resources, but I had no idea where to start. Of course, I could have checked each and every activity in every process (not to mention the different sub-version).

So, I decided to look somewhere else. Note that I’m still new to KTA, and the following might not be the best way to solve my request.. but anyways, one thing lead to the other, and I had a great time writing SQL statements again. And before I realized it, I had built a form that does the following:

The form allowed me to enter a resource name, and then showed me exactly where the respective resource was used. Across all processes. And across all activities. So knew exactly where I needed to go to before I finally could remove the group. (Well, at this point I guess I would have been faster checking every activity by hand.. but as said, I learned something new – so I guess, that is fine).

First things first: we had to learn a little bit more about resources, activities and processes. So I decided to sneak into KTA’s database, identifying the following tables:

It’s quite a nice table structure, and here’s everything we need:

• AW_RESOURCE contains all resources. Every user, every group you create is present in this table.
• BUSINESS_PROCESS contains all you processes, including all version.
• ACTIVITY contains all activities from those processes.
• ACTIVITY_RESOURCE helps resolving the n:m-relationship between an activity and your resources (remember: many users or groups can be assigned to an individual activity).

So, as said, nothing fancy – a JOIN here, a JOIN there, here we go. Only thing worth mentioning that ACTIVITY_RESOURCE and ACTIVITY both are using composite keys (consisting of PROCESS_ID, VERSION and NODE_ID).

DECLARE @resourceName nvarchar(40)

SET @resourceName = 'Approving Officers'


--CREATE VIEW RESOURCES_BY_ACTIVITY_AND_PROCESS AS
SELECT	res.RESOURCE_NAME
		, bp.PROCESS_NAME 
		, a.ACTIVITY_NAME	
		, a.VERSION
FROM AW_RESOURCE res
JOIN ACTIVITY_RESOURCE ar
ON res.RESOURCE_ID = ar.RESOURCE_ID
JOIN ACTIVITY a
ON ar.NODE_ID = a.NODE_ID
AND ar.VERSION = a.VERSION
AND ar.PROCESS_ID = a.PROCESS_ID 
JOIN BUSINESS_PROCESS bp
ON a.PROCESS_ID = bp.PROCESS_ID
AND a.VERSION = bp.VERSION
WHERE res.RESOURCE_NAME = @resourceName

So, let’s search for the group “Approving Officers”:

Voilà, suddenly I knew that this group is used in the process called “Loan Application Demo”, by the activity called “Loan Approval”, and that is the case for four different versions. Not only does the name “Approving Officers” make a lot of sense now, if I wanted to remove this group I had to change four different processes (or deleting older versions of my current process).

So, I should have been happy at this point, shouldn’t I? I decided to take it one step further. Maybe some other designer wanted to delete a resource and had the same problem, without any access to the database, SQL Management studio, or whatever. So, at this point I know that KTA supports custom forms, and that I easily could connect to a database, querying for information. So, let’s start with what I know: building a new form!

This was my initial design: everything’s here that I’d need. A textbox for entering the resource name, a search button, a table to show results. Plus two labels: one to show my how many rows were returned, and another one to provide some explanatory text.

Forms also support actions, and one of them is a database query – that is exactly what I needed. However, since I can not provide any SQL there (not to my knowledge), I could not easily join all my tables together. So, I decided to add a view to my database that would do the job – essentially equal to the statement above, but of course without the WHERE clause. The rest is – as most things, drag-and-drop and pretty self-explanatory. I would query all items in my view, and add the WHERE clause under Conditions: RESOURCE_NAME must match the content of txtResourceName (my text box).

 

Then, we map the elements returned to the table columns, and the count of rows returned to our label:

Finally, we add the action created to the button’s click event. I built another action (a display rule) to set the visibility of the explanatory text only after the button was clicked (“activities were found”). That way, it would not bother us when the page was loaded up for the first time.

With Preview, we can directly test our newly created form only to learn that Jane Pike is directly responsible for one activity:

So, that concludes this how-to. As said earlier, I don’t know if that’s the smartest way to tackle this use case, and maybe there’s a build-in function that I just could not find. Also, the query is not perfect: it would not resolve a user’s groups. While “Jane Pike” is a member of “Approval Officers”, this query does not show us all the respective group activities. Not too bad I would say, as you can easily find out which user is a member of which group. Kudos to Yvonne by the way, who helped my built the form (guess it’s not as easy as I made you think it is).