SQL Server tables can have primary key columns of data type GUID/uniqueidentifier. A GUID (globally unique identifier) is a value that's extremely likely to be unique across multiple systems and an example value looks like this "2e786c51-1668-46e6-a325-14918aad4276". GUID primary key values are commonly used to uniquely identify rows in replicated databases.
The problem is that forms in Power Apps don't work against tables with GUID primary key columns without some modification. This post walks through how to build a form that can add and edit records against a table with a GUID primary key column.
Why don't forms natively work against SQL tables with GUID primary keys?To demonstrate the problem, let's take the example table. Notice how the data type of the primary key column (product ID) is of data type "uniqueidentifier".
Let's now create an auto-generated app based on this structure. If we attempt to create a new record, Power Apps fails with the following error: "Field ProductID is required". The problem is that Power Apps doesn't generate the GUID value that's needed for the new record.
In the designer, the editor doesn't allow us to change the control type of the ProductID field to anything other than "View text". A card that's based on the "View text" control type does not include an update property. Therefore, this prevents us from being able to use the update property to specify a new GUID value when we create a new record.
How to setup a form to work against a SQL table with a GUID primary keys column
The workaround for this problem is to take a data card for an existing field and to modify the properties of the card so that it updates the GUID column. We can subsequently re-add the field to the form that we modified afterwards.
Here are the steps that we would follow. First, let's take the existing card for the "ProductName" field. We then unlock the card and set the following properties:
- Set the DataField property to "ProductID"
- Set the Default property to ThisItem.ProductID
- Set the Update property the following:
If(EditForm1.Mode = FormMode.New,
Finally, we can hide the "Product Name"card that we've modified by setting the visible property to false, and we can re-add the "product name" field.
At this stage, we can run the app, and successfully create and edit records without any error.
To update SQL Server tables with unique identifier/GUID primary key columns form, it's necessary to customise the form by adapting an existing field to work against the GUID column. This post described the process.