Suppose that you recently implemented a new process for storing Failure IDs in Failure records. Previously, Failure IDs were formatted as FAIL-n, where n indicated the number of the failure. For example, the tenth failure that was recorded contained a Failure ID of FAIL-10. You have decided that you want all failures to contain four digits and that zeroes should be used as placeholders if the failure is not the 1000th failure or later. For example, the tenth failure should be recorded as FAIL-0010 instead of FAIL-10.
You know that a Failure ID in the new format will contain nine characters, where the dash (-) is considered a character. You want to see which Failure records contain IDs with fewer than nine characters. In this case, you might configure a query on the Failure family, and add the Failure ID field and the ENTY_KEY system field to the query twice each.
In this example, to display each Failure record with a Failure ID that contains fewer than nine characters, you would configure an expression using the LEN function. You would also configure the alias to indicate that the column displays the number of characters in the Failure ID.
In this case, the expression syntax is:
LEN([Failure].[Failure ID])
The query also includes criteria on this column that indicates that the results should display only Failure records where the Failure ID contains fewer than nine characters.
A hyperlink has been added to the Failure ID field so that you can open from the results each Failure record in the Record Manager and update the Failure ID. Because the query is running in unformatted mode and the hyperlink includes the EntityKey parameter and the FamilyKey parameter, the Entity Key field and the Family Key field are also included in the query.
Note: You can use the LEN syntax on a SQL Server database only. To use this function on an Oracle schema, use LENGTH instead of LEN.
