Fun with GUIDs!
If you are asking “GUI-what?”, not to worry, you aren’t alone. A GUID is a Globally Unique Identifier that helps manage the relational data in the Common Data Service for Apps and SQL Server. PowerApps is committed to providing an environment where working with GUIDs is not required. If you have never seen one before you can safely skip this blog post.
For others, you may be accustomed to working with primary and foreign keys perhaps even in Canvas apps today. Or perhaps you have lots of experience with Microsoft platforms and you’ve been wondering “hey, this is supposed to be a Microsoft product, where are all the GUIDs?” If either of these cases is true, this blog post is for you. Canvas apps are now ready to claim their birthright. We have added GUIDs as a first class data type.
Motivation
Honestly, we didn’t want to add GUIDs as they are far from user friendly. Our hand was forced when we integrated with CDS which exclusively uses GUIDs for database keys. Until now we’ve been using text strings to hold a GUID which works in most cases but has issues. Most notably, some of you may have experienced this error:
A binary operator with incompatible types was detected. Found operand types ‘Edm.String’ and ‘Edm.Guid’ for operator kind ‘Equal’.
CDS can be strict about only comparing strings to strings and GUIDs to GUIDs. We tried relaxing the rules and using heuristics to help but we just couldn’t always get it right – I saw one of these in a customer app only last week. It became clear to us that we needed to add a proper GUID type.
There were other reasons too. Working with GUIDs as a hexadecimal string is error prone. Case sensitive compares might fail if the upper/lower case is different on one of the alpha hex digits. We can do better error detection if we know the string should be a GUID. For example without it, if a single digit is missing, a Filter formula may fail to return any results rather than give an error for a improper GUID. This work also benefits SQL Server which has a GUID data type. You also asked for it in the community.
GUIDs in CDS
All entities within CDS have a GUID primary key field to uniquely identify each record. This field usually has the same display name as the entity and the logical name has an Id tacked on the end. The data type is Unique Identifier. The field is always required and the value is generated by CDS when the record is created. For example, for the Account entity (you may need to change the field filter to All at the top of he screen):
If you look at this with the Data tab in the portal, again adjusting the field filter and scrolling, you’ll see the signature hexadecimal string of a GUID:
Today, Canvas apps see this field as a string that can hold anything, indistinguishable from a string that holds “Hello, World” and that’s the problem. Canvas apps are strongly typed – we know the type of everything – and that knowledge allows us to make good suggestions when authoring a formula and flag errors before they happen. If everything is reduced to the lowest common denominator (text string) then inferences based on type are no longer possible.
In the below screen shots, we’re displaying the Account field in a Gallery control. We’re also doing a comparison to “Hello, World” which always returns false. In a strongly typed world this comparison should be an error – you should only be able to compare GUIDs to GUIDs.
Experimental feature: Treat GUIDs as GUIDs
In the File menu, App settings, Advanced settings, toward the bottom of the list you will find this experimental switch:
It is currently off by default. We recently started the process to move this to Preview status and turn it on by default. Let’s turn it on now, refresh our data source, and see what it does to our app:
Notice that we are still displaying the GUID value just fine, as we can coerce a GUID to a string. There is a known issue with string coercion right now, for anything more complex than showing a value in a label control use the Text function to manually coerce to a string for the next couple of weeks.
Notice that the comparison to “Hello, World” is now throwing an error about a type mismatch between Guid and Text. Yay, we got an error!
Ok, that’s fine, we’ve caught the potential “Hello, World” bug. But what if you want to compare Account to a literal well-known GUID value? To do this, you can convert a properly formatted string containing a GUID to a value of data type GUID with the GUID function:
Now we can do the comparison without an error as we are comparing apples to apples, or GUIDs to GUIDs in this case. The Account GUID for the second Account matches our string that has been converted to a GUID value, and thus it shows true while the rest show false.
We believe this is the only case that you will need to change in your formulas. If you have a literal GUID in your formulas today and are doing direct comparisons to a GUID value coming from CDS or SQL Server, then you need to wrap it with the GUID function when this experimental feature switch is turned on.
GUIDs in SQL Server
Let’s now turn our attention to SQL Server. Here’s part of the schema definition for the Customers table from Adventure Works, modified to use a GUID for the primary key:
If we run a Select query on this table in SSMS:
And let’s see how this looks in a Canvas app without the “treat GUIDs as GUIDs” experimental switch turned on:
Do you notice anything a little different about the GUIDs in this example versus the CDS example? Look carefully… SQL uses uppercase letters in their GUIDs. Wow, yes that is subtle. But that could make a big difference when doing comparisons as the “=” operator is case sensitive for strings while GUIDs are not (after all, you are only looking at the hexadecimal representation of a 128-bit binary number). That could lead to a really difficult bug to track down.
If we turn on the GUID experimental feature, then the GUIDs are normalized and coerce to a string with lower case letters:
Besides the string comparison discussed above for CDS, this is the only other difference we are aware of when using SQL Server. It is very unlikely that you are dependent on GUIDs being displayed with upper case letters. In fact except in debugging situations you probably never show a GUID to an end user. But if this is important, you can always use the Upper function when displaying them to get the old behavior:
Due to a short term limitation of our GUID to string coercion, the Text function is needed to manually convert the GUID to a string before we can use the Upper function. This limitation will be removed shortly, a month from when this article is published you will no longer need to use the Text function.
GUIDs from thin air
Finally, you may have a situation in which you’d like to create your own GUID, effectively a large random number that is very, very unlikely to be duplicated. Something that is globally unique.
Not only can the GUID function convert a string to a GUID, it can also create a new GUID if you don’t pass in any arguments. Here’s a Gallery control with its Items property set to the formula ForAll( [1,2,3,4,5], GUID() ):
When used in this manner, the GUID function is a Volatile function: its value changes each time it is evaluated. For more details see the Volatile function section in the GUID function documentation.
A small change may be needed
As a result of all this, if you use GUIDs with CDS or SQL Server you may need to make a small change to your formulas.
As discussed above, direct comparisons to inline GUIDs in a string will no longer be supported. Although that isn’t very common, you may have some apps that do this today.
The change required is only if you compare GUID fields to an inline string, similar to this:
Then you will need to wrap the string with the GUID function:
Even if you have this in your formulas today, you don’t need to make this change right away, it will be months until it is required. With the guidance in this blog post you can get ahead of the curve and make the change now if you turn on the experimental switch described above. The places to fix are easy to identify as they will show up as errors in the Studio.
Your valuable feedback
This feature is still experimental and while it is we’d love to hear your feedback. And as always, we very much appreciate all your feedback on the community forums.
Yes, a small changes to how we work with GUID string literals is coming, but not immediately. When it arrives the formula changes are easy to identify, make, and can even be made now. Please let us know if you run into anything unexpected as we’d like to make this transition as painless as possible.
