Here’s what I’m trying to accomplish.
Two tables:
Sales Order Header – with basic top-level info re. the sales order – customer, date, sales order number, etc.
Sales Order Line – with the individual line by line info regarding what we sold.
These are linked via unique sales order number.
Sales Order #1
$10 freight for the entire sales order, specified at the header level, not at the line level.
Inventory Item A – sold 3 at $20 each = $60 on the first line of the sales order line table.
Inventory Item B – sold 1 at $40 each = $40 on the second line of the sales order line table.
Inventory Item C – sold 14 at $1 each = $14 on the third line of the sales order line table.
Here’s my issue. I need to report the total merchandise sold - $60+$40+$14 = $114 and add to it the total freight for the order for a total sale of $124.
I’ve tried putting the freight on the detail line (sales order line level), but it adds $10 for EACH line, and total freight ends up being $30, which is wrong.
I’ve also tried grouping the items by sales order number and inserting the freight field in the group header or group footer, but then when I grand total the freight field, it still adds freight in multiple times.
So I came up with a brilliant scheme of creating a formula field that calculates what percent of the total merchandise is represented by each line item and multiplying that percent times the total freight to get freight per line item. So line 1 gets (20/114)*10, line 2 gets (40/114)*10, and line 3 gets (14/114)*10. So the entire $10 of freight gets allocated at the line item level. Well that was a nice idea until I discovered that they have some invoices that are freight only, which would be okay if they didn’t have any line items, but they also have line items for non-stock items at 0 quantity and 0 cost. So if all three line items are 0, my formula no longer works.
So I’m stuck. I need a total freight by order, by customer, and a grand total. Any creative ideas?
Thanks in advance for the help!
Sherry Whitten
swhitten@idrivellc.com
Two tables:
Sales Order Header – with basic top-level info re. the sales order – customer, date, sales order number, etc.
Sales Order Line – with the individual line by line info regarding what we sold.
These are linked via unique sales order number.
Sales Order #1
$10 freight for the entire sales order, specified at the header level, not at the line level.
Inventory Item A – sold 3 at $20 each = $60 on the first line of the sales order line table.
Inventory Item B – sold 1 at $40 each = $40 on the second line of the sales order line table.
Inventory Item C – sold 14 at $1 each = $14 on the third line of the sales order line table.
Here’s my issue. I need to report the total merchandise sold - $60+$40+$14 = $114 and add to it the total freight for the order for a total sale of $124.
I’ve tried putting the freight on the detail line (sales order line level), but it adds $10 for EACH line, and total freight ends up being $30, which is wrong.
I’ve also tried grouping the items by sales order number and inserting the freight field in the group header or group footer, but then when I grand total the freight field, it still adds freight in multiple times.
So I came up with a brilliant scheme of creating a formula field that calculates what percent of the total merchandise is represented by each line item and multiplying that percent times the total freight to get freight per line item. So line 1 gets (20/114)*10, line 2 gets (40/114)*10, and line 3 gets (14/114)*10. So the entire $10 of freight gets allocated at the line item level. Well that was a nice idea until I discovered that they have some invoices that are freight only, which would be okay if they didn’t have any line items, but they also have line items for non-stock items at 0 quantity and 0 cost. So if all three line items are 0, my formula no longer works.
So I’m stuck. I need a total freight by order, by customer, and a grand total. Any creative ideas?
Thanks in advance for the help!
Sherry Whitten
swhitten@idrivellc.com