In this blogpost, we will learn to identify all Power Automate flows that reference or interact with a specific column or field within a Power Apps / Microsoft Dataverse. Before we begin, ensure you subscribe to CRM Crate to remain informed about the latest developments in the Power Platform field.
Power Automate is a cloud-based service from Microsoft that enables users to create automated workflows between apps and services to synchronize files, get notifications, collect data, and more—without writing code. It integrates seamlessly with the Microsoft Power Platform, including Power Apps and Dataverse, which is the underlying data platform. In Power Automate flows, fields or columns from Dataverse tables (used in Power Apps) are referenced through dynamic content or expressions. These fields can be used as triggers, conditions, or actions within a flow. For example, a flow might trigger when a new row is added to a Dataverse table, and then use specific column values to send an email or update another system. This tight integration allows for powerful, data-driven automation across business processes.
How to find all Power Automate flows that reference a specific table column or field?
To identify all Power Automate flows that reference a specific column or field within a Dataverse (Power Apps) table, we can query the underlying data stored in the workflow table, also known as the Process table. Specifically, we focus on workflows where the category is set to “5”, which corresponds to Modern Workflows—these include flows created using Power Automate.
The key to locating references to a specific column lies in the ClientData field, which contains compressed business logic and metadata about the flow, including references to table columns. Since this data is not directly viewable within the Power Apps interface, we must perform the query using SQL against the Dataverse database. By searching for the column name within the ClientData field, we can effectively identify all flows that interact with the specified column.
The SQL query below retrieves all Power Automate flows in the environment that reference the column ‘cc_eventname’. You can replace this column name with the specific schema name of the column you want to search for.
-- CRM Crate (WWW.CRMCRATE.COM) - By Prashant Tirlotkar
SELECT wf.name, wf.workflowid, wf.clientdata
FROM dbo.workflow wf
WHERE wf.category = 5
AND LOWER(wf.clientdata) LIKE '%cc_eventname%'
In conclusion, by querying the Process (workflow) table in SQL—specifically targeting Modern Workflows (category “5”) and inspecting the compressed ClientData—we can effectively identify all Power Automate flows that reference a specific column in a Dataverse or Power Apps table.
