Crystal Reports 8 - Linked subreport throwing 534 error at runtime.

Hello and thanks for reading.

I am adding a memo field into an existing report.  I'm using .dbf files from Epicor.  The memo is linked as follows:

1.  Get the ODBC POHeader table so I can retrieve the VendorNum, which doesn't come over on the .dbf from Epicor.
DBF.POHeader.Company -> ODBC.POHeader.Company
DBF.POHeader.PONum-> ODBC.POHeader.PONum

2.  Get the Memo that's linked via field Key1 with the ODBC POHeader field, VendorNum.
ODBC.POHeader.Company -> Memo.Company
ODBC.POHeader.VendorNum -> Memo.Key1

The .dbf converts everything to text.  Therefore, when I'm trying to link DBF.POHeader.PONum-> ODBC.POHeader.PONum, I have to create a formula that converts DBF.POHeader.PONum to an integer with ToNumber.

Additionally, I have to create a formula that converts ODBC.POHeader.VendorNum to Text, so it can be linked with Memo.Key1, which is a text field.

How can I spit out a subreport that does all this linking, with all these conversions?  I tried putting all the fields into one subreport and I get the 534 error.  I assume it's because I'm trying to link the Memo.Key1 field with a formula for the ODBC.POHeader table that hasn't happened yet because it's happening in the same subreport?  

Here are the Subreport links I have in place, that are throwing the error:

@VendorNumToChar -> Memo.Key1
ODBC.POHeader.Company -> Memo.Company
DBF.POHeader.Company -> ODBC.POHeader.Company
@PONumToInteger -> ODBC.POHeader.PONum

So confused.  Help please.  :(
Who is Participating?
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.

Can you upload the rpt file?

WHen do you get the error?

This doesn't really address your problem, but ...

 Forgive the obvious, but if you have access to ODBC.POHeader, and it includes data (VendorNum) that is not in DBF.POHeader, why use DBF.POHeader in the first place?  Why not skip DBF.POHeader and just go straight to ODBC.POHeader?

 I imagine there's some reason for that, but thought it was worth checking.

 As for your error, when you referred to "Subreport links", are you talking about the links from the main report to the subreport, or links between the tables inside the subreport?

 You talked about using DBF.POHeader to read ODBC.POHeader, and then using ODBC.POHeader to read Memo, and using formulas to convert some fields in order to make those "links", but I'm not sure where all of that is happening -- In the main report, or the subreport, or both.


 Oh, yeah.  When you refer to "Crystal Reports 8", are you talking about CR 2008, or a really old version (from before CR 8.5, CR 9, etc.).
MTaftAuthor Commented:
mlmcc, attached is the report.  

James, I'm using Crystal Reports 8.5.  I can't drop using the .dbf because it's an integrated legacy report.  It's a Purchase Order report native to Epicor and I don't have time to reinvent the wheel, unfortunately.  That would definitely make things easier.

Correct, subreport links refer to the links from the main report to the subreport.  I'm sorry for the confusing language.  When I say "The .dbf converts everything to text.  Therefore, when I'm trying to link DBF.POHeader.PONum-> ODBC.POHeader.PONum...", I'm referring to the keys I'm using to retrieve records from one table to another.

Maybe looking at the report I've uploaded will help make sense of it.  

Thanks so much to both of you.

Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

MTaftAuthor Commented:
I see a few subreports.  I'm assuming that the one in question is "Vendor-Specific PO Memos" ?

 I really don't know if this is significant, but the subreport record selection formula doesn't include a test on Memo.Key1.  You have @VendNumChar linked to Memo.Key1 in the subreport links, but Memo.Key1 is not included in the record selection formula.  That may be normal, but the other tests (eg. on POHeader.PONum) _are_ included, so I'm not sure.  It may not change anything, but I'd add the test on Memo.Key1, for consistency, if nothing else (since the rest of the tests are there):

and {Memo.Key1} = {?Pm-@VendNumChar}

 Also, what, exactly, does the error say?  You just said that it's a "534 error", but that means nothing to me.

MTaftAuthor Commented:
Yes, the Vendor-Specific PO Memos is the subreport that I've added.  The PO_ODBC was just a test subreport.  The Vendor_Contact subreport was existing.

I added the test you recommended, and I still got the error, "Error 534".  They used an extreme economy of words in this error.
MTaftAuthor Commented:
So I simplified the subreport this time.  These are the steps I took.

1)  I removed the Memo table and deleted the Memo fields from the report.  I only left the PO fields.  I removed all Subreport Links for the Memo table.  

