I am new to Crystal Reports.  I am using Crystal Reports XI R2.

I am editing an existing report and need to add an alert if certain conditions are met.

The report is a work order and some customers have given us specific instructions regarding a particular part they order from us.  If the customer orders that part, I want an alert to print on the work order so the shop sees it.

The issue I am running into is that the database was designed oddly.  If a customer has given us specific instructions and we have logged those instructions, then the Part Number and the Customer ID are both in ONE field.

The field is the 15-character alphanumeric Part Number, 15 spaces, the 7-character alphanumeric Customer ID, and finally 13 spaces.

I first tried adding a formula to the report and ended up with 4562 pages, instead of 2.  I discovered I need to put the formula in a sub-report.  I did that and added subreport links.  I still end up with 4562 pages and still do not have the alert printed out.

I need a formula in the sub-report that will look at this one field and see if both the Part Number and Customer ID are in it.  And if both are in the field to add the alert to the main report.

Here is what I have that is not currently working:
if {Standard_Notes.NOTTYP_46}="SV"
and {Standard_Notes.TYPDAT_46} startswith {Part_Master_Ext.PRTNUM_01}
and {Standard_Notes.TYPDAT_46} like {Customer_Master.CUSTID_23}
then "*SEE CUSTOMER PART INSTRUCTIONS"

Any help I can get will be greatly appreciated.

Sastry is giving you a nice option for your formula but , just a guess, I think you are having a join issue more than a formula isue and your are trying to use the formula as a way to handle a join. The fact that you jump from 2 to 4500 pages is a good indicater that your join (I am guessing a cross join) was enforced when you added the formula. Consider using a formula in the main report as a concatenated string to link the main report to the sub report.

@DBlank, Thanks for the Join tip.  While I am uncertain what a Join is (I was more or less tossed into editing this report having never used Crystal Reports before) I do have a Complete Reference I have been using and will read up on Joins.

The report was pulling information from an old database we are trying to eliminate.  The database is on an old Win2K server.  The data has been replicated in our new db, but when the new db was setup no one thought to create the field names exactly as they were in the old db until it was too late to change the field names.  I have added the new db tables to the report and created the proper links.  I have also edited the existing formulas.  But everything I have accomplished on this report has been through trial and error; seeing how the data was pulled from the old db and changing/creating formulas to extract the data from the new db.

Unfortunately, the existing formula for the alert cannot be changed to work because of the way the customer instructions are stored.  In the old db, the instructions were stored in one field and  the Part Number and Client ID were each stored in their own field.  In the new db each line of the instructions is stored in its own field and the Part Number and Client ID are combined into one field, replicated for each line of instructions.  So, if the customer instructions are 5 lines worth of text, I have a table that looks like this:

NOTTYP_46             TYPDAT_46               NOTNUM_46           NOTE_46
     SV            WIDG571(15)ABCDE01(13)        0001           Each widget
     SV            WIDG571(15)ABCDE01(13)        0002           must be placed
     SV            WIDG571(15)ABCDE01(13)        0003           on its own skid
     SV            WIDG571(15)ABCDE01(13)        0004           and shrink
     SV            WIDG571(15)ABCDE01(13)        0005           wrapped.

For the sake of space above, I changed:
the fifteen spaces following the part number to (15)
the thirteen spaces following the client ID to (13)

Obviously, each line of instruction is much longer, but this demonstrates how the instructions are stored.

I also need to extract the instructions themselves, but figured once I got the formula to insert the alert, I would be able to use some of the same formula/logic to print the instructions on the report.

I know it's difficult to write a formula not knowing all the specifics of the report and databases and how they're linked, but I figured it wouldn't hurt to ask someone more knowledgeable than myself.

*Edited to correct the note numbers under NOTNUM_46.


Edited by GrisCorp - 19 Mar 2013 at 4:29am

I have completely removed the old database table from the report.  I have also removed all formulas referencing the old db.  The report needs to pull all data from the new db tables only.

The instructions are in the Standard_Notes table.  I dragged a field from the table to a Details section of the Main report.  When I view it in my Report Viewer, it  resulted in over 4800 pages.  I deleted the field then dragged it into the page header.  Same issue.

I have removed the Standard_Notes table from the Database Expert selected tables.  I am unsure of what you mean by making a formula, in the main report, that creates the string in the special instructions table.  Without the table selected in Database Expert, I cannot create any formulas referencing it.

@DBlank,

Thanks so much for your assistance.  I still was unable to get my report to work the way we wanted, so my supervisor authorized our contact at Exact Software to take a look at the report since it is going to be used in our Exact MAX program.  He got the subreport to work perfectly.  It was an issue with my subreport links.  He found the data to which I needed to link in the subreport was in a different table altogether than the one I was trying to use.


Edited by GrisCorp - 25 Mar 2013 at 5:55am