Hi Paul

You don't mention whether you are working with an implementation of Sage CRM that is integrated with an accounts system.  I think that may change the way that people answer the question if you were.

The Quote and Order tables do have fields for Tax available - but so do the OrderItem and QuoteItem tables so are you going to calculate tax at the level of the Order/Quote Line or at the level of the Quote/Order.  The link to the product occurs at the OrderItem/QuoteItem level.

Hi, sorry about that. 

We are on Sage CRM 2022 R1 and it's integrated with Sage 200c Professional Version 12.00.0036

A colleague and I managed to get the VAT values auto-populating after a line item is saved by creating a trigger that updates the value in SQL Server but I'm not sure if that is an overly complicated way of approaching it. 

Thank you for the reply as I hadn't thought of the details around versions of software for inclusion in the post. 

I did this a few months ago on a system with Qnect 200 integration, also using a trigger:

CREATE TRIGGER [dbo].[maUpdateVAT]
ON [dbo].[QuoteItems]
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

UPDATE qi
SET qi.QuIt_tax=qi.QuIt_quotedpricetotal*tc.taxc_int_value_pc/100
FROM
QuoteItems qi
INNER JOIN inserted ins ON qi.QuIt_LineItemID=ins.QuIt_LineItemID
INNER JOIN TaxCode tc ON qi.quit_q_taxcode=tc.taxc_taxcodeid

UPDATE qt
SET
quot_tax=it.SumTax,
quot_grossamtinctax=it.SumQuotedPriceTotal+it.SumTax
FROM
Quotes qt
CROSS APPLY (
SELECT
qi.QuIt_orderquoteid, SUM(qi.QuIt_tax) AS SumTax, SUM(qi.QuIt_quotedpricetotal) AS SumQuotedPriceTotal
FROM
QuoteItems qi
WHERE
qi.QuIt_Deleted IS NULL
AND qi.QuIt_LineType <> 'c'
AND qi.QuIt_orderquoteid=qt.Quot_OrderQuoteID
GROUP BY
qi.QuIt_orderquoteid
) it
WHERE
qt.Quot_OrderQuoteID IN (SELECT DISTINCT ins.QuIt_orderquoteid FROM inserted ins)

END

I also use a SQL Trigger, it looks to see if a Tax Rate was applied to the line, otherwise grabs the Tax rate of the Account linked to the quote/order

Hi Paul,

Thanks for that! That looks very similar to how we got it to work but great to know we tackled it similarly!