Acquiring Data on a Row

[Trebel]

I am brand new to CR. Using V-8 for Peachtree. I have my report partially complete but it won't pick up certain data in a row. IE:
My report is grouped by Sales rep. It pulls the customer, transaction #, trans date for a specified time period. However, no matter what I do it will not pull the Inventory ItemID's for that transaction. I have tried formulas, and select reporter. I feel I must need some additional formula but am at a loss as to what it might be.
Can anyone help...since I then I have to pull the sale amount and COG, profit and commission from this ItemID.
Thanks for any info you can give.

 

[kenhamady]

What are the tables and fields used in the report.
How are they linked? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[Trebel]

Tables are-Customer,Journal Header, Journal Row, Line Item and Cost Code. The JrnHdr and JrnRow are linked by Post Order. JrnHdr, CostCode and LineItem are linked by description. The JrnRow and Customer are linked by Record #. I let the Wizard make my selections.
I have tried other links but they don't seem to change anything.

 

[kenhamady]

The "Smart linking" feature of CR is not usually effective in real life, especially with this many tables. You need to find out from PeachTree how these tables should be linked (and IF they can all be linked). You shouldn't be linking at all by description.

I believe that all of your arrows should go directly from JournalRow to the other tables. Your first link is correct to Journal header but you should delete all of the others. The link to LineItem is ItemRecordNumber. The link to Customer is Customer Record Number. CostCode probably links to CostCode ID in Journal Row. Try these and let me know if that helps. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[Trebel]

Thanks Ken. Yes, changing the links has now enabled me to bring up the Item ID. But...for some reason it is dropping it 5 rows down leaving large white space and if there are multiple Item ID's on the order it drops down rows on each one of them. I have done the supress on all fields including the Item ID but it never starts on the row of the detail section with or without the supress anyway. What would cause this. I am just grateful to finally have my Items listed at last tho. Thanks for your help.

 

[kenhamady]

Suppressing fields doesn't change the structure of the report. It actually makes it harder to see why something is happening. So, take off the suppress on the other fields, and describe what you are seeing in more detail.

Make sure you detail section (in design) is only as deep as one field and that all your field objects are on the detail section. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[Trebel]

I have taken the suppress off and all fields are in the same detail section.

For every new customer this is what I get......
SalesRep Cust. Inv# Inv.Date
This is repeated 4 rows and on the 5th row I get
SalesRep Cust. Inv# Inv. Date AND Item ID
The above 5th row is then repeated 3 times.
If there is more than one Item ID it repeats the row 3 times for each Item ID.
Then the last row for the customer is the same as the beginning 4 rows....all the info except the Item ID.

This sequence is followed for every new customer.
I must have some formatting or formula that is causing this.
Thanks for the help.

 

[kenhamady]

No, this sounds like what I call "Table Inflation" caused by an unexpected one to many relationship in a link. It may be that one of the links is bad. If you add a rule that the ItemID has to be greater than blank, does that eliminate the bad/duplicate records? If this works, verify that you are getting all of the records that you need. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[Trebel]

Putting in the "greater than" 0.00 did change the report. It eliminated the 4 rows without the Item ID at the beginning of a new customer and eliminated the last row without and Item ID. However, it is still putting in each row 3 times with the Item ID for each Item ID. If I do a suppress it then just leaves 2 blank spaces for where the additional rows were originally. Hope this is making sense to you.

 

[Trebel]

Fixed it Ken. I am back in business again. It is report just as it ought to for the time being.:.)

 

[kenhamady]

What did the trick? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)