Substring Error in Report Designer

Hi ​@jbuddecke71,

Could you please try the formula below and let me know if it works for you?

=IIF(
    IsNull([ARTran.TranDesc],'')='',
    '',
    IIF(
        InStr([ARTran.TranDesc], '|') <= 0,
        [ARTran.TranDesc],
        Substring([ARTran.TranDesc], 0, InStr([ARTran.TranDesc], '|') - 1)
    )
)

This formula should return the entire description if the | character is not present. If the | character exists, it will return only the text before the first occurrence of |. It also handles null or empty values to prevent errors.

Please let me know the results or if you encounter any issues.

​@jbuddecke71 As Dipak pointed out your function needs to include a clause of what to do when the | character isn’t present. This is where IIF() functions come into play.

​@Dipak Nilkanth Thanks for the response but I got a similar error.

An error occurred while executing the Substring(Identifier(ARTran.TranDesc), Const(0), BinaryOp - (InStr(Identifier(ARTran.TranDesc), Const(|)), Const(1))) function. Length cannot be less than zero

Hi ​@jbuddecke71,
Use Below formula, I tested in local environment and its worked fine for me.

​​​​​​​=Substring(
    [ARTran.TranDesc],
    0,
    IIF(InStr([ARTran.TranDesc], '|') <= 0,
        Len([ARTran.TranDesc]),
        InStr([ARTran.TranDesc], '|') - 1)
)

to debug this, Add a temporary text box with:

=InStr([ARTran.TranDesc], '|')

and preview the report. If you see values of 0, then the issue is definitely that Substring() is still being evaluated with a negative length.

​@Dipak Nilkanth 

Using your code threw an error.

The debugging code returned the following.

Hello ​@jbuddecke71 Can you use formula without IFF(). In one thread someone mentioned that  IFF() in Acumatica Report Designer can still evaluate the Substring() side even when the condition should return the other branch, so the guard does not reliably protect you from the bad length. This behavior has been reported with Report Designer expressions where the “true” or “false” expression is evaluated before the final IIf result is chosen.

You can try :
=Substring(IsNull([ARTran.TranDesc], ''), 0, InStr(Concat(IsNull([ARTran.TranDesc], ''), '|'), '|'))