Listing Communities and Visible Workflows

I’m looking to list all our communities and the visible workflows for those communities.
Does anyone know which database table provides the relationship between RXCOMMUNITY and WORKFLOWAPPS?


The tables that provide that information are PSX_ACLS, PSX_ACLENTRIES, and PSX_ACLENTRYPERMISSIONS. However, there is a helpful view RXWORKFLOWCOMMUNITY. This query will get you what you want.

FROM rxcommunity rxc,
  workflowapps wfa,
  rxworkflowcommunity rxwfc
WHERE rxwfc.communityid=rxc.communityid
AND rxwfc.workflowappid=wfa.workflowappid;

However, remember that the overall availability of a workflow is also determined by the assignment of the workflow to a content type (and the availability of that content type to the community). This SQL will get you the workflow to content type relationships.

SELECT ct.contenttypelabel,
FROM psx_contenttype_workflow psxctwf,
  workflowapps wfa,
  contenttypes ct
WHERE psxctwf.contenttypeid=ct.contenttypeid
AND psxctwf.workflowid     =wfa.workflowappid;

The availability of a content type to a community can be found via

SELECT ct.contenttypelabel,
FROM rxcontenttypecommunity rxctc,
  rxcommunity rxc,
  contenttypes ct
WHERE rxctc.contenttypeid=ct.contenttypeid
AND rxc.communityid     =rxc.communityid;

All of these relationships can be found via Workbench, but the SQL provides you with the ability to compile a report for audit purposes.

Those queries are exactly what I was looking for. Thanks.
Can I just check the second one … psx_contenttype_workflow doesn’t exists as a table or a view on our system. Does this need creating somehow?

What version are you running?

Version 6.5.2 Build 200710P01 (3308) [RX-15948]