Using OR Condition Operator in Multiple Tables with FetchXML

In this fascinating blog post by Temmy Wahyu Raharjo, the author delves into the process of utilizing the 'Or' conditional operator in multiple tables with FetchXML integrated with Microsoft Dataverse. This process is particularly useful on the Contact table, featuring a lookup to Parent Contact/Account, allowing data preparation for contacts with both Parent Account and Parent Contact. The author uses this as the real-world scenario for his tutorial.

The author demonstrates using a form with a Filter attribute to enable contact filtration. The author also crafts a new View labeled "Demo Filter" in Contact, where they extract the Full Name from the parentcustomerid to the Contact and Name from the parentcustomerid to the Account. Following this, the author makes an attempt to build the Fetch XML using Advance Find. However, it becomes apparent that applying the “Or” condition across entities or tables is not directly achievable.

Fortunately, with the help of XRMToolBox, specifically, SQL4CDS by Mark Carrington, the correct Fetch XML can be generated. After query preparation, the correct function of the "convert" button reveals the FetchXML in the text below.

The author then brings our focus to the Demo form, demonstrates setting the Default view for the Contact, and disables the MRU. They also prevent users from altering the view to avoid invalid data selection. For demonstration purposes, they establish a JavaScript function designed to activate during Form On Load.

During testing the customization they applied, the author experienced an error. The author investigates the error by accessing the Network via the F12 Developer console, and copies the Request URL from Headers. They then decipher the string via notepad to notice the entity alias, allowing them to fix the JavaScript code.

After a chain of tweaks and trials, the successful run of the code fetches the satisfying result. The blog post concludes, with the author expressing satisfaction with their success and looking forward to more CRM adventures. The author is notable for their role as a Microsoft Dynamics 365 Technical Consultant, KL Power Platform User Community Leader, an enthusiast for Student Forever, Test Driven Development, and Human Code.

For further reading on this topic, you can access more information from here.

Understanding Microsoft Dataverse and FetchXML

Microsoft Databank is a solution to manage and store data for business applications. These unique tables, columns, and rows integrate smoothly with FetchXML, a proprietary, XML-based language. This language fetches various types of data, including attributes, relationships, and conditions from the Dataverse database. With this technology, businesses can build easy-to-navigate user interfaces, and customize forms with added filter attributes optimizing data handling. Highly accessible, FetchXML seamlessly integrates with other applications, making it a great asset in CRM processes and enhancing user-friendly experiences. As demonstrated by Raharjo, although encountering some challenges, one can achieve successful and efficient data handling by using FetchXML with Microsoft's data management tools.

Read the full article Dataverse: How to use the condition operator or in multiple tables in FetchXML

Learn about Dataverse: How to use the condition operator or in multiple tables in FetchXML

In today's blog, we will guide you through navigating Dataverse using FetchXML's conditional operator or in multiple tables. Dataverse, now known as Microsoft Dataverse, is a low-code data platform service that allows you to create custom business apps that connect your data across platforms, ensuring data compliance, security, and privacy. This guide is excellent for Microsoft Dynamics 365 Technical Consultants, code enthusiasts, and followers of KL Power Platform User Community.

To begin with, let's take a scenario where we have a Contact table with a lookup to Parent Contact/Account. Image visualization aids in preparing data where the Contact has Parent Account and Parent Contact. Now, we have prepared a Form, which will have a Filter attribute, to filter the Contact shown.

To further illustrate the scenario, a new View named “Demo Filter” is created in Contact. This View pulls the Full Name from the Parent Contact to the Contact and the Name from the Parent Contact to the Account. Advance Find is then utilized to build the Fetch XML. However, there is a limitation; you can't add the “OR” condition if Cross Entities (tables).

Now, you might be wondering how to overcome this limitation. Good news! The mature tools in XRMToolBox can assist with this. In particular, SQL4CDS by Mark Carrington is your go-to option here. This tool helps generate the correct Fetch XML once the query is prepared.

Attention needs to be given to the syntax highlighted because it can be utilized later on. For example, in the Demo form, the Default View for Contact is set and, as always, the MRU is disabled. The selection “Allow users to change view” is unselected to avoid users choosing invalid data.

Now, Javascript comes into play. For the sake of demonstration, a Javascript code is created which calls it on the Form's On Load. This is a significant step as it sets the foundation for further customization.

Upon testing, if an error is encountered, we have a solution for that too. Open F12 (Developer Console) > Go to Network > Find the request for that pertaining action and inspect the error. This process helps identify the entity alias that can aid in troubleshooting and rectifying the JavaScript code.

Can you guess what the final step is? Yes, checking the result. Enjoy the fruitful outcome of your technical labor and say "Happy CRM-ing!".

In conclusion, this guide simplifies the complexities of using the fetchXML “OR” condition in different tables within the same query. Remember, when one door closes, the XRMToolBox opens another; use its tools wisely and make the most out of it.

• Microsoft Dataverse
• FetchXML
• SQL4CDS by Mark Carrington

Indeed, the journey of learning never ends. Keep exploring, keep learning.

More links on about Dataverse: How to use the condition operator or in multiple tables in FetchXML

Dataverse: How to use the condition operator or in multiple ...

Apr 8, 2023 — Today we will learn how to use the conditional operator "Or" when applying addPresearch in Javascript (but you also can implement it from ...

Dataverse: How to use the condition operator or in multiple ...

Today we will learn how to use the conditional operator "Or" when applying addPresearch in Javascript (but you also can implement it from ...

Use FetchXML to query data (Microsoft Dataverse)

Sep 8, 2023 — Find out how to use FetchXML fiscal data conditional operators and "older than" clauses for date and time values. Use FetchXML aggregation ...

Keywords

Dataverse, FetchXML, Condition Operator, Multiple Tables, Use Condition Operator, Dataverse FetchXML, FetchXML Tables, Condition Operator FetchXML, Dataverse Multiple Tables, Dataverse Condition Operator