Canvas apps are a great tool that empowers citizen developers and professional developers alike to quickly build custom applications that meet specific business needs. However, like most things, it isn't perfect. One limitation is the built-in search functionality. Which limits users to only being able to search for columns on the base table the view is for. Meaning, if you want to search for opportunities by their products or contacts by company type, those records won't appear. So how do we solve this problem? Well, like many hurdles we come across in the Microsoft ecosystem, a solution lies within Power Automate.
What we need:
The first thing we will need is a multi-line text field for the table we are going to search on. This column doesn’t need to be on any form or view and the character limit will need to be large enough that we don’t run into issues down the road. Then, we just need a couple of flows that can take care of the rest.
We are going to use this column for searching on both related records and the base record. Since we can't trigger flows from multiple tables, we'll have the flow responsible for populating the search column be a child flow.
Now using opportunities and opportunity lines as the example. We'll start with the child flow by using a manual trigger with a text input to store the opportunity id. Then, we'll create a search column string variable and populate it with (