receiving error message on split formula

[tsh1586]

Hey guys I have the following formula:
//{@one}
if ubound(Split({@Tax County Footer}, ","))>0 then(split({@Tax County Footer}, ",")[1]) else "0"
I believe I am having an issue because in the split I have some records that are null if this is the case it needs to skip @one and go to the @two group to process. I had the same issue with my group 2 originally but I was able to fix it. I have tried several different formulas on this one and no luck.
Please Help
Thanks

 

[kray4660]

What is the error message?

 

[tsh1586]

Array's dimension must be an interger between 1 and 1000

 

[lbass]

If you are trying to break a string into two pieces, you probably don’t need to use split() or arrays. A first step is to show the contents of {@Tax County Footer} and of any nested formulas. Secondly, you should explain what you are trying to do with your {@one} and {@two} formulas.

-LB

 

[tsh1586]

So I am pulling from a field that can have up to two values. I had to use a stringvar formula to show both amounts. I then used the split to break these two values into separate fields one is state tax one is county tax. I had to pull the items I needed from the details group into the group footer so I did not see the multiple lines for each invoice that showed the tax. The county footer is the county portion of the sales tax string.
whileprintingrecords;
stringvar county;
Mid(county,2);
I have the same type of setup for the state. I do not use crystal very often so I am trying to figure out as I go. I believe I got the state formula fixed I was having the same error with it I used the following formula for it:
//{@two}
If ubound (split({@Group Footer}, ","))>1 then Split({@Group Footer}, ",")[2];

Please let me know what I am doing wrong.
thanks

 

[lbass]

You need to show content of all formulas. Can’t follow this so far. It might help if you show a mock up of sample data and what you would like your formula to generate as a a result of that.

-LB

 

[tsh1586]

Below is a sample of the output without the split function:

Vendor Paid Date Invoice# Invoice Amt SalesTax Amount County
BB&T 11/18/18 101118 106.45 2.13 Union
BB&T 11/18/18 101118 106.45 5.06 NC State

I grouped the report by vendor number so I could see all of the tax entries for each invoice. The Sales Tax is coming from the invoice distribution table field is named amount. I used the split to combine both amounts on one line. I pulled the vendor name, paid date, invoice number, and invoice amount to the group footer section and I suppressed the detail section.
I used the following formulas to split the tax to show on the same line (I tried several this is the only one I could get to work)
In the Group Header (Header): whileprintingrecords;stringvar names := "";
In the Details (Details): whileprintingrecords;stringvar names;names := names & ", " & {tblAPInvDistributions.Amount};
In the Footer (Group Footer): whileprintingrecords;stringvar names;Mid(names,2);
This worked to show both amounts separated by a comma: 2.13,5.06
I then created the following formula to show just the first amount(county footer):
//{@one}if NOT IsNull ({@Group Footer})then if isnumeric (Split({@Group Footer}, ",")[1]) then ToNumber((Split({@Group Footer}, ","))[1])else 0

I did the same breakdown for the state calculation

 

[lbass]

You should do the null check on the amount field, not on the formula as a whole, since I’ guessing the name field is always non-null. You might want to set it up so a null will appear as a 0, as in:

Names := names &”,”&if isnull({table.amt}) then 0 else {table.amt}

Rewrite this within your names formula (don’t use my quote marks as they will cause the formula to fail).

It concerns me that you are capturing results in the group footer—are you sure there will always be only two rows per group?

Another approach would be (if there are only two rows per invoice #) to create conditional formulas like this for the detail section:

If {table.county}=“State” then {table.amt}

If {table.county}<> “State” then table.amt}

Then insert maximums on the formulas at the group level.

-LB

 

[tsh1586]

I put the isnull formula in name still got the error message regarding the array. I tried the state formula originally before the split. It would not work always pulls in a zero amount for the state.

 

[lbass]

Are the tax amounts ever in the 1000's? This would cause the formula to fail. Try changing the "," to a "^" in both the names formula and the split formulas, and then report back, please.

Also, please post your state formula, if only it is failing.

-LB

 

[tsh1586]

Will do... It's my county that is failing not the state with the current formulas (the array formula). The state only failed when I try to do the state formula within the details with the conditional formula

 

[lbass]

Also, I think your formulas should check for number of members in the array:

//{@one:
if ubound(split({@group footer},","))>=1 then
ToNumber(split({@group footer}, ",")[1])else 0

//{@two}:
if ubound(split({@group footer},","))>1 then
tonumber((Split({@Group Footer},",")[2]))else 0

-LB

 

[tsh1586]

I had some transactions that were over 1,000 when I changed all of the formulas to the ^ I am not receiving the message anymore. Thanks so much for your help. Do I still need to check the number of members?

 

[lbass]

It can’t hurt, and adds another check.

-LB

 

[tsh1586]

thanks again for all of your help