Array not building array.

[scott_selph]

I have an array that will not build an array of the values.  It is is printing each unique value of "{UPORTALCLOCKS.UPCNOTE}" in the details section on a new detail record, and then repeats.  Am needing the array to print all the values of {UPORTALCLOCKS.UPCNOTE} in one instance of the Print (Group Footer 2), followed by a hard character return (chr(10)) to list the next item in the array below the previous item.

Does anyone see any errors in the below?

Thanks!

Initialize (Group Header 2)

shared stringVar array JobNotes := "";

Shared numberVar x := 1;

Load (Details)

Shared numberVar x;

Shared stringVar array JobNotes;

x := x+1;

redim preserve JobNotes[x];

JobNotes [x] := {UPORTALCLOCKS.UPCNOTE} & " @" & totext({UPORTALCLOCKS.UPCDATETIME}, 'yyyy-MM-dd') & " " & {UPORTALCLOCKS.UPCEMPLOYEE};

Print (Group Footer 2)

Shared stringVar array JobNotes;

whileprintingrecords;

Join(JobNotes,ChrW (10))

[abhilash_kumar · Accepted solution]

Hi Scott,

The Initialize formula should be:

shared stringVar array JobNotes := "";

Shared numberVar x := 0;

The Print formula should be:

Shared stringVar array JobNotes;

Join(JobNotes,Chr(10))

Also, right-click this formula field > Format field > Common tab > Check 'Can Grow'.

-Abhilash

[scott_selph · Accepted solution]

Thanks.  The Print formula is now printing the desired results.  However, when I insert the "load" formula into the group headed it causes the "remaining ops" array to start duplicating results the same number of times as the count of JobNotes that are displayed.  Any idea as to why?

This is before:

This is after:

[abhilash_kumar · Accepted solution]

Duplicates are usually due to an incorrect join.

A quick fix is to modify the formula so that it only accumulates unique values:

What is the code for the remaining ops formula?

-Abhilash

[scott_selph · Accepted solution]

1) How would I edit the formula to exclude unique values in the join?

2) Following is the code for the remaining ops.

Intialize JobOps (In Group2 Header)

shared stringVar array JobOps := "";

Shared numberVar x := 1;

Load JobOps (In Detail-supressed)

Shared numberVar x;

Shared stringVar array JobOps;

If {JobOperations.jmoProcessID} <> '' and {JobOperations.jmoProductionComplete} = 0 then

(

     x := x+1;

     redim preserve JobOps[x];

     JobOps [x] := {JobOperations.jmoProcessID};              

);

'';

Print JobOps (In Group2 Footer)

Shared stringVar array JobOps;

Join (JobOps, " ")

[abhilash_kumar · Accepted solution]

Try:

Shared numberVar x;

Shared stringVar array JobOps;

If {JobOperations.jmoProcessID} <> '' and {JobOperations.jmoProductionComplete} = 0 and Not({JobOperations.jmoProcessID} IN JobOps) then

(

     x := x+1;

     redim preserve JobOps[x];

     JobOps [x] := {JobOperations.jmoProcessID};             

);

'';

Also, the initialize should be:

shared stringVar array JobOps := "";

Shared numberVar x := 0;

-Abhilash

[scott_selph · Accepted solution]

In the Load JobNotes (original posted code), I am now getting the error "array dimension must be between 1 and 1000".  It is highlighting the line " redim preserve JobNotes[x]; "

Load JobNotes:

Shared numberVar x;

Shared stringVar array JobNotes;

x := x+1;

redim preserve JobNotes[x];

JobNotes [x] := {UPORTALCLOCKS.UPCNOTE} & " @" & totext({UPORTALCLOCKS.UPCDATETIME}, 'yyyy-MM-dd') & " " & {UPORTALCLOCKS.UPCEMPLOYEE};

[abhilash_kumar · Accepted solution]

You cannot use the same variable - 'x' in both the formulas. Change the JobOps formula to:

Shared numberVar y;

Shared stringVar array JobOps;

