Percentage Sign handling in NAV is dangerously flaky

rocatis

For quite some time now I've been frustrated by the apparently inconsistent behaviour of STRSUBSTNO. Eventually I needed to get a grip on the exact shortcomings of this function.

Behold, these are my findings in the form of a "commented report".

OBJECT Report 88888 Percentage Sign Trouble
{
  OBJECT-PROPERTIES
  {
    Date=28-10-11;
    Time=14:30:37;
    Version List=;
  }
  PROPERTIES
  {
    ProcessingOnly=Yes;
    OnPreReport=BEGIN
                  // Percentage Sign handling in NAV is dangerously flaky.

                  // For quite some time now I've been frustrated by the apparently inconsistent behaviour of STRSUBSTNO.
                  // Eventually I needed to get a grip on the shortcomings of this function. This is the result.

                  MESSAGE('%1',STRSUBSTNO('This should output 10%: %1%',10));  // This works.
                  MESSAGE('%1',STRSUBSTNO('This should output 10%: %1%2',10,'%'));  // So does this.
                  MESSAGE(STRSUBSTNO('This should output 10%: %1%',10));  // But this doesn't (it even puts the result on a separate line!)
                  MESSAGE(STRSUBSTNO('This should output 10%: %1%2',10,'%'));  // And neither does this  (also puts the result on a separate line)

                  // How about putting the message into a text variable before showing it?
                  String := STRSUBSTNO('This should output 10%: %1%',10);
                  MESSAGE('%1',String);  // This works.
                  MESSAGE(String);  // But this doesn't.

                  // This demonstrates that it is in fact not STRSUBSTNO that's the culprit. It's MESSAGE.
                  // Let's streamline the example:
                  MESSAGE('%1','This should output 10%: 10%');  // This works.
                  MESSAGE('This should output 10%: 10%');  // But this doesn't. Like, not at all.

                  // I've created a text constant with the same value as String below.
                  // In essence, you would expect all three messages to display the same. But they don't,
                  String := 'This should output 10%: 10%';
                  MESSAGE('This should output 10%: 10%');  // This doesn't work.
                  MESSAGE(String);  // But this does.
                  MESSAGE(TextConstant);  // And so does this.

                  // By now I guess my suggestion would be to always replace % with the text 'pct.'...

                  // But the percent sign is not nearly done creating havoc. It gets much worse:
                  MESSAGE(STRSUBSTNO('This should output X0X: %1%2%1','X',0));  // This works.
                  MESSAGE(STRSUBSTNO('This should output 909: %1%2%1','9',0));  // This doesn't.
                  MESSAGE(STRSUBSTNO('This should output 909: %1%2%1',9,0));  // And neither does this.

                  // You get even "funnier" results using this method:
                  MESSAGE('%1',STRSUBSTNO('This should output X0X: %1%2%1','X',0));  // This works.
                  MESSAGE('%1',STRSUBSTNO('This should output 909: %1%2%1','9',0));  // This fails spectacularly.
                  MESSAGE('%1',STRSUBSTNO('This should output 909: %1%2%1',9,0));  // So does this.

                  // The last two statements show that it doesn't matter if the number is an actual integer or a text.
                  // Too bad as this ensures you can't do a workaround using FORMAT.
                  // It also means that your code could be working perfectly for years until one of the parameters for one reason or another.
                  // is changed from a text to a number (say, a setup parameter defining a file path).

                  // So why haven't you bumped into this before? Well, that's when it gets seriously messed up:
                  MESSAGE('%1',STRSUBSTNO('This should output 909: %1%2%3',9,0,9));  // This works.
                  MESSAGE('%1',STRSUBSTNO('This should output 909: %1%2%1',9,0,9));  // While this fails.

                  // What this shows is that the ORDER of the % fields affects the result!
                  // In essence you should ALWAYS use parameters sequentially. Too bad if you using STRSUBSTNO to achieve something like this:
                  String := STRSUBSTNO('%1%4%2%4%3%4','field1','field2','field3',';'); // The semicolon represents a delimiter, e.g. for data export.
                  MESSAGE(String);  // This actually works! But why? Because none of the parameter values are numbers. Observe:
                  String := STRSUBSTNO('%1%4%2%4%3%4','field1','999','field3',';');
                  MESSAGE('%1',String);  // I was using this method to create a delimited file and I'm still seeking professional help.
                  MESSAGE(String);  // It's even better if you resort to debugging using MESSAGEs... Now one of the fields have vanished!}

                  // The error does not affect the first parameter. That would be too easy. The remaining ones do exert a horrible revenge, though:
                  String := STRSUBSTNO('%1%4%2%4%3%4','1','2','3',';');  // Expect "1;2;3;"
                  MESSAGE('%1',String);  // Not "1;2;3;". Not even close.
                  MESSAGE(String);  // Distance from the expected result is measured in light years for this one...

                  // Incidentially, did you ever try to to MESSAGE a file path but instead got a new line for each backslash?
                  // The MESSAGE('%1',... workaround handles this too:
                  MESSAGE('%1','C:\Program Files');  // This gives you the backslash.
                  MESSAGE('C:\Program Files');  // This gives you a new line.

                  // However, in this case it actually makes sense as it tells MESSAGE to either show the string "as is" or subject it to NAV's
                  // internal formatting routine. It's obviously the same logic that makes the % examples work with MESSAGE('%1',...
                  // But if you're unlucky you'll end up in a situation where you actually need both the "as is" result and the formatted result:
                  String := STRSUBSTNO('This should output 10% on a new line:\%1%',10);
                  MESSAGE('%1',String);  // This doesn't work (everything is on one line)
                  MESSAGE(String);  // Neither does this.

                  // The last example actually converts the first % to a new line for no apparent reason, resulting in a 3 line message...

                  // I'm not overwhelmingly impressed.
                END;

  }
  DATAITEMS
  {
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      String@1000000000 : Text[1024];
      TextConstant@1000000001 : TextConst 'ENU=This should output 10%: 10%';

    BEGIN
    END.
  }
}

