Product Note
Understanding Logical Properties
Welcome to the first in an as-needed series of emails helping you to better understand just what’s possible with Mobility.
In this first issue, I want to expose the seemingly not well understood capability of Mobility to create logical properties or virtual fields.
I chose this topic because seemingly the feature is not well known; consequently, it took our teams’ longer than was necessary to resolve a customer support ticket.
Logical Properties
Logical properties allow us to execute arbitrary SQL statements in our SELECT queries so that we can create custom values or express physical values in custom formats which we can then display in our Mobility screens.
Case Study
AeroControlx - a Trimin customer – is required to periodically file reports with the FAA. The FAA insists on having dates in an unconventional format.
02 Feb 2024
The customer raised a ticket of how to format dates in this way for use on one PDF report.
Typically, Mobility allows the application developer to format dates using either short or long formats … but not in an arbitrary format.
And using long/short standard date formats doesn’t work here because it’s a global setting and that’s not what we want.
The solution is to use logical properties. Here we can create a custom format using a SQL expression for the date field that meets the customer’s needs.
TO_CHAR (date, ‘DD MON YYYY’)
The date column stores date/time as a SQL date object. We read this value and pass it to the TO_CHAR() function and specify that we want the date object expressed as a string with format DD MON YYYY … just as our customer wants!
If our date field was stored as a millisecond integer timestamp, we could deploy a variant of this strategy:
TO_CHAR (TO_TIMESTAMP(int_date / 1000), ‘DD MON YYYY’)
Logical properties, because they fall back on native SQL expressions, are very powerful ways to get custom values or formats into your Mobility application.
Because they are SQL expressions, they support any SQL function so you can use them to:
- split strings
- concatenate strings
- use conditionals
- apply mathematical operations
- use static values or constants
- … and much more!
And because they are executed on the SQL server, they are generally very performant … but as always, be careful when using lookups on non-indexed columns!
A full reference of PostgreSQL query functions is here.
Logical properties are cool! Use them liberally to enrich your applications!