Blog
Dataverse - How to retrieve FetchXML or SQL for Dataverse views
July 13. 2021
With, Dataverse It's possible to define views. Dataverse stores view definitions in a system table called 'Views'. We can interrogate this table to retrieve the SQL or the FetchXML for a table and this post examines this setup in more detail.
With Dataverse, we can define views which enable us to carry out basic joins, and to filter and to sort data server-side.
As an example, here's the definition of a view that's based on a custom table called 'Property'. This view retrieves the address, city, and property type conlumns from this table, and inlcudes a filter to return only records where the property type matches 'house'. The name of this view is 'houses'.
Dataverse stores the definition of this view in a standard table called Views. If we open this table in the Dataverse designer, we notice two interesting columns - FetchXML and 'Offine SQL Query'.
By default, it isn't possible to view the contents of this table in the designer - the 'Data' tab is missing from the designer
However, we can access this table through other means, such as through the TDS endpoint, or by building an app that's based on this table.
The screenshot beneath illustrates an auto-generated app that's based on the 'Views' tables, and highlights how we can display the FetchXML and SQL definition of the 'houses' view.
Conclusion
Dataverse stores view definitions in a table called Views. We can access the contents of this table and use it to discover the FetchXML and SQL definitions of the Dataverse views.
- Categories:
- dataverse
Related posts
- Dataverse - How to work around the error "multiple levels of many-to-one relationship expansion aren't supported"
- Dataverse - The easiest way to modify Dataverse data outside of Dataverse and Power Apps
- Dataverse - How to use a checkbox control a set a yes/no field in Dataverse
- Dataverse - How to create Entity Relationship diagrams
- Dataverse - How you can more quickly bulk update data using the SQL language
- Dataverse - How to fix the bug in the 'Business Rules' editor that prevents numeric values from saving
- Dataverse - What are the benefits, and how to create formula columns
- Dataverse - How to sort the available choice items from a choice column
- Error - Unable to modify Dataverse tables with error message, Language id should not be null
- Data - Why is Dataverse so fast?
- Dataverse - How to create and use Dataverse views in Canvas Apps
- Dataverse - How to filter Dataverse choice columns
- Dataverse - How to switch to the classic designer when the option is not available
- Licensing - What are Dataverse Restricted tables?
- Media - How to save and work with microphone recordings
- Dataverse - how to access tables from another environment
- Dataverse - How to Patch the 5 most complex data types
