Blog

Forms - How to add and edit records against SQL Server tables GUID primary keys

SQL Server tables can be set up with primary keys that are of data type GUID/uniqueidentifier. Power App forms do not work against these tables without some slight modification. This post describes how to add and edit records against tables with a GUID primary key column, using a form.

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, 
   GUID(), 
   ThisItem.ProductID
)



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.

Conclusion

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.
  •   Categories: 
  • sql
Previous
Next
Related posts