kine

Yes, it sucks. And sometime it do not work at all, e.g. in SETFILTER when using something like SETFILTER(field,'*%1*',value).

vaprog

kine wrote:

Yes, it sucks. And sometime it do not work at all, e.g. in SETFILTER when using something like SETFILTER(field,'*%1*',value).

With SETFILTER the following appears to happen behind the scenes:

Record.SETFILTER(Field, String, [Value],...)

• The String parameter is being parsed as a filter expression. The following operators are considered to have special meaning:

  .. & | < <= > >= <>

  Wildcard characters * ? and @ are not included in that list!
• Whatever is in between those operators is considered to be a value to filter for. If any of those values consist of only a %-sign and a following position number, and a Value parameter corresponding to that position exists, the value of that Value parameter is used instead.
• If that substitution value is the empty string or contains any of the filter operators mentioned above, the value is enclosed in single quotes (even if it was already).
• Wildcard characters always do their magic, regardless of where they were provided, be it the Sting parameter or a Value parameter, quoted or not.

In the case of kine's example *%1* is the one and only filter value. Because it consists of more than a %-sign and a position value, no substitution takes place. value (the last parameter in his example) remains unused. The applied filter expression is *%1*. If field contains the literal string %1 anywhere, it passes the filter.

Thus, it is impossible to filter properly for a value that happens to contain a wildchar character.

The basic approach to first parse the filter expression in String, and then fill in the Value(s) is perfectly good. Only it should be clearly documented. But wildchar characters should be treated exactly as the filter operators, in my opinion, both when it comes to build the final filter expression and regarding quoting.

sorinz

Actually, the STRSUBSTNO function works in most cases, the problem is how the MESSAGE command interprets the wildcard characters like vaprog said.
instead of using MESSAGE try to write to a txt file and you will see a different result.

STRSUBSTNO problems are here like in your example (without any logical explication)

1
STRSUBSTNO('This should output 909: %1%2%1','9',0)); // This fails spectacularly.
I use a character that I'll extract after using the function one like [
DELCHR(STRSUBSTNO('This should output 909: %1%2%1','[9','[0')),'=','[') ; the result should be 909

2
STRSUBSTNO('%1%4%2%4%3%4','1','2','3',';'); // Expect "1;2;3;"
MESSAGE('%1',String); // Not "1;2;3;". Not even close.
in this case it wil work only if you put the ; character to be the first variable ()
STRSUBSTNO('%2%1%3%1%4%1',';','1','2','3')); // Expect "1;2;3;"

rocatis

sorinz wrote:

Actually, the STRSUBSTNO function works in most cases

Yeah, that's kind of the problem :? Your code may run with no problems for years and then suddenly malfunction for no (documented) reason.

sorinz wrote:

I use a character that I'll extract after using the function one like [
DELCHR(STRSUBSTNO('This should output 909: %1%2%1','[9','[0')),'=','
Unfortunately that effectively ruins the otherwise structured and easily readable statements that you can use STRSUBSTNO for. Take your example, substituting the constants with fields:

DELCHR(STRSUBSTNO('This should output 909: %1%2%1','['+field1,'['+field2)),'=','[') ;

Not to mention that any legit [ characters will be stripped as well.

sorinz wrote:

STRSUBSTNO('%1%4%2%4%3%4','1','2','3',';'); // Expect "1;2;3;"
MESSAGE('%1',String); // Not "1;2;3;". Not even close.
in this case it wil work only if you put the ; character to be the first variable ()
STRSUBSTNO('%2%1%3%1%4%1',';','1','2','3')); // Expect "1;2;3;"

Yes, because unlike the rest of the parameters, the first parameter is not messed up. That's inconsistent, and inconsistencies kill programmers ](*,)