Crystal Error -2147191673 A string can be at most 65534 characters long.
HELLO ALL...
Please *HELP*...this is somewhat of an urgent request. Our Crystal Developer left us in an lurch (uugh!). I know Crystal "to a point"...but when it gets to complex formulas such as below, I admit readily I get very lost and very confused.
We *do* know that it's the (syntax?) of the formula below, that's causing our grief. When the output gets too big, it throws that "Error -2147191673 A string can be at most 65534 characters long" error. The report is accurate and works *perfectly* otherwise, but, unfortunately, the data that is generated from the formula below, is mission critical to our Accounting department.
Again, please help. All we need, is the correction to the formula that's necessary, to negate this error condition. Thank you very much for your time...we appreciate it!...Mark
WhilePrintingRecords;
StringVar Current := {VI_PS_TKT_HIST_LIN_SER.SE
StringVar Prior := if not onFirstRecord then Previous ({VI_PS_TKT_HIST_LIN_SER.S
//New Logic for split parts
NumberVar CurrentX:= Length(Totext (Val(StrReverse(Current & '1')),0,''));
NumberVar PriorX:= Length(Totext (Val(StrReverse(Prior & '1')),0,''));
StringVar CurrentA := if CurrentX > Length (Current) then "" else Current [ 1 to - ( CurrentX )];
StringVar CurrentB := Current [ -(CurrentX-1) to -1];
StringVar PriorA := if Priorx > Length (Prior) then "" else Prior [ 1 to - ( PriorX )];
StringVar PriorB := Prior [ -(PriorX-1) to -1];
StringVar Output;
If OnFirstRecord
then Output:= Current
else
if Val (CurrentB) - 1 <> val (PriorB) or CurrentA <> PriorA //This line now does two separate tests
then Output := Output + ' to ' + prior + CHR(13) + current;
output
Please *HELP*...this is somewhat of an urgent request. Our Crystal Developer left us in an lurch (uugh!). I know Crystal "to a point"...but when it gets to complex formulas such as below, I admit readily I get very lost and very confused.
We *do* know that it's the (syntax?) of the formula below, that's causing our grief. When the output gets too big, it throws that "Error -2147191673 A string can be at most 65534 characters long" error. The report is accurate and works *perfectly* otherwise, but, unfortunately, the data that is generated from the formula below, is mission critical to our Accounting department.
Again, please help. All we need, is the correction to the formula that's necessary, to negate this error condition. Thank you very much for your time...we appreciate it!...Mark
WhilePrintingRecords;
StringVar Current := {VI_PS_TKT_HIST_LIN_SER.SE
StringVar Prior := if not onFirstRecord then Previous ({VI_PS_TKT_HIST_LIN_SER.S
//New Logic for split parts
NumberVar CurrentX:= Length(Totext (Val(StrReverse(Current & '1')),0,''));
NumberVar PriorX:= Length(Totext (Val(StrReverse(Prior & '1')),0,''));
StringVar CurrentA := if CurrentX > Length (Current) then "" else Current [ 1 to - ( CurrentX )];
StringVar CurrentB := Current [ -(CurrentX-1) to -1];
StringVar PriorA := if Priorx > Length (Prior) then "" else Prior [ 1 to - ( PriorX )];
StringVar PriorB := Prior [ -(PriorX-1) to -1];
StringVar Output;
If OnFirstRecord
then Output:= Current
else
if Val (CurrentB) - 1 <> val (PriorB) or CurrentA <> PriorA //This line now does two separate tests
then Output := Output + ' to ' + prior + CHR(13) + current;
output
ASKER
Hi MLMCC...
It is data which contains the unique SERIAL NUMBERS of the Line Items being sold. In MSSQL, this is a Varchar(30) field. Because it's a Varchar field, there can, certainly, be a "mixed bag" of AlphaNumerc characters. It all depends what the Manufacturer specified, for the range of their Serial Numbers.
Thoughts?
Thanks!...Mark
It is data which contains the unique SERIAL NUMBERS of the Line Items being sold. In MSSQL, this is a Varchar(30) field. Because it's a Varchar field, there can, certainly, be a "mixed bag" of AlphaNumerc characters. It all depends what the Manufacturer specified, for the range of their Serial Numbers.
Thoughts?
Thanks!...Mark
I am just trying to figure out what the formula is doing.
It is obviously stringing together the serial numbers.
Where is the formula?
Is there a formula that resets the output to nothing?
mlmcc
It is obviously stringing together the serial numbers.
Where is the formula?
Is there a formula that resets the output to nothing?
mlmcc
ASKER
I'll just upload the Crystal Object. It's in the "PS Ticket Journal Serial Sub.RPT"...so...effectivel
PS-Ticket-Journal.rpt
PS-Ticket-Journal.rpt
A couple of thoughts
Add a formula to that subreport in the report header to initialize it to ""
WhilePrintingRecords;
StringVar Output;
Output := "";
Add a selection formula to the main report so you only run for 1 Group4 value.
That will see if the formula is working.
If that works check if it runs for 2 or 3 group 4.
Then check if a group 2 or 3 runs
mlmcc
Add a formula to that subreport in the report header to initialize it to ""
WhilePrintingRecords;
StringVar Output;
Output := "";
Add a selection formula to the main report so you only run for 1 Group4 value.
That will see if the formula is working.
If that works check if it runs for 2 or 3 group 4.
Then check if a group 2 or 3 runs
mlmcc
ASKER
Hi mlmcc...
Sorry...please don't shoot me...but I'm a "novice" when it comes to formulas such as this. Exactly what do you want me to 'select'? What would that formula look like...and how can do I do this...such that it's only applicable...to the Group4 value?
BTW...I added that snippet that you suggested to the SubReport's header to initialize it...same behavior...same error.
Please let me know....and THANKS again!...Mark
Sorry...please don't shoot me...but I'm a "novice" when it comes to formulas such as this. Exactly what do you want me to 'select'? What would that formula look like...and how can do I do this...such that it's only applicable...to the Group4 value?
BTW...I added that snippet that you suggested to the SubReport's header to initialize it...same behavior...same error.
Please let me know....and THANKS again!...Mark
Open the report
Click REPORT --> SLECTION FORMULA --> RECORD
{VI_PS_TKT_HIST_LIN.LIN_SE
Replace 5 with a valid sequence number.
Click OK
Run the report
mlmcc
Click REPORT --> SLECTION FORMULA --> RECORD
{VI_PS_TKT_HIST_LIN.LIN_SE
Replace 5 with a valid sequence number.
Click OK
Run the report
mlmcc
What kind of values are in VI_PS_TKT_HIST_LIN_SER.SER
Can SER_NO actually be non-numeric (eg. "A1B")? The formula is trying to convert the field to a number, which seems odd if the field may not be numeric, but I also don't get why it's trying to convert the field to a number in the first place, so maybe it makes sense. :-)
The subreport is grouped by VI_PS_TKT_HIST_LIN_SER.SER
I would go into the subreport and remove the group on SER_NO. It seems pointless, and just creates extra work for the report. Then I would add a sort on SER_NO, ascending. If the records are not sorted properly, adding a sort might actually fix your problem (since the formula is trying to build ranges, and it can't do that if the values are not in the right order).
James
Can SER_NO actually be non-numeric (eg. "A1B")? The formula is trying to convert the field to a number, which seems odd if the field may not be numeric, but I also don't get why it's trying to convert the field to a number in the first place, so maybe it makes sense. :-)
The subreport is grouped by VI_PS_TKT_HIST_LIN_SER.SER
I would go into the subreport and remove the group on SER_NO. It seems pointless, and just creates extra work for the report. Then I would add a sort on SER_NO, ascending. If the records are not sorted properly, adding a sort might actually fix your problem (since the formula is trying to build ranges, and it can't do that if the values are not in the right order).
James
ASKER
Hi Folks...thanks for chiming in...it's appreciated :-)
MLMCC...OK, understood...I was over-complicating what you were asking for :-)
JAMES...Because the Serial-#'s are VARCHAR's in the MSSQL database, they can be *anything*...such as:
123456
00045237 (yes, *with* leading zeroes)
ABC123
1004QXR63WZ
001ABC002XYZ
...and so forth. These come from the *manufacturer*...so there's no way to have them changed...based on the product sold...make sense?
Now...here's the kicker...some of these Serial-#'s are for ADMISSION TICKETS, which can then be sold to a Ticket Outlet...and can *easily* number into the 10's of Thousands PER TRANSACTION; BUT, the Groupings of Consecutive *RANGES* is what we're looking for (which is what that formula does), purposely for brevity of output...and particularly...brevity of output on Accounting Journals and Invoice forms. Remember, it depends what's in stock when it's sold, so there may be many multiple *ranges*...per transaction.
I hope that now makes sense...please let me know. I'll try your suggestions, and provide feedback in the morning...Thanks Folks!...Mark
MLMCC...OK, understood...I was over-complicating what you were asking for :-)
JAMES...Because the Serial-#'s are VARCHAR's in the MSSQL database, they can be *anything*...such as:
123456
00045237 (yes, *with* leading zeroes)
ABC123
1004QXR63WZ
001ABC002XYZ
...and so forth. These come from the *manufacturer*...so there's no way to have them changed...based on the product sold...make sense?
Now...here's the kicker...some of these Serial-#'s are for ADMISSION TICKETS, which can then be sold to a Ticket Outlet...and can *easily* number into the 10's of Thousands PER TRANSACTION; BUT, the Groupings of Consecutive *RANGES* is what we're looking for (which is what that formula does), purposely for brevity of output...and particularly...brevity of output on Accounting Journals and Invoice forms. Remember, it depends what's in stock when it's sold, so there may be many multiple *ranges*...per transaction.
I hope that now makes sense...please let me know. I'll try your suggestions, and provide feedback in the morning...Thanks Folks!...Mark
ASKER
Hi Folks...
So, this has been bothering me, and I did now want to wait till the morning to provide feedback. I made both your suggestions, as follows:
MLMCC...putting that criteria on the Record Select...still yielded the error. I believe that when Crystal encounters the read of a given Item-# which has MANY MANY Serial-#'s, where the RANGES are many multiple, within the SAME given Item-#...is when it throws the error...and just shuts down the output.
JAMES...I killed those GROUPS in the SubReport...I agree with you, they seemed very unnecessary. I put a sort on the SERIAL-# field, and while it sped up the report and made it more efficient, it still errors out, and also shuts down the output.
Anything else y'all would like me to try? I'm open to any suggestions...Thanks!...Ma
So, this has been bothering me, and I did now want to wait till the morning to provide feedback. I made both your suggestions, as follows:
MLMCC...putting that criteria on the Record Select...still yielded the error. I believe that when Crystal encounters the read of a given Item-# which has MANY MANY Serial-#'s, where the RANGES are many multiple, within the SAME given Item-#...is when it throws the error...and just shuts down the output.
JAMES...I killed those GROUPS in the SubReport...I agree with you, they seemed very unnecessary. I put a sort on the SERIAL-# field, and while it sped up the report and made it more efficient, it still errors out, and also shuts down the output.
Anything else y'all would like me to try? I'm open to any suggestions...Thanks!...Ma
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Are you looking for ranges on all serial numbers and "equipment" or just for the tickets?
If just for tickets are you filtering for them?
mlmcc
If just for tickets are you filtering for them?
mlmcc
The answer to this question seems to be applicable
https://www.experts-exchange.com/questions/28691016/Need-Immediate-Help-With-Crystal-Throwing-An-Error-2147191673.html?anchorAnswerId=40841151#a40841151
mlmcc
https://www.experts-exchange.com/questions/28691016/Need-Immediate-Help-With-Crystal-Throwing-An-Error-2147191673.html?anchorAnswerId=40841151#a40841151
mlmcc
ASKER
Hi Folks...
MLMCC...No, unfortunately, we cannot employ the Gaps & Islands technique...as the data for Line Items on transactions...is stored differently...and a lot of this is "on the fly". Yes, it's "similar" in concept, but we need so solve this issue, on a Crystal Only basis, unfortunately.
Also, there is NO difference to the Inventory...whether this is Equipment, Admission Tickets, Cell Phones...etc. Serialized Inventory...is Serialized Inventory. The line items just detail the quantity sold, by sequence, by item#, for a given transaction (with pricing and all the other characteristics that are involved...discounts...cos
JAMES...I'll try your suggestion shortly, and provide you feedback.
Thanks a bunch to both of you!...Mark
MLMCC...No, unfortunately, we cannot employ the Gaps & Islands technique...as the data for Line Items on transactions...is stored differently...and a lot of this is "on the fly". Yes, it's "similar" in concept, but we need so solve this issue, on a Crystal Only basis, unfortunately.
Also, there is NO difference to the Inventory...whether this is Equipment, Admission Tickets, Cell Phones...etc. Serialized Inventory...is Serialized Inventory. The line items just detail the quantity sold, by sequence, by item#, for a given transaction (with pricing and all the other characteristics that are involved...discounts...cos
JAMES...I'll try your suggestion shortly, and provide you feedback.
Thanks a bunch to both of you!...Mark
ASKER
Hi James...
Actually...your suggestion...coupled with the "Sort" sequencing...appears to be *working*. It's not throwing that Error condition anymore...and, it appears, that ALL the data...is being outputted correctly now...on the Crystal Report. I'm going to wait until Monday when all Accounting users have blessed this as "kosher"...but for now, it appears that we're in good shape!
Thank you *both* sooooooo much...it's very much appreciated! If our Accounting Department has no issues come Monday, I will reward BOTH of you the points...because we appreciate the help & support of both you "gurus". :-)
Thanks again!...Mark
Actually...your suggestion...coupled with the "Sort" sequencing...appears to be *working*. It's not throwing that Error condition anymore...and, it appears, that ALL the data...is being outputted correctly now...on the Crystal Report. I'm going to wait until Monday when all Accounting users have blessed this as "kosher"...but for now, it appears that we're in good shape!
Thank you *both* sooooooo much...it's very much appreciated! If our Accounting Department has no issues come Monday, I will reward BOTH of you the points...because we appreciate the help & support of both you "gurus". :-)
Thanks again!...Mark
Unless you've got duplicated data, my suggestion can't possibly be showing you everything. It is deliberately discarding 65K characters, possibly more than once for a given set of serial numbers, to avoid getting the error.
The formula is trying to build a list of serial numbers, like
1 to 3
5 to 7
9
11 to 20
and on, and on, and on
When that list is over 65465 characters long (a semi-arbitrary figure that I chose), the formula just replaces everything in the list at that point with the current serial number, and then the process of building a list starts over with that record. Everything saved prior to that record (over 65465 characters) is gone. And if the new list gets to be over 65465 characters, it will do it again. And again. Until the report finally reads the last record in that set and you get a partial list, from the last X records. For the lists that were giving you the error, whatever my version of the formula gives you could be just a fraction of the actual list. It is most definitely not complete.
The idea is to let you see some part of the list from that set of records that was giving you the error, and see if the values look OK.
Maybe you can see places where it's not using ranges, but it should. In that case, the problem may be that you're getting a lot of individual values in the list where you could have ranges. For example, "1 to 100" is just 8 characters, while a list of all of those numbers ("1", "2", "3", ... "100") is many times longer. So maybe the string is too long because you're getting individual values, instead of ranges. If so, getting the formula to build the ranges correctly (if that's possible) might fix the problem.
OTOH, maybe you're getting ranges where you should, and there are just too many values. In that case, your only choice may be to break the string up into pieces.
If you're having trouble identifying the lists that have been cut off, try changing
if Length (Output) > 65465 then
Output := current
to
if Length (Output) > 65465 then
Output := "~~~" + Chr (13) + current
Then any list that starts with "~~~" on the first line was cut off by the formula, meaning that at least 65466 characters in that list were discarded, possibly more than once.
Now, if none of the lists start with "~~~", that means that none of them were cut off, which means that none of them exceeded the 65465 character limit I set, which means that my change had nothing to do with not getting the error. :-) In that case, something else, maybe how you're sorting the records, changed the lists that you're getting, and now none of them exceed the 65534 character limit. If that's the case, you can remove my changes and put the formula back the way it was.
James
The formula is trying to build a list of serial numbers, like
1 to 3
5 to 7
9
11 to 20
and on, and on, and on
When that list is over 65465 characters long (a semi-arbitrary figure that I chose), the formula just replaces everything in the list at that point with the current serial number, and then the process of building a list starts over with that record. Everything saved prior to that record (over 65465 characters) is gone. And if the new list gets to be over 65465 characters, it will do it again. And again. Until the report finally reads the last record in that set and you get a partial list, from the last X records. For the lists that were giving you the error, whatever my version of the formula gives you could be just a fraction of the actual list. It is most definitely not complete.
The idea is to let you see some part of the list from that set of records that was giving you the error, and see if the values look OK.
Maybe you can see places where it's not using ranges, but it should. In that case, the problem may be that you're getting a lot of individual values in the list where you could have ranges. For example, "1 to 100" is just 8 characters, while a list of all of those numbers ("1", "2", "3", ... "100") is many times longer. So maybe the string is too long because you're getting individual values, instead of ranges. If so, getting the formula to build the ranges correctly (if that's possible) might fix the problem.
OTOH, maybe you're getting ranges where you should, and there are just too many values. In that case, your only choice may be to break the string up into pieces.
If you're having trouble identifying the lists that have been cut off, try changing
if Length (Output) > 65465 then
Output := current
to
if Length (Output) > 65465 then
Output := "~~~" + Chr (13) + current
Then any list that starts with "~~~" on the first line was cut off by the formula, meaning that at least 65466 characters in that list were discarded, possibly more than once.
Now, if none of the lists start with "~~~", that means that none of them were cut off, which means that none of them exceeded the 65465 character limit I set, which means that my change had nothing to do with not getting the error. :-) In that case, something else, maybe how you're sorting the records, changed the lists that you're getting, and now none of them exceed the 65534 character limit. If that's the case, you can remove my changes and put the formula back the way it was.
James
mlmcc