If {JobOperations.jmoProcessID} <> '' and {JobOperations.jmoProductionComplete} = 0 and Not({JobOperations.jmoProcessID} IN JobOps) then

(

     y:= y+1;

     redim preserve JobOps[y];

     JobOps [y] := {JobOperations.jmoProcessID};            

);

'';

Also add the variable 'y' to the Initialize formula:

shared stringVar array JobOps := "";

Shared numberVar x := 0;

Shared numberVar y := 0;

-Abhilash

[scott_selph · Accepted solution]

Ok, it is really close to what I need.  Only issue now is that the JobNotes are repeating.  Example if there are 5 unique notes, then the 5 notes are being repeated multiple times. See JobNotes column at far right.

[abhilash_kumar · Accepted solution]

You could use the same logic for the JobNotes code too:

Shared numberVar x;

Shared stringVar array JobNotes;

If  Not({UPORTALCLOCKS.UPCNOTE} & " @" & totext({UPORTALCLOCKS.UPCDATETIME}, 'yyyy-MM-dd') & " " & {UPORTALCLOCKS.UPCEMPLOYEE} IN JobNotes) Then

(

x := x+1;

redim preserve JobNotes[x];

JobNotes [x] := {UPORTALCLOCKS.UPCNOTE} & " @" & totext({UPORTALCLOCKS.UPCDATETIME}, 'yyyy-MM-dd') & " " & {UPORTALCLOCKS.UPCEMPLOYEE};

)

-Abhilash

[scott_selph · Accepted solution]

Abhilash, thank you so much!  One last question (I hope).

If {UPORTALCLOCKS.UPCNOTE} is empty (null or ""), how would I exclude this from the array and add this to the "If not..." criteria?  Note that this is a memo field, thus I can't use the Select Expert to build a quick filter.

[abhilash_kumar · Accepted solution]

That would be:

If  {UPORTALCLOCKS.UPCNOTE} <> '' AND Not({UPORTALCLOCKS.UPCNOTE} & " @" & totext({UPORTALCLOCKS.UPCDATETIME}, 'yyyy-MM-dd') & " " & {UPORTALCLOCKS.UPCEMPLOYEE} IN JobNotes) Then

In the formula editor for this formula, make sure 'Default Values for Nulls' is selected from the drop-down on the top. The default selection in that drop-down is 'Exceptions for Nulls'.

-Abhilash

[scott_selph · Accepted solution]

Thank you so much.  My report is now exactly as I wished/needed.

I cannot convey my gratitude for all of your assistance.  I sincerely thank you for taking time to assist me on my report.

[scott_selph · Accepted solution]

Would you please revisit this?  If {JobOperations.jmoProcessID} is null, then this array does not print the {UPORTALCLOCKS.UPCNOTE}.  I don't see how the ProcessID is affecting the Note.

Here is the load you coded for the "Notes"

Shared numberVar x;

Shared stringVar array JobNotes;

If  ({UPORTALCLOCKS.UPCNOTE}) <> "" and Not({UPORTALCLOCKS.UPCNOTE} & " @" & totext({UPORTALCLOCKS.UPCDATETIME}, 'yyyy-MM-dd') &

" " & {UPORTALCLOCKS.UPCEMPLOYEE} & "***" IN JobNotes) Then

(

x := x+1;

redim preserve JobNotes[x];

JobNotes [x] := ({UPORTALCLOCKS.UPCNOTE} & " @" & totext({UPORTALCLOCKS.UPCDATETIME}, 'yyyy-MM-dd') & " " & {UPORTALCLOCKS.UPCEMPLOYEE} & "***");

)

[abhilash_kumar · Accepted solution]

I don't see why the Process ID field should cause this.

Could you open this formula and make sure 'Default Values for Nulls' is selected from the drop-down on the top?

Also, is this array being printed on the Group Footer? Perhaps the {UPORTALCLOCKS.UPCNOTE} field is blank?

-Abhilash

[scott_selph · Accepted solution]

The default for nulls is selected.  Here is an excel version created via SQL.  The Note field contains data.