Introduction to Microsft Datavers table key column
This guide is a step-by-step on how you can add a primary key column to your Microsoft Dataverse table. Although there are several ways of creating primary keys, the aim of this guide is to give you suggestions on how you can do it more efficiently.
In spite of creating a table being an important preliminary topic, on this blog, you can learn an introduction topic.
The first thing to remember even though you will work a simple model that has almost two relationship tables you will need a key column and one kind of relationship table. Moreover, if you have a complex table with many columns maybe simplifying your model can do more performance and clarify your data.
Another Key point, is important learning about modeling data, if you can know more about it and how to creta table seing below the link for more topics.
To be sure that you will be focus principally on key columns get more details in the de link below.
Principal Name Column
When you create a table one column is automatically created. You can use this column and personalize your type and more. In view of that primary column doesn’t have value and I personally use it to create a Key column or ID column for this table.
- Firstly, create a table if don’t have one;
- Secondly, prepare a key column.
- To put it another way, if you need to construct a table or if you desire to use an existent column (optional). It is not a rule to the primary column, you will see that is possible to create a key column using existent columns.
Create a key column
It is common to need a way to uniquely identify a record in a table. By default, Microsoft Dataverse tables have a GUID as their only unique column. This GUID called the Primary Key and consists of a long string of numbers and letters that are not useful to an average user in terms of meaning or importance.
Therefore, you can define an alternate key to enhance user searches. When you define a column as a key, Dataverse ensures that each entry in that key column requires and unique, so you can use the key column to uniquely identify a specific record. It also improves searching and filtering on a specific column or make a relationship modeling table, as alternate key fields are always indexed.
One interesting choice to do is personalise you key mixing text with numbers or data withi numbers with this key is part of specific projet. For exemple, if you have a medical application for identify a specific key for one table you can put a prefix doc-0001 for document, ph-0001 for phamarcy products, pat-0001 for patients. it can create a simple way to work with key column inside a complex modeling.
Note: For each dataverse table you can create 10 key colums.
As an illustration, will be three principal formats: text with numbers, date with numbers and combine colums.
Step 01 to create key using an existent column.
Open you table ande select the column to personalize thus select edit column.
When you click on the narrow down the window in the 2023 power apps design.
At this version select the first option: Edit column.
Only to explanning in this window you will contact about column proprieties like ordering A to Z, filter ‘this columns’ By and other refer to proprieties columns.
But, all of a sudden, you will look insert column inside this menu. Its note necessairy a column propretie, even thought can help easily to create a new column. Confuse, almost for me.
That’s ok, next!
Step 02 preparing the column to be alphanumeric string key column
In the next windows open is important mind a principal key column concept: there will be the unique value to each record or most knell as row.
- Display name: you can change the name only before receve data.
- Data type: the most important propertie, you can customize the format of these columns and then rely on the system to generate corresponding values that fill them automatically at runtime. This column is of text type, the classic solution designer does not support the creation or management of AutoNumber columns.
- Required: for key column you tsave to select as Business required, never as a optional.
- Select the box with with you want tracking this column for Azure logs.
- AutoNumber in the Data Type dropdown, select string prefixed number to alble the nest field.
- Prefixed string number: the most common format for AutoNumber is a prefixed string number. For example, a prefixed string number with the Contoso prefix would generate records like Contoso-1000, Contoso-1001, Contoso-1002, and so on. Prefixed date number: another common format for AutoNumber is a prefixed date number. When this type is selected, the AutoNumber consists of an automatically incremented number with a formatted date prefix.
- Minimum number of digits: select this to simplify or standading format. In this exemple you wil the number with four digits starting after one thausand.
- See value: you can define the format number.
- You can see a preview result of you key
Step 03 preparing the column to be date strings key column
The date portion of the record reflects the current date and time when the record was created in UTC time. For example, a prefixed date number would generate records like 2019-26-02-1000, 2019-27-02-1000, 2019-28-02-1000, and so on, based on the current date and selected date format.
In this step repeat the steps above till Autonumber type and select ‘Date prefixed number’
The field Seed value receives your specification in this exemple is used 1000 base.
As has beeen noted on Preview the format is demonstred. Additionally this information the date will progress at lonf de time, that meaning when you will create a record.
In the Advanced option you can manipulate the maximun character count. Another key point analyse the sie field to avoid errors to input the data.
The input method editor (IME) on this level of knowledge select the Auto option. If you want knoe more about this topic learning more in the Microsoft documentation.
Step 04 creating a key column microsoft dataverse
Access Power Apps and select the Dataverse, afeter you table. With you table open select Keys from the options below Schema.
Select + New key on the command bar.
At lenght,give a display name to the key, select one or more columns that will form the new key and select the Save button.
Given this points if you choose two columns as a key the results are one mix key each column. for exemple: The columsn First Name and Phone number it come be Michael5147142442. it is good if you want have a double check key.
In the next picture we can see the three key represented. Guid column tha is a system column key, and the two other construction key : string with numbers and date eith numbers.
Note: A key is compose by one or multiple columns. If you select multiple columns, the key will be called a composite key.
After a few minutes, you will see the new key added to the list of keys for that table. you con create 10 keys for each table.
Conclusion
In this tip, we have demonstrated how to create a key column using Microsoft Dataverse. However, it is in your best interest to remember that each alternate key you define must be unique and must fulfill all the requirements discussed above. In addition, we don’t recommend that you have multiple alternate keys in one table as this represents redundancy, and a duplicate search can occur. It is also worth noting that an Id field also serves as the default unique identifier for a table. This means that it cannot be deleted or changed once it has been created. If you decide to use an alternate key, then engage in a data analysis phase to ensure you are not using fields with too much overlap. Ultimately, your decision depends on what type of information you want to extract from your table when you perform searches and filter results. You also need to consider if your users will benefit from the alternate key more than they would a standard primary key.
Related tags:
Artifical inteligence Power Apps Power Automate Power BI Power Platform Connector UX design
