Help with editing an array formula to add another condition 1

[elsenorjose]

Hello folks,

Environment: Windows XP, CRXI R2.

I've inherited some reports which contain arrays and variables to display data. My challenges:

a) I don't understand arrays and variables very well and

b) I need to edit a formula to add another condition to test for to display certain data.

Here is the original formula:

StringVar Array SessionNo := Split({V_ATH_ACT_TRNG_HISTORY.ATH_TRAINING_NO}, "   ");
NumberVar i;
StringVar FormattedSessionNo := SessionNo[1];
If Count(SessionNo) > 1 Then
(
    For i := 2 to Count(SessionNo)
    do
    (
    If Trim(SessionNo[i]) <> "" Then
            FormattedSessionNo := FormattedSessionNo + ", " + SessionNo[i]
    );
  );
FormattedSessionNo;

First of all I'm not really sure I understand what this formula is doing, exactly. I know it's supposed to display a formatted session ID based on the results of the formula but like I said, arrays are not my strong suit. Additionally, I need to add a new condition to the formula so that it evaluates another field in the DB and displays a string on the report based on that evaluation:

if {V_ATH_ACT_TRNG_HISTORY.ATH_SOURCE_TYPE} = 1 then "Self Train"

I have no idea where I would add this condition. I've tried adding it to the end of the formula with an 'Else if' but I get a 'The remaining text does not appear to be part of the formula' error. If I put it before the array declaration, I get a 'This array must be subscripted' error.

I'm not sure if this is enough information to go off of but I'd really appreciate any help. Thanks.

 

[beltmanjr]

if {V_ATH_ACT_TRNG_HISTORY.ATH_SOURCE_TYPE} = 1 then 
    "Self Train"
else
(
    StringVar Array SessionNo := Split({V_ATH_ACT_TRNG_HISTORY.ATH_TRAINING_NO}, "   ");
    NumberVar i;
    StringVar FormattedSessionNo := SessionNo[1];
    If Count(SessionNo) > 1 Then
    (
        For i := 2 to Count(SessionNo)
        do
        (
        If Trim(SessionNo[i]) <> "" Then
                FormattedSessionNo := FormattedSessionNo + ", " + SessionNo[i]
        );
      );
    FormattedSessionNo;
)

As for the array code, it seems to get a string from the database field {V_ATH_ACT_TRNG_HISTORY.ATH_TRAINING_NO} which looks something like (I guess)
'11 20 45 71 etc' (made up some values here)
Then it splits this string on the big empty spaces between the numbers (perhaps it is a tab when inserted). That is done by
Split({V_ATH_ACT_TRNG_HISTORY.ATH_TRAINING_NO}, " ");

Result is an array, which kind of 'temp table' in this case called 'SessionNo' looking like:

key value
1 11
2 20
3 45
4 71

The key is how you call one of the values from this array
So SessionNo[3] gives value 71.

Next the formula checks for the array, starting from key 2 (For i:=2.....) if the value is empty (or only contains spaces) using the trim function.

If not empty it adds the value of the key to the previous one with a comma in between. Result is one string like:
11,20,45,71


As far as the new condition goes, if this is the most important thing then put it on top as I did in the code above.

If you get a 'the remaining text is not part of the formula bla bla' message, remove or add some ;. It usually jumps to where this is going wrong.

Good luck

 

[elsenorjose]

Thanks for both the solution and the explanation. Very helpful!