Can't think of a good way to do this that will perform particularly well. In fact the only way I can think of getting it to work would be to use sub reports.

The initial report would need to be on invoice items, joining this back to invoice and then to sales ledger, and to then group by the stock code. This would give you the items that customers have bought and you can apply whatever criteria or filtering you like to this e.g. only items bought in the last 6 months. Then in the stock code header or footer section you could add a sub report based on the price table with criteria for the pricing ref and stock code. Back on the main report you'd then need to link those criteria to pricing ref from the sales ledger table and stock code from the invoice item table.

I've not tested that but I believe it should work. The main downside is that I'd not expect it to perform particularly well as it would result in what is called an N+1 query, meaning that a query would run for the main report, and then another query for every line (stock code) in the main report i.e. the more stock items customers buy, the more queries are run and the slower the overall report is to produce.

The only alternative would be to export the data to something else and do the lookup and report generation there e.g. Excel.

Hope that helps.

Hi Darron,

Thanks for your suggestion for getting this report. It seems like it's not as easy as I first thought it would be.

Is it not possible to link an existing Sage report already filtered to a customer to their price list? The report IP_SALBC gets me the data that I need. I thought it would have been fairly straightforward to then link that to the customers price list or is that where it gets tricky?

Regards

Andy

In products / reports / Price Lists

There are two Customer Price reports, The "List Prices" works for Customers with a Price List specific to them, the other works for any customer

You should be able to link this report to the Stock Availability

Linking a customer to a customer price isn't a problem, but what you need to do is link it to the specific product within that price list and this gets in to circular joins which won't work. You could certainly start with the IP_SALBC report and use that as what I referred to the initial report above, but you'd still need add a sub report to get the specific price from the price list for each stock code.

Another complexity that I forgot about is that the price tale will only have the raw price value if the price list is manually calculated. This measn that if the price list is automatically calculated you would need to use the CaclulatePrice method to get the actual value - see the customer price list prices report (PRCLSTGP.report) for an example of this.

Unfortunately you cannot embed an existing report as as sub report so you'd need to recreate one of them as a sub report. Alternatively you could export both reports and join them back up together in an external tool such as Excel.

If you edit PRCLSTGP you can add QTY IN STOCK and other values

I would create individual customer price lists for each customer I wanted to distribute to

No need for sub reports

That won't give the prices for products that customers have actually bought though. If all that is needed is a price list with all products on it then simply adding stock quantity to PRCLSTGP would work.
No need for separate price lists unless the prices offered are different for different customers though as you can simply add a criterion or SALES_LEDGER.ACCOUNT_REF to that report and have it automatically filter to the correct one.

I agree. My understanding is that the sales history requirement is to create a list of products that the customer is interested in. Yes it would be nice to be able to automatically adjust the list when a customer buys a new product, but using an edited version of the PRCLSTGP is the simple solution.