Northwind2 caculating invoice line? (1 Viewer)

[Gasman]

Hi NW2 people,
I just offered the DB as a good example for producing invoices.
The O/P was questioning why the calculation was done on the form and not the table, where he was initially doing it with calculated fields.
I replied, no idea, but would ask a developer.
I did say that I would do it that way as well, but then I have only started using a db version with calculated fields (I believe?, 2019), so would do it the old fashioned way.

TIA

 

[GPGeorge]

We followed the fundamental principle that calculated values should not be stored in tables. They are calculated as needed for display in forms and reports.

But perhaps there is more to the question than that. Was there some other consideration involved in the original question?

 

[Gasman]

Not sure TBH George,
Here is what was posted to my link. I can post the link to the thread if required.

I dare say, if I started with a db that supported calculated fields, I might have asked the same question.

Thank you all. sorry for the confusion, but you guys helped me a lot. the northwind2 database is a great help i can take the idea and implant it on my database. I think it will work the best the order form is really good, and it is what I'm looking for. I just need to make it work on my data. Is there any advantage or disadvantage of calculating the Qty and unit price on the form vs calculating it on table?
northwind database use the following on the form page =[txtQuantity]*[txtUnitPrice] for total of 1 row
I create a calculated field on my table that call total that doing the same thing.
my question is what is the best way to do?

Edit: Just looked and I do not think 2019 will do table calculations?

 

[GPGeorge]

Thanks, I think the answer, then, is the same. The proper place for calculated values is in Forms and Reports. With only rare exceptions, storing calculated values in tables is bad juju.

 

[Cotswold]

I always stored the line calculations in a table as integers (longs in Access) as that ensures any totals now, next year, or whenever, will be the same. Calculations with VAT (tax) can be all over the place with rounding if the %age has decimals and different VAT rates are on the invoice (VAT should be calculated on every line, not on invoice sub totals). If you add up 10 or 10,000 lines their total should be the same every time. It can never be a penny out. Using integer calcs ensures that. On a form or report I'll then just string handle the decimal point to the correct position. Additionally, some systems allow the user to set rounding to suit themselves which may well cause issues unless integer calcs are used,

As an aside, whilst we are on invoices. I never used an invoice header table. It is quite unnecessary with SQL and only adds complications with needing to be updated. I also use integer calcs on weights.

I can see no reason not to use integers because I always remain in control of the result and there is no need to ever check the totals. I guess that it is faster to add up than calculate every time and space on the disk hasn't been an issue for thirty years.

 

[GK in the UK]

