Peachtree sales report

[XavierMac]

Hello,

I saw this message in another thread but didn't view an answer. It pretty much describes what I'm going after.

"How do you get the cost for an item sold in Crystal Reports v9?

I have tried:

if {jrnlrow.rowtype} = 1 then GetPeachRowAmountv2

but it only returns 0.00's for every item sold.

I have a record selection:
{jrnlrow.journal} = 3

3 = the sales journal

columns on my report include:

customer
invoice #
date
item
sales amount
cost of goods sold
gross profit

I am using the following tables:
company
chart
customers
jrnlhdr
jrnlrow
lineitem"

I've already gotten most of the fields, I'm missing two that I just can't seem to retrieve the data from. In peachtree they're the Item ID and the unit cost fields. I've entered the Item ID field in the design section of crystal reports but the field just comes out empty along with all the other data. If I remove Item ID, the rest of the data shows up as I would like. I'm also looking for the unit cost field which I can't seem to find. Is there anyone who could help me? I'm very new to crystal reports. I've only been working on it for two weeks. I pretty much don't know too much about formulas. Any help would be greatly appreciated. I've posted another thread in the peachtree forum but it seems there a lot more people in this one. Thanks!

 

[lbass]

This sounds like it might be a table linking issue, but I can't tell what table the Item ID is from, and you haven't told us how you have linked the tables and with what kind of joins. There is no point in referring to columns without telling us what table they are from.

-LB

 

[dgillz]

You should ask this on the Peachtree forum since this is database specific.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[XavierMac]

I've been able to progress a little since my last post. I've gotten the unit cost field into the report. I've posted on both the Crystal reports forum and the Peachtree forum. There doesn't seem to be too much traffic in the Peachtree forum, hence I posted on both. I guess my biggest problem would be to retrieve data from the LineItem table. ItemID is under the LineItem table but I can't seem to link that table to the report. I've only linked the tables by retrieving data using the database expert. All the data is on the server so it's pretty simple stuff. I'm very new at this so let me know if there's something I need to add. BTW, the tables are listed at the bottom of the post.

 

[XavierMac]

Okay. One more question that may be more fitting to this forum. Is there any way you can convert a data field from a number to a string? For example, a field under the JrnlRow table is set as a number - ItemRecordNumber : Number. I'm thinking if there's a possible way I could convert this to a string it may work. I'm thinking the ItemID isn't coming up on the report because the LineItem table isn't actually tied with the transaction(it is a sales report). I'm about 90% done with the report..I just need that ItemID field!!! Any help anyone?

 

[lbass]

You did not explain how the tables are linked to each other in database->database expert->linking tab. Posting what the tables are doesn't really help. When you reference fields, please use the convention {table.field}. In describing linking, you would explain that tables were linked From one table To another, on what fields, using what kind of join (equal, left outer, etc.).

-LB

 

[XavierMac]

LB,

Thank you for your input. I've done more researching and found out that I didn't have the right link between the JrnlRow table and the LineItem table. I've linked them with the ItemRecordNumber Field(equal). You seem very knowledgeable about Crystal Reports with Peachtree, I do have one more question. I'm trying to find the freight charges that are located in each transaction. We actually have a column for it in each invoice. The following is what I got from Peachtree and I've followed instructions but I can't come up with anything. Here's what it says..

"
RowType
Integer

Identifies what kind of row this is. Valid values include:

0 => JrnlRowType_Normal (A general-type journal transaction row.)

1 => JrnlRowType_CGS (The cost of sales row. This row is generally not seen in the program.)

2 => JrnlRowType_InvChg (The opposite side of the cost of sales row above.)

3 => JrnlRowType_JobOnly

4 => JrnlRowType_Discount (This row may be present in a receipt or a payment. The row amount represents the discount amount applied from this check to the related invoice.)

5 => JrnlRowType_SalesTax (The row associated with sales tax in sales or receipt transactions.)

6 => JrnlRowType_Freight (The freight amount calculated for sales transaction.

7 => JrnlRowType_BelowZero
_COGS => (A system-generated cost of sales row used in inventory costing. This is not directly related to any particular header.)

8 => JrnlRowType_BelowZero
_InvChg (A system-generated row inventory adjustment row used in inventory costing. This is not directly related to any particular header.)

9 => SortJob_AssemblyUnbuild
_COGS (A system-generated cost of sales row used in inventory costing when building and unbuilding assemblies. This is not directly related to any particular header.)

10 => SortJob_AssemblyUnbuild_
InvChg (A system-generated inventory adjustment row used in inventory costing when building and unbuilding assemblies. This is not directly related to any particular header.)
"

So I've went into the record expert and made the formula of
{JrnlRow.RowType} = 6
and then enter the {JrnlRow.RowType} field into the report hoping that I may find the freight charges to show up. Then when I click the preview tab..once again the report becomes blank. What am I doing wrong here? I really appreciate all your input LB.

 

[lbass]

You shouldn't be selecting by the rowtype, since this isn't the only rowtype value you want in the report. Instead, go into the field explorer->formula->new and enter:

if {JrnlRow.RowType} = 6 then {JrnlRow.JrnlRowType_Freight} //just a guess--this should be the field that holds the freight amount

I'm not really familiar with PeachTree, so I can't really tell you for sure what value field should be used.

-LB