Blog

Functions - Here's a use of the @ operator you may have been unaware of

Did you know that you can use the @ operator to validate the schema of a record? If not, this post describes this usage in more detail.

Most app builders are aware of how to use the @ symbol to disambiguate field names. However, we can also use operator to validate the schema of a record. This is a tip that I picked up from Carlos Figueira, and this post describes this usage in more detail.

The primary use of the @ operator

The primary use of the @ operator is to disambiguate field names, particularly when we call combinations of functions such as nested calls to LookUp inside a call to AddColumns.

As the example beneath demonstrates (taken from my book), let's take an example of a formula that references two tables - a table of issues, and a table of tenants. Both of these tables include a field called TenantID. The @ operator enables us to correctly identify the target fields within the conditional part of the LookUp function. 

AddColumns('[dbo].[Issue]',
           "TenantRecord",
           LookUp('[dbo].[Tenant]', 
                  TenantID = '[dbo].[Issue]'[@TenantID]
           )
)

How to use the @ operator to validate a record schema

To demonstrate how to use the @ operator to validate a record schema, let's take an example of where we want to build a local collection of records that we'll patch to a data source at a later point in time.

The collect formula to build this local collection would look like this.

Collect(colNewProperties,
      {Address1:"35 Kings Road", City:"London", Bedrooms:1},
      {Address1:"83 South St", City:"Bristol", Bedrooms:3},
      {Address1:"56 Madioson Ave", City:"Oxford", Bedrooms:2}
)

If the intention is to patch this local collection to a SQL Server table called 'Property', a more robust way to build this collection is to use the following syntax. This syntax verifies that the field names and data types of the record correctly match those in the target data source.

Collect(colNewProperties,
      Property@{Address1:"35 Kings Road", 
                        City:"London", Bedrooms:1},
      Property@{Address1:"83 South St", 
                        City:"Bristol", Bedrooms:3},
      Property@{Address1:"56 Madioson Ave", 
                        City:"Oxford", Bedrooms:2}
)


The benefit of this is that it enables us to detect potential errors that could relate to mis-matches in data types earlier at design time, rather than at runtime.

What happens when there is a mismatch of schema?

When there's a mis-match of schema, the designer will display a warning. For example, if we were to pass a "Bedrooms" value of "Four" (ie, as a string rather than the expected numeric value), the designer will show the error that's highlighted beneath.

Collect(colNewProperties,
      Property@{Address1:"35 Kings Road", 
                        City:"London", Bedrooms:"Four"},
)


Are there any limitations of this method?

In general, this method works well. However, it can struggle with more complex data types. For instance, it doesn't currently seem possible to pass values that are of data type currency.

Conclusion

The primary use of the @ operator is to disambiguate table/column objects. However, it's also possible to use the @ operator to validate record schemas, and we can use this feature to write more robust formula.

The online documentation beneth provides more details about the @ operator, but at the time of writing (May 2021), it does not cover this specific usage.
Previous
Next
Related posts