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 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.
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.
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.
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
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
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 firstname.lastname@example.org 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.