I do have an invoice header table but no values are stored in it. I realised long ago that Sage and possibly others don't bother, they just sum an invoice based on some grouped reference field. When I started with Access I'd never heard of an aggregate query (didn't actually know about any type of query) but if I was starting again I might well adopt that model, it seems brilliantly simple at the cost of repeating header information in the lines.

For quantities and prices I use re-scaled currency, (4 dp isn't enough) and the cardinal sin, I store the calculated net value in the transaction line as currency. I could eliminate it and I've considered it for the sake of normalisation purity but the string to calculate it with discounts etc. is so long with the re-scaling it just seems easier to select 'NetValue' in queries. Since it's written at the same moment as all the other stuff that makes up the line how does it get to be wrong? (Cue 100 reasons I guess). I didn't know Access could do the calcs at table level but I wouldn't adopt that method.

UK VAT - not being VAT registered means it's not an issue for me but I've yet to build a model to record purchase VAT in my application. For sales, it should be calculated on the invoice line. But as I understand it, for purchases, you reclaim the total VAT that the supplier has charged even if it differs from your calculation. There is more than one legally valid way to calculate and apply rounding to VAT. So I'm wondering how to make this work. What if the line totals don't agree? Do I amend the VAT value on one line to make it match? Messy and I won't actually know which line was in error if the supplier shows only the invoice total VAT. That alone would force having a column for VatValue which would not necessarily have the same result as my legally correct way of calculating it. Or do I store purchase VAT in the header? Equally messy as the same form does for sales and purchases. It's a conundrum I've yet to solve.

 

[Minty]

You have to store the VAT per line, as the line items might have different vat rates. No choice.
For a suppliers invoice I would record the VAT they charged per line regardless of the calculation.

Using one form for both would be problematical, but not impossible.
I'm not sure I would go down that route due to the complications.

 

[cheekybuddha]

Thanks, I think the answer, then, is the same. The proper place for calculated values is in Forms and Reports. With only rare exceptions, storing calculated values in tables is bad juju.

But a calculated column in a table doesn't suffer from the same issues as storing the result of a calculation, since it ought to update on the fly when its inputs change, no?

I don't know whether it's true in Access, but in other RDBMS's you can also index calculated columns which can be a bonus.

 

[Cotswold]

UK VAT - not being VAT registered means it's not an issue for me but I've yet to build a model to record purchase VAT in my application. For sales, it should be calculated on the invoice line. But as I understand it, for purchases, you reclaim the total VAT that the supplier has charged even if it differs from your calculation. There is more than one legally valid way to calculate and apply rounding to VAT. So I'm wondering how to make this work. What if the line totals don't agree? Do I amend the VAT value on one line to make it match? Messy and I won't actually know which line was in error if the supplier shows only the invoice total VAT. That alone would force having a column for VatValue which would not necessarily have the same result as my legally correct way of calculating it. Or do I store purchase VAT in the header? Equally messy as the same form does for sales and purchases. It's a conundrum I've yet to solve.

Minty is right in #7.

Calculate the line total and then that VAT on that line total and store them into Long fields as integers (is my method). So 123.45 is 12345 and the VAT (20%) 24.69 2469 and those values will not change after an invoice has been printed, issued and posted to accounts . As you calculate the VAT per line, the total of many lines on an invoice will be more than the total of the sub total of all the lines and with VAT calculated on that due to rounding on each line.
As an example, for 125.39 x 0.2 = 25.078 ; you need to decide if it 25.07 or 25.08. The same with discounts to arrive at a line total.

If you are building a system with VAT calculations, do not hardwire the VAT rate in. You must have a VATFILE with the VATRATE. VATCODE, plus start and end dates for the rates. Then when/if the VAT Rate changes, the user can update their files accordingly. (we had one period where within the space of a few years the VAT moved from 17.5% to 15% to 17.5% and then to 20% where it is now). If you hardwire it and the rate changes you will have huge problems. When your invoice section opens it needs to lookup the current VAT rate, or the VAT rate for the invoice date depending. However you decide how it works. It is like a very easy price list file. Which can be a joyless can of worms to cope with price changes.
The VATCODE is usually a single letter code for different rates. You can then, only if you want to, show the VATCODE to each line. Necessary if your clients' sell goods at different VAT rates.

 

[GPGeorge]

Awesome. The purposes of the templates, both Starter and Developer, included providing a solid design from which to learn and to provide a launching pad for additional development and enhancement. This discussion very much falls in line with that second goal.

In addition to offering differing ideas about how to implement features such as a calculated value in a field in a table, it extended the discussion of additional relevant factors.

Thank you very much for contributing to the conversation about Northwind 2.

 

[CJ_London]

the VAT rule is if the total invoice value, net of VAT is less than £250, you can calculate on the total invoice value (known as a retailers VAT invoice), otherwise it should be calculated by line. However this is only of any real use if all the sales your business makes is less that £250 a time. See section '16.6.2 Retailers’ VAT invoices' in this link

VAT guide (VAT Notice 700)

The guide to VAT rules and procedures.

www.gov.uk

An invoice is what I would call a 'legal' document and the rule for legal documents should be that all calculated values and the components that make up that calculation are stored with the document so there is no risk of not being able to replicate the document a year or two down the line. But I guess there is an argument that paper or electronic versions stored securely for easy retrieval would meet this requirement.

 

[Cotswold]

the VAT rule is if the total invoice value, net of VAT is less than £250, you can calculate on the total invoice value (known as a retailers VAT invoice), otherwise it should be calculated by line. However this is only of any real use if all the sales your business makes is less that £250 a time. See section '16.6.2 Retailers’ VAT invoices' in this link....

That demand by HMRC is just ludicrous and should be totally ignored. You'd need to work out the VAT line by line and then at the end decide if you will total them up, or do a single VAT calculation. Who would pay you to include such a pointless operation? SAGE calculates VAT line by line. So that's good enough for me.

I used to have clients complain that their clients complained about the VAT calc difference. I'd tell them if they entered the same values into SAGE they'll get the same result as mine. I would say that when SAGE changes it, then I would. PEGASUS is the same as is QUICKBOOKS no doubt. Of course all of this was much more noticeable when VAT was at 17½%. Far less at 20%.

HMRC also instructs you to round VAT up but I never did. I would though round up discount calculations to increase, if applicable the amount of discount given. It is surprising and depressing just how many people will moan to their suppliers over a penny.

 

[GK in the UK]

You're describing the method, I think, for sales. I have that all covered, more or less, but the conundrum arises when you consider purchases. Where do you store the purchase VAT? I have a place to save it, the header record which I think would be the right place for purchases but you don't have a header record.

I have a recent supplier invoice in front of me which has multiple items on it all at the same VAT rate ('S' for standard) but it shows the VAT as a total. I've built a system where the same form is used for sales or purchases. The only thing that changes when you create an invoice is that, for sales, the quantity field alone gets negated behind the scenes before it's updated to the table, the same table for sales or purchases.

let's put aside the taboo of saving calculated values for a moment, and let's ignore my re-factoring of quantities and prices:

A purchase line in the table will show qty: 100 price: £1 NetValue: £100
A sales line in the table will show qty: -100 price £2 NetValue £-200
The user keyed 100 items sold and the invoice total appears as £200. But viewed in the ledger, the sales invoice line shows as 100 items in the 'out' column and £200 appears in the credit column.

But that's a side issue to the VAT

Now let's say I'm entering this supplier invoice in and I enter the details and the calculated VAT on each line sums to £117.50 in the 'VAT Total' field on the invoice in my system. But because the invoice has multiple lines and the supplier adopted a different valid method of calculating and rounding the VAT it shows as £117.49. It doesn't matter that it's different, HMRC state you record what the supplier charged.

Which line differed? I don't know and it doesn't really matter as it's not a cost to the business. But where do I save it? I'm inclined to think it should be in the header record. I could permit the line VAT value to be over-ridden for purchase invoices but that's messy. I think I will permit the purchase VAT total to be over-ridden and that keyed value gets saved in the header. The line VAT values for supplier invoices will be in the table (I could zeroise them), but redundant. Thinking about it more, maybe I could simply not take line input for VAT on purchases at all. Just writing this stuff out has focussed my mind a bit.

 

[CJ_London]

that demand by HMRC is just ludicrous and should be totally ignored.

It is not a demand - it is a suggested alternative that HMRC consider to be acceptable. It's called Retailer VAT invoice for a reason. You don't have to use it.

I think I will permit the purchase VAT total to be over-ridden and that keyed value gets saved in the header.

You would need to permit it. - you could use a calculation on the gross amount (or sum of vatable rows) to determine the VAT value rather than keying it in each time - and overwrite when required

With regards where to put it - it ends up in the VAT control of the ledger - transactions would be something like

Account	Dr	Cr
Purchase Ledger (or bank)		117.49
VAT Control	17.49
Stock	90.00
Delivery charge	10.00

So you would store the total value in the header, not the VAT value

 

[GPGeorge]

You know, reading through all of the discussions about the details of implementing tax calculations reminds me of early discussions among the NW2 team about how much depth to go into in a template aimed at the newer developer. I think we might still be arguing over details and hoping for a launch of the templates "at some point" if we hadn't chopped off the discussions and stuck to a reasonably straightforward starting position from which you all can continue to expand.

 

[Gasman]

Blimey, look what I started?.
I was just curious between using calculated fields in table or the form.
I cannot see the fields option in 2019, so I would still have to use the form method.

 

[Cotswold]

It is not a demand - it is a suggested alternative that HMRC consider to be acceptable. It's called Retailer VAT invoice for a reason. You don't have to use it.

I would be and am totally opposed to implementing that. You could get the situation where your system issues a multi lined invoice at £250 and another at 250.01 and depending on the calcs one could be five or ten pence different due to rounding. You would lose the will to live having to explain why every time it cropped up. A typical government idea to solve a problem that doesn't exist.

 

[Cotswold]

You're describing the method, I think, for sales. I have that all covered, more or less, but the conundrum arises when you consider purchases. Where do you store the purchase VAT? I have a place to save it, the header record which I think would be the right place for purchases but you don't have a header record.

I have a recent supplier................................

I do not see a difference between a sales and purchase invoice design.
I am presuming that your system creates sales invoices that will end up in the purchase ledger of their customer.
The systems I am referring to are in essence ticketing systems. Sales tickets are entered and then formed into invoices. The invoices are then despatched to the customer and entered into their purchase and nominal ledger as one line. Total goods, total vat. The company sending the sales invoices out will then import them into them into their accounting system as one line. The sales total and the sales VAT. Of course if there are multiple VAT rates on the invoice then that split is needed in the import with a separate line to each.

A very, very, very simple ticketing system could be as follows:
TicketNo, TicketDate, CustCode, Goods Description, Quant, Rate, Ticket Total, Ticket VAT, InvoiceNo, InvoiceDate, NominalCode, PostedRefNo

I would then create a query with all unposted tickets in Cust Code + TicketNo order. Then trip through them calculating the Ticket and VAT totals and adding the Invoice Numbers and Invoice Dates. When forming the invoices all I need to do is advance the invoice number as I reach each new CustCode.

I can then create a SUM() query for each invoice to create the header on-the-fly. It will be one line Cust Code, Invoice Number & Date, Nominal, SubTotal of Goods & VAT. That header can then be used to create an export file to import into the Accounting system to update the Sales and Nominal Ledgers. It will also be used as the header for an invoice by linking the CustCode to the Customer Table for name and address details for display.

You can then print the invoices first with the Header, then the body showing all Tickets, then the footer showing the Goods and VAT Totals. Before you post the Tickets/Invoices to accounts you first update the PostedRefNo from zero to a unique posting number. All the tickets remain in a table and you can pull individual Tickets, or Invoices as and when needed. Either singly or as a batch. Only charges with a zero post number can then be edited. There is no physical header table. Obviously you could normalise the above but I have never felt the need. It is simple and that basic design served me well for over 30 years. If I had a header table, I am then obliged to maintain it as charge items are amended. Using just one table there is no need. Some of my systems will be adding many thousands of charges per month. Never any speed issues, even over Terminal Server. Access is sound if you look after the databases.

Of course most ticketing systems can be very complex. Invoicing by Order Number, or by Site. Weekly or Monthly Invoices, Many Nominal Codes, Several Cost Centre Codes, OnStop or Suspend settings, Discounts etc, etc. Charges may be by tonne and you could have Quant, Length, (Weight per Metre in a separate Table of course), the LineTonnage and TonnageRate to calculate the line charge and VAT. Of course in those cases there will be more tables and normalisation.
( odd clarifications added in a minor edit )