Result:  Report worked.

2)  I added the Memo table back in.  I did not add any Subreport Links back for it.  In the Record Selection code, I did not include the Memo.Key1 = {?Pm-@VendNumChar}.  I simply added the fields below:

{POHeader.Company} = {?Pm-poheader.COMPANYID}
{POHeader.PONum} = {?Pm-@NumPONUM}
{Memo.Company} = {?Pm-poheader.COMPANYID}
{Memo.RelatedToFile} = 'Vendor'
{Memo.CategoryID} = 'PO'

Result:  Error 534

Does this help pinpoint the problem?  What am I missing?  It seems so simple...
FWIW, I did a little searching online for that error, but didn't see anything that seemed specifically relevant.  Most of the references included a message, like "Error detected by database DLL".  One cause was apparently some change to the db, like the name changed.  You could try opening the subreport (not the main report, but the subreport) and doing a "Verify Database", just for the heck of it.

 Have you used that Memo table in any other CR reports?  If not, maybe CR has some kind of problem accessing it.

 Either way, you could try creating a new stand-alone report (not a subreport) that reads Memo (only Memo) and see if that works.

 If a new report with just Memo works, try adding POHeader and linking it to Memo, like in your subreport, and see if that works.

 And if that works, you could try importing that as a new subreport in your main report (assuming that CR 8.5 has that option), and see if that works.  There is a remote chance that your old subreport is "corrupt" in some way.

MTaftAuthor Commented:
Good morning James.  After MUCH digging, this is a problem specific to Memo fields within Crystal Reports.  I've submitted a ticket to Epicor's support line to get help with why their memo fields won't display.  Thank you for your time and suggestions.  I will post here again when I have received an answer from them, so someone else can get use out of this thread.
CR 8.5 is limited to strings of 255 characters or less.  Memo field usually will display but sometimes they run into the length issue.

Is the field set to grow so it can handle data of any length?

Is this a new issue with the report that may have been caused by as Windows update, printer driver update, etc.
Is this a new report or new database field?

Have you tried doing a VERIFY DATABASE (under the database menu)?

If this field has worked before, any changes to the database driver?

What is the full message?

After MUCH digging, this is a problem specific to Memo fields within Crystal Reports.
I was wondering if you might be looking at "memo" fields.  I think the Memo table name kept bringing it to mind, but, of course, that didn't mean that the fields were actually "memo".

 IAC, CR still had some issues with memo fields in CR 10.

 Here's one basic thing you could try:
 Create a new formula (call it whatever you like) in the subreport and just put the Memo.Key1 field in it.
 Create a new string parameter (call it whatever you like) in the subreport.
 Remove the subreport link from @VendNumChar to Memo.Key1, and link @VendNumChar to the new parameter instead.
 Then go into the record selection formula in the subreport and add:

and {@new formula created above} = {?new parameter created above}

 It's a long shot, but I found that in CR 10, I could get around some limitation on memo fields by simply putting the field in a formula.  It was like putting the field in a formula implicitly converted the "memo" field to "string".

 Of course all of that is assuming that the link to Memo.Key1 is causing the error.  That just seems more likely to cause an error than simply trying to display a field on the report.  But you could try the same thing for any "memo" fields in the Memo table that are displayed on the report (create a formula that contains the field and put that formula on the report instead).

If you are trying to link on a memo field in CR8.5 it won't work.  CR8.5 is limited to 255 characters in a string.  Memo fields cannot be used in formulas except to test for NULL and I believe to extract the first 255 characters.


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
MTaftAuthor Commented:
Well that is exactly correct mlmcc.  I gave up.  I'm going outside of Crystal Reports and just writing what I need directly out of Progress.
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.