this might be a formula issue not sure

[luv2bike2nv]

I am using Crystal Reports 8.5

I am creating a report that will eventually show inventory and non-inventory purchases with the quantity and cost that a certain buyer buys.

The report is pretty simply (or at least I think it should be)
4 DB's (order master, notes, purchase order code, and part master)

The DB’s are linked as follows:
Purchase Order code links to Order Master (equal join) with ORDNUM fields
Order Master is linked to Part Master (equal join) with PRTNUM fields
Order Master is linked to Notes (equal join) with Order Master ORDER to Notes KEY fields (no error messages when I exited out of the visual linking expert) They are both String type.

The fields I use:
Order Master are: ordnum, orgqty, cost and Buyer
Purchase order code: XODE (inventory/non-inventory)
Notes: Notes (non-inventory)
Part Master: PMDES01 (inventory)

I have 3 formulas:
@PartNum:
stringVar XODE:={Purchase_Order_Code.XODE} [1 to 2] ;
if XODE ="NI" then
"NON-INV PART"
else
{Order_Master.PRTNUM}

@Description:
stringVar XODE:= {Purchase_Order_Code.XODE_16}[1 to 2];
if XODE = "NI" then
{MAX_Notes.NOTES_60}[1 to 50]
else
{Part_Master.PMDES1_01}

@Cost:
{Order_Master.COST_10} * {Order_Master.ORGQTY_10}


I have one Select Statement:
Order Master:Buyer is equal to ?{Buyer ID]

Design mode:

Details : Ordnum @PartNum @Description Orgqty @cost Buyer

The Problem I am having is as follows:

If I just have on the details line : Ordnum @Partnum Orgqty @cost Buyer
The report will be work. however i need a description of what is ordered. I would put in Buyer ID 13 (because Buyer ID 13 has about 10 orders that are either inventory or non inventory) i have attached a word doc with print screens of design and preview modes w/ and w/out the @description.

When I add the @description to the details line and run the report, nothing will show up!

I am not sure why this is happening.
With what I have provided here, can someone help me? If more information is needed, ask and I will provide it.

Thank you Very Much In Advance!
Robin

 

[lbass]

What's the content of your description formula? Can the description field be null? If so, you need to use a left join TO the table containing the desription field.

If the field is always populated, you should check to make sure the fields you are linking on match in the two tables, by browsing the fields in the linking expert.

-LB

 

[luv2bike2nv]

Thank you for your response LB.

If I understand your question:
The description formula is the description of the product that was purchase.

@Description:
stringVar XODE:= {Purchase_Order_Code.XODE_16}[1 to 2];
if XODE = "NI" then
{MAX_Notes.NOTES_60}[1 to 50]
else
{Part_Master.PMDES1_01}

they are both Strings however PMDES01 length is 25 and the Notes length is 240. the other links in linking expert match up to each other -- String, length and field names.

Every line item that comes up will have a description either from Part Master or from Notes DBs.

if it is a non-inventory product it gets pulled from the Notes DB. if it an inventory product it gets pulled from the Part Master DB.

I don't know if this help out in order to help me out.

Thanks again.
Robin

 

[lbass]

In the database expert, use left joins FROM Order Master TO PartMaster and TO Notes.

Then change your description formula to:

if {Purchase_Order_Code.XODE_16}[1 to 2] = "NI" and
not isnull({MAX_Notes.NOTES_60}) then
{MAX_Notes.NOTES_60}[1 to 50] else
if not isnull({Part_Master.PMDES1_01}) then
{Part_Master.PMDES1_01}

-LB

 

[luv2bike2nv]

Thank You LB.

That solved the issue! I really appreciate your help.

Robin