Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals

IF statement help

Report design issues and solutions.
User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: IF statement help

Post by brucedenney » 25 Nov 2019, 11:03

You are going to have issues going down the road as you add new products and they are not in the exceptions and so forth. The expression is confusing even if it works.

The way to deal with this is to simplify, make a generic solution.

I think you need to go back to basics and start thinking about why you are doing things in this way?

Why are you using KG as the Qty?

Why not sell it by the 20kg pack? record how much you have in stock as a qty of 20kg packs rather than a number of kilos of product?

If people want to buy by the Kg, then I suggest you create a separate product for that with its own separate price, selling Kgs costs you more, you have to measure (and there is always a little loss in measuring) I think a premium should be applied to the price when buying by the Kg to cover the costs, or you could see selling whole packs as easier and so give a discount for whole packs, it is the same thing, just looked at a different way.

For example If you wanted 30kg of topcoat, you could buy 1 x 20kg pack/roll/tub/drum at £54 and 10 x 1Kg topcoat @ 2.80 per kg.

You can use a stock transfer to convert a single 20kg product into 20 x 1 kgs. The stock level of single KG product tells you that there should be that many KG left over somewhere and you shouldn't transfer/split a new pack until those are used up.

The issue of people entering the wrong quantities now goes away, if you want show a price per kg on the items then it is a simple expression unit_price divided by weight.

Over time you can work out how much stock is sold as spilt packs, and see if the amount of time and hassle spent splitting packs is being recovered. You could even use that sort of comparison to determine what the premium for split packs should be because you would be able to quantify how much stock is sold as split packs. You will be able to measure the losses in measuring single KGs and be better able to work out the extra you need to charge to cover those losses properly.

Are people coming to you to buy the split packs and buying the bulk of whole packs somewhere else? What is the ratio of split packs to whole packs by customer, have you got some customers buying wildly different levels of whole packs to split packs than others, does that marry up? Can you identify the customers you suspect are buying products elsewhere by comparing buying patterns between customers, if you know they are buying product xxx somewhere else, then that is an opportunity you got for free, ask them if they know you sell product xxx, if they say yes but it is a bit cheaper at other supplier so we buy it there because we have to go there for product yyyy. Then at least you know why, perhaps you could skimp a little bit of margin to compete, perhaps you should stock yyyy as well? These are all the sorts of things you might be able to do if you collect and analyse the sales information.

It means a change to working practice, but rather than being a band aid to keep things going it becomes a cure for the underlying problem.

If this is not going to work, if you have to do it by the kilo, then why not get rid of the 20 kg in the description and just sell kilos of topcoat.

You could use an expression on the item to calculate the number of units/packs/rolls/drums and the number of single KGs extra.

You may want to have a minimum qty to sell, you might want to only allow only whole packs of some things. You could use the extra fields on the product record to do this, you could name Custom1 Allow Splits and it should contain a Y if you do allow splits on that products And Custom2 could be minimum Qty. If you are not splitting packs, then I would handle them as packs and not by the kilo because you will never sell kilos only whole packs.

You expression is now be more simple and as you ad new products, you do not need to alter the layout to accommodate them, you just set those 2 fields.

I am not suggesting either of these will work for you, just that there are ways and means around these issues and you need to look for a more generic way to achieve what you want and look to see if you can simplify and build better historical data so in a couple of years time when you are booming, you can go back and ask the questions you will need at that time of your sales.

If knowing what products you will split packs on and which one you will not is useful, then embedding that into the stock record could pay dividends elsewhere in future.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

Post Reply

Who is online

Users browsing this forum: Ahrefs [Bot] and 10 guests