Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

402 formulas with shared arrays, only first 100 work, deleting first 100, makes 101-200 work

Status
Not open for further replies.

XrayboX

Technical User
Oct 8, 2002
104
US
Crystal reports 2016
OLE DB to Microsoft SQL database

I have a database field "permissions" that is loaded with up to a 536 character string of "Y" or "N" with each position in the string representing a security state for different areas within an app (moronic, I know, but out of my control)
I need to build a report that shows the state of every area.
My plan was to parse through the field and load a shared boolean array (perm_on) with the states, then because those positions in the string aren't the same as the report order, I created another shared array (sort_order) to track report position, vs field position (far less apt to muck it up;) finally I also planned on using formulas to output a text string representing what was granted permission, so I hard coded a shared array perm_name with those values.
Originally I planned on using arrays directly in fill box formulas, but while I can declare shared variable arrays there, as soon as I use them in the if statement, crystal reports crashes
(code example that crashes crystal report)
Code:
shared numbervar array sort_order;
shared booleanvar array perm_on;

if perm_on[sort_order[1]
then crBlack
else crNocolor;

I created over 810 formulas, the first formula created shared arrays, the second loaded it, the third was a test formula wasn't in use when problem first appeared, and doesn't impact result when used.
Formulas 4-405 are being used to show text for what permissions were for, being easier to title them as sequential numbers (001-402), followed by menu level (L1-L4), then area (sales/orders/etc)

Finally, formulas 406-808 being the one's I have issues with. which are formulas that result in a boolean to toggle fill state on boxes next to respective area labels
when I discovered those issues, I first deleted formulas 4-405 figuring I'd manually create text boxes, when deleting those didn't work, I tried deleting the first few boolean formulas, for every one I delete, one more further down the list works.
so


so first step, I made a formula to create the shared arrays, redim them based on len of "permissions", then loaded the sort_order and perm_name with a hard coded values

Code:
shared stringvar array perm_bit;
shared numbervar array sort_order;
shared stringvar array perm_name;
shared booleanvar array perm_on;
shared numbervar perm_length;
local numbervar i;


perm_length:=len({PermissionCodes.Permissions});

redim perm_bit[perm_length];
redim sort_order[perm_length];
redim perm_name[perm_length];
redim perm_on[perm_length];


sort_order[1]:=1;
sort_order[2]:=2;
sort_order[3]:=3;
sort_order[4]:=4;
sort_order[5]:=5;
sort_order[6]:=6;
sort_order[7]:=370;
sort_order[8]:=382;
sort_order[9]:=9;
sort_order[10]:=7;
sort_order[11]:=8;
snip.......
sort_0rder[536]:=536;

perm_name[1]:="Point Of Sale";
perm_name[2]:="Left Side Menu";
perm_name[3]:="Sales";
perm_name[4]:="Orders";
perm_name[5]:="Quotes";
snip....
perm_name[485]:="Requests";   // perm_name loaded out of order because I used the spreadsheet which has the menu items, and their order to create the code
"";


after that I created a the second formula to load shared array perm_on ( I know I don't need variable perm_bit, but I created it when debugging earlier issue with misspelled variable ;)

Code:
shared stringvar array perm_bit;  
shared numbervar array sort_order;
shared booleanvar array perm_on;
shared numbervar perm_length;
local numbervar i;

for i:=1 to perm_length do
(
    perm_bit[i]:=mid({PermissionCodes.Permissions},i,1);
    if perm_bit[i]="Y"
        then perm_on[i]:=true
        else perm_on[i]:=false;
);
"";


I created 402 formulas called B001 to B402
with the code returning true or false based on permission location that was stored in sort_order
example B402 has the code
Code:
shared numbervar array sort_order;
shared booleanvar array perm_on;

perm_on[sort_order[402]];

for each of the boxes I used code that looked at the correct "B" formula ((B001 to B402) based on it's position in the report) in the fill formula (I guess that technically means I created over 12,00 formulas ;)
Code:
if {@B001}
then crBlack
else crNocolor;

I checked SAP, they mention various limitations, but nothing specific about formulas.

After typing this question out, I wonder if it has to do with the number of formulas resulting in boolean, versus just total number of formulas or even number of boxes with formulas?


When Linux is Free and Open, who needs Windows or Gates?
 
I have had over a hundred formulas before, but there is a hard limit of 100 values for an array. The only way around it is use Crystal Reports for Enterprise.
 
Kray4660, SAP says the limit for arrays is 1,000 elements (values)
From Crystal reports 2016 help said:
Limitations (Crystal syntax)
For reference purposes, here are the sizing limitations of the formula language:

The maximum length of a String constant, a String value held by a String variable, a String value returned by a function or a String element of a String array is 65,534 characters.

The maximum size of an array is 1000 elements.

The maximum number of arguments to a function is 1000. (This applies to functions that can have an indefinite number of arguments such as Choose).

The maximum number of loop condition evaluations per evaluation of a formula is 100,000. (See Safety mechanism for loops (Crystal syntax) for the precise meaning of this). Note that you can use the Option Loop (Crystal Syntax) statement to change this limit.

Date-time functions modeled on Visual Basic accept dates from year 100 to year 9999. Traditional Crystal Reports functions accept dates from year 1 to year 9999.

There is no limit on the size of a function.

and I'm able to load and use over 400 elements (values) in a single array without a problem (all the arrays I'm working with actually contain 536 elements because I re-dimension them based on the length of the database field {PermissionCode.permissions} ).
The only issue I seem to have is when I try to use multiple formulas that return a boolean, that's where I hit a "100" limit.

I've changed the formulas to return a number instead of boolean.
Code:
shared numbervar array sort_order;
shared booleanvar array perm_on;

if perm_on[sort_order[402]] then 1;
Now I can have over all 402 formulas returning the expected result
now it appears there's an issue with sub sections.
if I drop all the formulas in section "Report Footer a" they work, but the same formulas in section "Report Footer b" all return 0, regardless of if they're used in section "Report Footer a" :/
I also tried using group footers a, & b, section "Group Footer a" works and section "Group Footer b", doesn't work
To be clear, not sure if old formulas with boolean results also failed in "b" sections because the first 100 were in section "Report Footer a", and when I first ran into a problem, I thought it was the section that was breaking it but moving formula's 101-200 to section "Group Footer a" didn't fix it.
will keep plugging away and reporting my results.




When Linux is Free and Open, who needs Windows or Gates?
 
Opps!! bad memory. For some reason I remember a hard limit at 100. Not sure what that was for.
 
Kray4660
Was curious so I did some digging, nothing wrong with your memory
Crystal Reports 6 supported a max of 100 elements in an array, in CR7 it went up to 250, and in CR8 it was pushed up to current limit of 1,000.



When Linux is Free and Open, who needs Windows or Gates?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top