display all open opportunities for all accounts in hierarchy

A related list can highlight open opportunities on the account , for those people who find it too much work to look themselves to make it easy to see them. Recently a power user raised the need for finding open opportunities for other accounts within the hierarchy. So we built a screen flow to do this

It was fairly straightforward, thanks to the relatively recent addition of the IN operator for get elements.

It does require a get element in a loop, which is generally bad form but should be fine in this use case, as we are in a screen flow and you would need a 99 level deep hierarchy to hit the 100 query limit.

There are probably other ways to solve this, but the key for me was to realize I must first navigate to the top of the hierarchy (via the get in the “loop”) and then work my way down, level by level, adding all the child account IDs to a collection variable. A final query gets me the opportunities where accountId IN collection variable

So here is the approach

1) get to the top of the hierarchy

use a get records and a decision element to loop until you get to the ultimate parent account.

2) work your way down

use a get records and a decision element to crawl your way down the hierarchy. a transform element adds the children to a text collection of account IDs that is used in the final opportunity query. note: you must also add the ultimate parent account ID to the collection of all account Ids.

3) get opportunities where accountID IN collection

now that you have a collection of all the account IDs in the hierarchy, you can use them to get all the opportunities (or the open opportunities, or whatever makes sense for your use case)

4) display the results using a data table

boom!

This makes account hierarchies that much more useful - you can now easily see all the opportunities (or cases, or whatever makes sense in your use case).