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.SER_NO};
StringVar Prior := if not onFirstRecord then Previous ({VI_PS_TKT_HIST_LIN_SER.SER_NO}) else 'x1';

//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
datatechcorpAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
What is the data in the {VI_PS_TKT_HIST_LIN_SER.SER_NO} field?

mlmcc
datatechcorpAuthor Commented:
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
mlmccCommented:
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
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

datatechcorpAuthor Commented:
I'll just upload the Crystal Object.  It's in the "PS Ticket Journal Serial Sub.RPT"...so...effectively (to my limited knowledge) the formula resets anytime the Sub Report is called again into action (anyway, that's my understanding).  The purpose of using this formula, is to output only "RANGES" of Consecutive Serial-#'s.  If this is not done, then there could be, literally, 10's of thousands of individual Serial-#'s outputted from Crystal...which would be completely unacceptable to Accounting.  I hope what I'm saying makes sense.  Anyhoo, I'm uploading the Crystal Object to EE...if that helps at all...Thanks!...Mark
PS-Ticket-Journal.rpt
mlmccCommented:
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
datatechcorpAuthor Commented:
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
mlmccCommented:
Open the report
Click REPORT --> SLECTION FORMULA --> RECORD

{VI_PS_TKT_HIST_LIN.LIN_SEQ_NO} = 5

Replace 5 with a valid sequence number.

Click OK
Run the report

mlmcc
James0628Commented:
What kind of values are in VI_PS_TKT_HIST_LIN_SER.SER_NO ?  Is it just a single value or some kind of list or range?  if it's just a single value, the formula seems way over-complicated.

 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_NO, which seems completely unnecessary.  But perhaps more to the point, the group is using Original order, meaning unsorted.  You presumably need the records to be sorted by SER_NO, so that you can build your ranges.  Maybe they should already be sorted that way in the db, but, even if that's true, I wouldn't want to rely on them being in that order when they get to the report.

 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
datatechcorpAuthor Commented:
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
datatechcorpAuthor Commented:
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!...Mark
James0628Commented:
You're trying to build a list of ranges.  Do you only have a range when there is a record with every value in that range?  For example, do you only have a range of "1 to 4" if you have records with "1" and "2" and "3" and "4"?  Or if you have records with "1" and "4", does that count as a range of "1 to 4"?  Just trying to better understand your data.

 The following is not meant to fix your problem.  I'm just trying to avoid the error, so you can see some of the data that's causing it and see if it looks correct.  I don't know if the error is because the ranges are not being built correctly (so you're getting a lot of individual values, instead of ranges), or if you just have too much data.  If you can see some of it, you can at least see if that part looks correct.  If you just have too much data, it may have to be split into pieces.

 Anyway, change the end of your formula to:

if    Val (CurrentB) - 1 <> val (PriorB) or CurrentA <> PriorA //This line now does two separate tests
then
  if Length (Output) > 65465 then
    Output := current
  else
    Output := Output + ' to ' + prior  + CHR(13) +  current;
output


 All that does is check the length of Output, and if it gets close to the limit, it replaces it with the current field value, erasing all of the values that the formula had stored up to that point.  I think that should keep you from getting the error, but it's doing it by dropping some of the values, so it's not a solution.  The idea is to just get a look at some of the "problem" data.

 James

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
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
datatechcorpAuthor Commented:
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...costs...etc.).

JAMES...I'll try your suggestion shortly, and provide you feedback.

Thanks a bunch to both of you!...Mark
datatechcorpAuthor Commented:
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
James0628Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.