Tips and Tricks - Applies to Infiniti v8.0 or later
If you have an alpha numeric field and you wish to apply a numeric formula to the values within. You will need to break up the field into segments, apply the numeric formula and then concatenate the alpha numeric field back together.
Take the below issue as an example:
If you had an alpha numeric value like: ‘L#####/##/##’, e.g. ‘L12345/02/01’ being pulled from a data source and you wanted to -1 from the highlighted value. You will first need to split the value into three segments, apply the -1 formula and concatenate the formulas back together.
There is no current function that will cast a string to a numeric value, but if you break up the concatenation into a two step process, you will be able to achieve what you’re trying to do.
Your formula function will look like this:
!- - This is the step that casts the substring into a numeric value.
!- - Intelledox now treats the above as a numeric value. We can now -1 from the Alpha Numeric Value and concatenate it back together.
=Concat(Left([q1.ALPHA_NUMERIC_VALUE], 7),concat("0",[REFERENCE THE ABOVE FORMULA HERE]-1), Right([q1.ALPHA_NUMERIC_VALUE], 3))
See Article: "Split and manipulate fields returned from data sources" for further information on Left(), Right and SubString().
The outcome is shown below: