How to Filter and Fetch Entities with State for Flow Execution Extensions?

hannahmontanna

I need to filter and fetch entities based on their state but cannot include the state in the Fetch Entities step due to the state being tied to a separate table (folder table). Is there a way to fetch data and filter by state in one step?

Manisha_Decisions

Unfortunately, you cannot directly filter entities by state within the Fetch Entities step because the state field is linked to the folder table, while the flow execution extension data (FEE) is tied to a different table. To resolve this, you can use a SQL parameterized query to join the relevant tables and filter the data based on the state.

Here’s a SQL query workaround that can help you achieve this:

SELECT * 
FROM entity_folder
INNER JOIN flow_tracking_folder_data
  ON entity_folder.extension_id = flow_tracking_folder_data.extension_id
INNER JOIN YOUR_FLOW_EXEC_EXT_DATASTRUCTURE_TABLE
  ON YOUR_FLOW_EXEC_EXT_DATASTRUCTURE_TABLE.id = flow_tracking_folder_data.flow_tracking_ex_id
WHERE entity_folder.state = @folderstate
AND name = @firstName

This query joins the entity_folder table, flow_tracking_folder_data table, and the flow execution extension table, allowing you to filter by state and other fields. You can pass the state as a parameter in the query when executing it in your flow.

You can also refer to the following documentation for more details:

• Add Query to External Database
• Using Parameterized Queries

Haim

Actually you can do this, it's just not intuitive to work out, I got it from another post on here:

The state is stored with the folder that the case entity is associated with. On your fetch entities, set the type to be a folder, and the extension type to be your case type and youll be able to set a field to state.

So the fetch will generate the JOIN for you, and it should be more efficient than retrieving them all and filtering by the state, unless the compiler is super smart I guess.