In trying to follow the best practices for creating sequential record IDs, I ran into an issue wherein I was getting different results depending on whether the intermediate variable containing the “starting value” was blank, in either the blank=null or blank=empty string fashion.
For example, in populating the starting value by popping a value from a specific array index generated from the previous record ID, if the record ID was missing the numerical suffix, it would generate an empty string, but if it was missing a delimiter (used to split the last record ID into the array values), it would result in that intermediate variable being null.
I created some diagnostic triggers to display messages indicating which scenario caused each issue, leveraging ISBLANK(), RegEx matching ^$, and LINK(), and =“” expression methods.
The ISBLANK() function detects both null and empty string problems, and clearing the intermediate variable whenever ISBLANK() was true made causes it to read as null, thereby allowing subsequent triggers to use the LINK() function to replace the null with “0” to allow consistency.
I realize this is limited in application but after all the diagnostic effort to learn why differing results were attained, the solution was simple and satisfying, and I will likely use it in any new applications that require generation of sequential record IDs to avoid having to manually create the first record in a given table.
2 Likes
Hi @jmlowden,
thanks for sharing.
I find this useful, to exactly understand whats happening for app builders with no coding background.
Could you tell more about how you actually use this?
For only visual debugging you could also do (not better, but additional methods I used in the past):
-
Add a QR-Code with this variable. “No value selected” means “null”, an actual QR code means it is at least an empty string
-
Simply show a Message with this Variable.
If the message is null, its “NULL” if ist an empty message, then its an empty string.
Otherwise it would show the string itself, e.g. Hello World
However, for every use case there is a way. There are actually a lot more (more or less useful) ways.
I highly recommend to initialize text variables with an empty string. Also don’t use clear on them, but an empty string, as long as you don’t need to set it to null…
This specific use case was using a table aggregation to find the last (most recently created) record ID value, which is supposed to have the format “CHI-TLD-xxxxx” where the x are numerical. I have similar use cases wherein table record IDs will need to have incremental numerical suffixes to facilitate transcription and operator readability (such as on printed labels for device putaway/picking). Basically the process used is to split the result from the table aggregation into an array based on the delimiter “-”, then take the value at index 2 (the numerical suffix), convert it to an integer, add one, and convert it back to text, adding leading zeros as required to keep the format consistent.
If the table were to be blank and the first record would be created, this would cause that referenced array element to be null, and the LINK() function would do what is required. However, if for some reason there is an error in the last record ID wherein either the numerical is missing but the second delimiter is present, that referenced array element ends up being an empty string, and attempting to convert an empty string to an integer results in an error.
Its probably a rare case this would crop up, but it was interesting to figure out how to flag which type of error it was and how to correct for both error types efficiently. Something more involved would be needed to get the incremental numbers back on track “automatically” if there was an error - probably the easier thing would be to create a new record directly in the table with the correct ID to get the ball rolling again.
