At Compass365, we’ve been using Microsoft Dataverse for some time now and have put together a list of tips and gotchas to help highlight aspects of Dataverse that may not be apparent to the newcomer. These are items that we believe will be helpful to know early on in the process of creating a solution utilizing Dataverse as the data storage component. While we learned them eventually through trial, error, and frequent use, we hope the following tips and gotchas save you some of the effort!
Table and Column Creation
Table Name
Although it is possible to change the display name of a table, we would recommend trying to keep the display name and the underlying name the same where possible. Making the display name different than the underlying name can cause unnecessary confusion within both Power Apps when referencing tables and Power Automate where the display name is used when selecting a table in a Dataverse action, but the underlying name is used when writing a query filter.
Table Ownership
Create tables with ownership of “User or Team”. This setting cannot be changed after the table is created so it is important to consider it upfront. The “User or Team” setting will allow for the most flexibility as it is needed for row-level security implementation. If the “Organization” setting is used, then the table will not be able to implement more discrete security configurations.
Standard vs Custom
Where possible, utilize standard tables that are created to support core business functionalities. For example, Account or Contact tables can be used instead of creating custom tables and it reduces the dependencies your solution might have on customizations.
When creating a new custom table, there are a couple of considerations we think are important:
1. The primary name of the table should be the singular form. In the table creation menu, a separate setting is available to designate the plural form of the table. Keep track of the plural format as it will be needed to reference the table value as a Lookup in Power Automate
2. Every custom table created comes with a set of default columns. The Name column is one of these columns and is similar to the Title field that is automatically created in SharePoint lists. This is a text field and is used as the display value of the record if the table is used as a Lookup. When creating a table, the name of this field can be updated. It is defaulted to <prefix>_name (like craa7_name) but can be changed at table creation time to more closely match its value.
3. Calculated fields and Rollup fields can be useful to reduce logic elsewhere in the solution. For example, a Rollup field can be used to create Totals field based on child lookup values. Keep in mind though, a Rollup field is not calculated instantaneously, there is a schedule that can be set to determine how often the Rollup field should be calculated.
Choice vs Lookup
Choice fields have the ability to support thousands of options, but it is not recommended to actually use that high number of values. In general, users have trouble selecting from a drop-down field that has too many options. It is recommended to use Choice fields for categories of data rather than use them to represent separate items of data. If it is separate items of data, then a table used as a Lookup would be a better fit.
An additional consideration is that updates to a Choice field will require a release to deploy into QA/Prod environments while Lookup fields do not need a release.
Naming Consistency
Naming standards are important to create consistency in the data structure. One standard that we found to be useful when adding a lookup column, suffix the underlying name with “id”. For example, if creating a lookup column to the Contacts table, the default underlying name will be something like craa7_contact. During column creation, this can be updated to craa7_contactid. This is useful to clarify what data is used to compare to when the underlying name needs to be used for query filters like in Power Automate.
Reference:
- https://docs.microsoft.com/en-us/powerapps/maker/data-platform/create-edit-metadata
- https://docs.microsoft.com/en-us/microsoft-365/community/cds-and-model-driven-apps-standards-and-naming-conventions
Power Apps and Dataverse
In Power Apps, a Dataverse Choice field has a special format for reference. If you try to compare a Choice field with a text value, you will receive an error (OptionSetValue expected) because it is expecting a Choice reference. In order to reference the proper Choice value, type the name of the Choice field and Intellisense should bring up the available choices. In the example below, ‘Batch Status Choice’ is the name of the choice field and ‘Payment Batch Not Started’ is one of the options.
This is how the error would look if a text value was used instead:
Power Automate and Dataverse
There are two special types of fields that require different methods of reference when using Dataverse actions. They are Choice and Lookup fields.
Choice Fields
When creating a choice field, each choice is automatically assigned a number value. When trying to filter based on a choice column or when trying to set a variable to a choice field, the number value of the choice field must be used. The number values can be seen by going to the Choice field definition and expanding the View More option.
Lookup Fields
When setting a lookup field on a table, the following format with the plural form of the table should be used. For example, if setting a Lookup value to the Contact table, the format would be craa7_contacts(<contact_guid>). In this example below, the Compose action was used to get the GUID of the record we want to reference and then the Output is used to construct the Lookup value.
Tables and Solution Migration
Data Flows
When Dataverse tables are included in a solution and exported to a different environment, the solution package will create the tables, but it will not bring the data that was in that table. This means for tables that need to be pre-populated, one option is to use the Data Flow feature which can connect tables between two environments. When implementing a Data Flow, make sure to test follow-up runs to migrate updated values. By default, the Data Flow will not check for existing data to update, instead, it will migrate the data each time it is run, so you may end up with duplicate data if this is not configured correctly. The other consideration for follow-up test runs is to make sure any lookup relationships are not impacted by the data updates.
The following setting should help to make sure data is not duplicated (delete rows that no longer exist in the query output).
Clone and Patch Solution
If you are in the middle of development but need to deploy a Production fix that is part of the solution, review the Clone and Patch Solution features. These features were intended to allow for partial deployments. The issue that may come up if not using Clone/Patch features is that if the solution is deployed with components that are still in development, future updates may contain conflicts. For example, if you change a table field type (by recreating the field) and it has the same underlying name, this will lead to an error on import where it expects the field to be the original type. In this scenario, making sure the underlying name is different will allow the import to move forward. However, a cleaner approach would be to leverage the Clone/Patch features designed for this scenario and not to deploy components until they are fully ready for release
Deleting Tables
Once a table is created in Dataverse, it will quickly gain custom dependencies that make it difficult to delete the table. Here is a quick checklist to go through to make sure all custom dependencies are removed so that the table could be deleted. Note that if you click on the “Show Dependencies” option, it will show many default dependencies that do not impact the deletion of the table. Within this list, you should look for only references to custom fields and components.
Here is the list of items to look for:
- Go through all views and remove references to custom columns in each view. If it is a custom view that is no longer needed, then the view could be deleted instead.
- Go through all forms and remove references to custom columns in each form. If it is a custom form that is no longer needed, then the form could be deleted.
- References to the custom table fields from inside a Power Automate workflow will also lead to a dependency issue. Clear out the reference from the Power Automate workflow and then go back and try to delete it.
- For other potential dependencies, note that you can also click on “Show Dependencies” for a field that will focus on that field instead of on the table which will give you different dependency results.
New to Dataverse and need some assistance?
Reach out at any time to discuss Compass365’s Power Platform Consulting Services, we are happy to help. Contact Cathy Ashbaugh at cashbaugh@compass365.com to arrange for a complimentary consultation.
Compass365, a Microsoft Gold Partner, delivers SharePoint, Microsoft Teams, and Power Platform solutions that help IT and Business leaders improve the way their organizations operate and how their employees work.
