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

Does anyone know why.

I have managed to work around it by using a third party tool to do the allocation, but am I missing something?

I don't know if this is related but I am struggling to link a payment (eg SR) to a specific invoice item. I am linking as follows:

AUDIT_USAGE by split_number to AUDIT_SPLIT by header_number to AUDIT_HEADER by inv_ref_numeric to INVOICE by inv_number to INVOICE_ITEM

This works fine when the invoice only has one invoice item. But if there is, say 2, then I get 2x2 = 4 results and it is not clear which is correct. Is there a better way of doing this?

You can't link to items in 2 places the invoice items link repeats all the rest for each invoice item.

Why are you linking to the invoice item?

brucedenney wrote: 20 Mar 2025, 10:39 You can't link to items in 2 places the invoice items link repeats all the rest for each invoice item.

Why are you linking to the invoice item?

I link INVOICE_ITEM.STOCK_CODE to STOCK.STOCK_CODE, I want to get the STOCK.LAST_PURCHASE_PRICE

You can't do that in sage report designer.

You need to do it as multiple queries and then combine them together.

I would do it in Excel and have 3 queries

One would get the stock code and cost price.

Another would get the invoices and Items.

The third would grab the payment info.

You could do this in Excel using ODBC to pull the data out of Sage.

I guess you are trying to do something like calculate commissions based on profit on paid invoices. There are edge cases you need to consider eg what if an invoice is part paid?

I would look at the Amount of the invoice that has been paid and divide that by the invoice total to get a percentage paid, then multiply that by the profit of the invoice and then apply a commission rate to that.

brucedenney wrote: 20 Mar 2025, 15:09 I guess you are trying to do something like calculate commissions based on profit on paid invoices. There are edge cases you need to consider eg what if an invoice is part paid?

I would look at the Amount of the invoice that has been paid and divide that by the invoice total to get a percentage paid, then multiply that by the profit of the invoice and then apply a commission rate to that.

Yes great guess! It is related to commissions.

I am now using a bit of a work around. I get all the relevant payments for the quarter (SR,SA) and associated Invoice numbers and invoice items. I limit this to fully paid invoices (INVOICE_HEADER.PAID_FLAG='Y'). I process this into a unique list to remove duplicates. Then I run another set of queries looping through this list, to get the INVOICE ITEM data.

I am using pypyodbc