Share cost of one line item to other line Items 1

[jonbarr]

CR vers 8.00
SQL database
I need to calculate the margin on each line item of an invoice. Problem is, I need to include in the Item Cost a share of the cost for the pallet the Item is sitting on. We use over 20 different types of pallet (i.e. 20 different costs ), but only one type per invoice. Different weights may be placed on these pallets. e.g.
Line Item 1 6.0 tonnes (5 x 1.2 tonne pallets)
Line Item 2 4.8 tonnes (4 x 1.2 tonne pallets)
Line Item 3 2.0 tonnes (2 x 1.0 tonne pallets)
Line Item 4 11 pallets

I have calculated the share of the total pallet cost per item as
Line Item 1 6.0/12.8 x 11 = 5.17 = 5
Line Item 2 4.8/12.8 x 11 = 4.13 = 4
Line Item 3 2.0/12.8 x 11 = 1.72 = 2

Now I need to identify the cost of the particular pallet used in Line 4 and then add a cost equal to the number of pallets used per line x the pallet cost.

I've tried several formulae to isolate the cost based on "if the Line Item = "pallet" then "Cost", but of course as soon as I use this on a Line Item which is NOT a pallet, the result is zero.
I've tried grouping into "Pallet" and "Other" items, but still have the same problem. The Pallet Cost formula will not work on any line which is not a pallet.
Report is also grouping by Customer, and Invoice number.

Perhaps variables are the answer, but I have not had much experience with them.
Any suggestions would be warmly welcomed.

 

[Madawc]

as soon as I use this on a Line Item which is NOT a pallet, the result is zero

I don't follow. Isn't that what you want - no pallet cost added where there is no pallet?

Or are you multiplying? If so, I'd assume you should add "else 1" to your formula.


Madawc Williams (East Anglia, UK)

 

[lbass]

Can you please clarify the names of your fields? Do you have a field like {table.lineitem} and then a separate field that contains tonnes and the number of pallets? Or is the field containing tonnes different from the one containing the number of pallets? Can you also please show the formula you are trying to use with appropriate field names?

-LB

 

[jonbarr]

Thanks for the feedback, and sorry for not explaining enough, I'll try and do better.
On a given invoice we have maybe four lines, three for the goods we are selling, and the fourth for the pallets.
Cost and selling price for the three items are different, so the margin (profit) is different.
In calculating the margin, we need to take into account the cost of the pallet the goods are supplied on.
We have several pallet types, so the cost of the pallet needs to come from the type of pallet identified on the fourth line, i.e. relevant to that particular invoice.
So we need to identify the particular pallet type used for this sale, identify the cost of that pallet type, and then add the cost of the pallets used for each line to the cost of the item we are selling, before calculating the margin as
Item selling price - (Item cost plus pallet cost ).
This needs to be done on a line by line basis.

lbass, the fields I am using are
{table.lineitem}
{table.description}
{table.unit}
{table.shippingweight}
{table.quantity}
{table.cost}
{table.price}
We have different units of 'each' and 'tonne', so I convert the quantity to tonnes with a formula and calculate the number of pallets used for each line based on this and a formula to isolate the quantity of pallets for the invoice.
Sum ({@QuantityPallets}, {SOP30200.SOPNUMBE}) * ({@QuantityTonnes} / Sum ({@QuantityTonnes}, {SOP30200.SOPNUMBE}))
This formula gives me the share of the total pallet cost for each line of item sold, but my problem is how to multiply this share by the pallet cost.
The pallet cost is in line 4 and I need to use it on lines 1,2, and 3.
The cost of the pallet is easily identified on line 4 with
"if {table.description} = "pallet" then {table.cost} else 0" , but as soon as I use this formula on lines 1,2, or 3, it returns 0 as the description for those lines is of course not "pallet".
Seems a very simple problem, but it has me stumped !

 

[lbass]

I still can't translate the fields back to the quantity of pallets versus tonnes. Please show the content of your nested formulas {@QuantityPallets} and {@QuantityTonnes}. Also, what other values can the description field hold?

-LB

 

[jonbarr]

{@Quantity} is used to deal with invoices and credits
"if {SOP30300.SOPTYPE} = 3 then {SOP30300.QUANTITY}
else -({SOP30300.QUANTITY})" // 3 = invoice

{@QuantityPallets} is
"if {IV00101.ITMGEDSC} = "PALLET" then {@Quantity} else 0"
The description I am using is a generic description, so all pallets have a different Item # but the same generic description of "pallet".

{@QuantityTonnes} is
"if {IV00101.ITMGEDSC} <> "PALLET" then
{@Quantity} * {IV00101.ITEMSHWT}/100/1000
)else 0

 

[lbass]

I'm sorry it took so long to get to this point, because the answer is fairly simple. To use the pallet cost in a formula in earlier detail lines, use:

maximum({@palletcost},{SOP30200.SOPNUMBE})

This assumes that {@palletcost} is:

if {IV00101.ITMGEDSC} = "PALLET" then {table.palletcost} else 0

Once you have your formulas working correctly, if you want to summarize at the group levels, you will need to use variables. Let's assume that you want to summarize at the Customer, Invoice, and Grand Total level. You would create these formulas:

//{@resetcust} to be placed in the customer group header:
whileprintingrecords;
numbervar custamt;
if not inrepeatedgroupheader then
custamt := 0;

//{@resetinv} to be placed in the invoice number GH:
whileprintingrecords;
numbervar invamt;
if not inrepeatedgroupheader then
invamt := 0;

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar custamt := custamt + {@yourfinalcostperlineitem};
numbervar invamt := invamt + {@yourfinalcostperlineitem};
numbervar grtot := grtot + {@yourfinalcostperlineitem};

//{@displinvamt} to be placed in the invoice group footer:
whileprintingrecords;
numbervar invamt;

//{@displcustamt} to be placed in the customer group footer:
whileprintingrecords;
numbervar custamt;

//{@displgrtot} to be placed in the report footer:
whileprintingrecords;
numbervar grtot;

-LB

 

[jonbarr]

Of course, I knew it would be a simple answer, once I had it . The use of "maximum" is obvious now !!!

Thank you very much lbass, and for including the formulas for variables as well. I will try to use them and learn more about variables in the process.