AutoMate Database Schema JOIN Question

Greetings,

Does anyone know how to join from the ScheduleTriggerConstructs table to the AutomateConstructs table in order to get a list of workflows that are associated with certain schedule triggers?

I am using BPA std.v.9045 and cannot find any built-in views that will show me what I need, so I've gone into the database to try and create a query to get what I need.

Thanks in advance.

Casey

Hi Casey,

In the Details section of an objects properties, you can see what workflows it is associated with.

Thanks Justin, I see it now.

We currently have 231 Schedule Conditions, which I am attempting to clean up/get a handle on. Clicking through the GUI and manually noting the associated tasks would be extremely time consuming. Therefore I would renew my request for information on how to join the tables so that I can create a query (or report) in order to get the list all at once.

Thanks,

Casey

I'm not running v9, but here's the query in v10.

SELECT workflow.ResourceName AS WorkflowName,
workflowconstruct.ResourceName AS ScheduleName
FROM workflowitemconstructs workflowitem
LEFT OUTER JOIN automateconstructs workflow ON workflowitem.WorkflowID = workflow.ResourceID
LEFT OUTER JOIN automateconstructs workflowconstruct ON workflowitem.ConstructID = workflowconstruct.ResourceID
WHERE TriggerType = 3
ORDER BY workflow.ResourceName,workflowconstruct.ResourceName

You only need the scheduletriggerconstructs table if you want to know details about each schedule.  If you're just looking to see which schedules are assigned to which workflows, you only need workflowitemconstructs and automateconstructs.  workflowitemconstructs defines which items are used in each workflow, and automateconstructs is used to get the names of those objects as well as the name of the workflow.

Since you're looking for unused repository objects, you can easily query for that directly.  Here's the query I use that will return all unused tasks/processes/conditions:

SELECT ResourceName AS ItemName,
CASE ResourceType
WHEN 2 THEN 'Task'
WHEN 4 THEN 'Condition'
WHEN 40 THEN 'Process'
END AS ItemType
FROM automateconstructs
WHERE ResourceType IN (2,4,40)
AND ResourceID NOT IN (SELECT DISTINCT ConstructID FROM workflowitemconstructs WHERE ConstructID<>'' AND ConstructID IS NOT NULL)
ORDER BY ResourceType,ResourceName

Thank you kind sir. I came up with one last night, but yours is much more succinct.

Casey