Searching across Multiple Tables in Canvas Apps

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.

Setup:

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 the desired data from opportunity. After that, we will pull in all opportunity lines from the opportunity and append the product name (from the opportunity line) to the search column variable. Once complete, we can finish up by updating the search column on the opportunity.

Next, we can add our parent flows. One should trigger when columns we are searching on are updated on the opportunity. The other will trigger when an opportunity line is added or modified. Both of them will run the child flow and pass along the opportunity id.

To finish up we just need to modify the search box in the canvas app with the following:

SortByColumns(

    Search(

        Filter(

            Opportunity,

            ‘Status Reason’ = ‘Status (Opportunities)’.Active

        ),

        TextInput1.Text,

        “btg_searchcolumn”

    ), 

    “createdon”,

    SortOrder.Ascending

)

Reach out to Beringer today!

We love to implement Microsoft Dynamics 365 and Power Platform solutions here at Beringer.  We’ve been working with Microsoft Dynamics since its inception, and we’re always finding innovative ways to implement the latest tools and help automate business processes.

Beringer Technology Group, a leading Microsoft Gold Certified Partner specializing in Microsoft Dynamics 365 and CRM for Distribution also provides expert Managed IT Services, Backup and Disaster Recovery, Cloud Based Computing, Email Security Implementation and Training, Unified Communication Solutions, and Cybersecurity Risk Assessment.