SageForum for UK versions of Sage 50- Help and support.

Hi Bruce,

Ive managed to add a Weight of the entire invoice via IF.

However, wish to break that weight down via webcateg1. So if web cat1 = UN 1866 then tell me the weight for those items only on said invoice.

Ive tried combining if and sumif with no joy. So then tried to return the weight for UN 1866 when Uom is "KG" (as a separate expression). Then another expression for when UoM in "EA"& then was going to add together....but cant get the 1866 KG expression to return correctly anyway (only returns correct if last inv item line is UN 1866 & UoM is "kg").
If

Uom= "kg"?Ivitem qty
Uom= "ea"?Ivitem qty*unitweight

Any help much appreciated.

Can you please clarify

via IF ??

Categories are best used via the cat number rather than name, as names can change but the numbers remain.

the principle is pretty simple.

You have an expression in the detail section that calculates the weight this is then totalled to give you the total weight.

If you want to know for Category 1, then add another expression at the detail level that is

CATAGORY_NUMBER=1?weigth times quantity:0

So if the category is not 1 it has a value of 0, now add that up in the footer and you have it.

Hi Bruce,

Apologies I'm so late getting back to you I did have a draft reply from the day after you responded but not had time revisit!

Thanks to your help I got it working

Your completely right about the cat no. rather than name....be handy if Sage had sub categs.

I still really struggle with how the sections work the sage literature for it is cr*p.

I'm trying to achieve the same thing (weights of diff item types) however instead of via invoice I'm trying to do it by Kit product. So we have set up roofing kit products and I want to add up the BoM via prod sub component type for each kit.

So I have tried to make a report that only looks at the roofing kit prods, export to excel as single line for each kit. Then want to export to excel and filter
on certain criteria (e.g. kit size) so I can quickly check kits of the same size have the same qty for certain prods etc. I've imported 360 kits with over 2000 indiv item lines, no way can check the import manually so want this report so can filter on kit size as a control.

Its key that each kit has all relevant info on my report as a single line.

I thought I'd done it and it worked when ran for one prod/kit but when ran for multiple its adding all the successive kit item weights.

Hi Bruce,

I've just tried calling you (left a voice mail) it's regarding my last post here. Just so you can make sense of my rambling voice mail .

Might be easier just going off the excels at first.


Columns F:O are summing certain component weights. Report works correctly when ran on individual SKU but not when ran on a range of prods, its summing all the indiv columns as goes along.
Format is perfect, as can filter on Kit type (Col C:E) which are my kit variables....

....then after filtering on variables I can then filter on F:O to ensure all the same quantitys in each column per similar variable combos (C:E).

As I have 1000's of item lines across 100's of kits this is the quickest way of checking I have imported all these kit lines correctly per each kit type.

Any additional rules will be too complicated as there's diff *nuances. The variables, are'nt just "Size" & " Glass".

*CU Brand will have diff ratios of TC but same for everything else.
*TR Brands will have diff qauntities for all other Brands as they have smaller pack sizes for resin, topcoat & catalyst. But the same for CSM.

Thanks
Gareth

Apologies, I had given it a lot of attempts before asking for help orig!...but think Ive cracked it (in wrong section and needs to have a reset section. Juts dont get tehse parts of report designer need to re-red the material instead of guessing stuff. Thanks