Invoice Tables PK FK and Indexes

[Jordonjd]

Hi there,

I have a draft form for invoice creation, (i know there shouldnt be '-' in names but this was made a while ago)

Stage-1 main invoice
Stage-2 product details/costs etc

At the moment the autonumbers are the PK's but INV-1-Ref is the actual invoice number

My question is, is there some other relationship or index that i should put in place between the two other than the Autonumber? Or should the autonumber not be the PK? if the record is deleted somehow then i would not be able to relink the missing product records from stage-2 again?

I would like to make sure i get the foundation structure correct as this will be the same/similar structure for Sales Orders, Purchase Orders, Quotations etc

I've attached a screenshot which includes a snip from the relationships as well

 

[arnelgp]

on your Relation window, make sure you Cascade Delete to the
child table. when the Header record is deleted, all child Items will automatically
be deleted.

 

[Jordonjd]

on your Relation window, make sure you Cascade Delete to the
child table. when the Header record is deleted, all child Items will automatically
be deleted.

Of course, thats a good idea,

I guess i could also index the INV-1-REF (invoice no) with no duplicates

 

[CJ_London]

Couple of general points about the tables - I note you have an address field - this would normally be in a separate customer table or perhaps an address table if customers can have multiple addresses. Would only store that in a header table if every customer only ever bought from you once

other point is about vat. Not sure where you are storing it but for calculation purposes it should be against the line, not the header. That is a legal requirement

 

[Jordonjd]

Couple of general points about the tables - I note you have an address field - this would normally be in a separate customer table or perhaps an address table if customers can have multiple addresses. Would only store that in a header table if every customer only ever bought from you once

other point is about vat. Not sure where you are storing it but for calculation purposes it should be against the line, not the header. That is a legal requirement

The address field on there is actually a lookup to a separate address table.

I didnt know that about VAT, i used our companies existing invoices as a template and there is no vat per line item.

Currently our invoices are excel generated and then entered on sage, so i assume it is stored by line in sage, do you know if the rules apply to the invoice that is actually sent to the customer?

 

[Jordonjd]

After reading quite a bit it seems the Vat per line needs to be displayed if there is different rates within the invoice.

Im 99.99% we always use the same rate per invoice but i'll move VAT rate to "Stage-2" table so its stored correctly,
have the subtotal calculated and also put a dropdown box so the user can select a VAT rate on "Stage-1" and set that as the default for the field in "Stage-2"

 

[CJ_London]

Hmrc require the vat calculation on a line by line basis to avoid potential rounding issues - can equate to a penny per line so not likely to break the bank.

sage calculates on line by line and stores the calculated value on each line record - recommend you do the same since the data is a matter of record (one of the very few occasions where you should store a calculated value) and must be stored for a minimum of 6 years - but I guess you have sage for that.

 

[Jordonjd]

oh so even store the value not just the % ?

Yes all the actual accounting is Sage but if there is a proper way to do it then i like to try and make the database follow that as much a possible.

So i would actually create a calculated field in the table of the line total and vat % ?

 

[CJ_London]

Yes - best to include all the variables used - that way you can always reproduce the invoice without any calculation. Doesn’t mean they have to be displayed on the document.

Typically that might include quantity, price, gross line value, discount percentage and amount based on the rules you apply (might be quantity discount, trade discount, Something else. note an invoice value discount would typically be stored in the header record), vat rate and amount and finally net line value.

Once an invoice is issued - I.e. sent to the customer the records should be locked so they cannot be changed. This depends on your rules, in you case perhaps the issue event is when sage is updated, until that point changes can be made, but you can imagine the confusion if you send an invoice to a customer, make a change and then update sage.all depends on your real life processes

you should apply this principle to any document that has legal status - might include quotes, payroll calculations (typically bonus and commissions, time recording). Despatch notes, etc

 

[Jordonjd]

will do, thanks for the advice everyone

 

[Cotswold]

Why have an invoice header table and an invoice body(charges) table? I only have the invoice body.
I total up the details from the body and show them in the header. You don't then have the problem of
making sure the header totals and VAT are updated if anything changes in the charges section. It has
worked well for me doing that for more years than I care to remember. Far easier and rock solid.

If you must do it with a Header and Body do not use an Autonumber as the link. If you need to delete
or have any corruption you will lose the joining association. Have an invoice number in a master table
which you use for each new invoice. Then increment it as each invoice is added.

 

[Jordonjd]

Why have an invoice header table and an invoice body(charges) table? I only have the invoice body.
I total up the details from the body and show them in the header. You don't then have the problem of
making sure the header totals and VAT are updated if anything changes in the charges section. It has
worked well for me doing that for more years than I care to remember. Far easier and rock solid.

If you must do it with a Header and Body do not use an Autonumber as the link. If you need to delete
or have any corruption you will lose the joining association. Have an invoice number in a master table
which you use for each new invoice. Then increment it as each invoice is added.

Hi, I think its standard practise to have the header and details tables with a relationship.

There are no fields that store any data in the stage-1 (header) table, its fields on the form

But yes my original question was whether it was better for the invoice number or autonumber be the pk and fk due to possible breaks in the relationship

 

[Cotswold]

For an invoice system that will be just creating a series of random invoices, I can understand that a Two Table System maybe the solution. However, if you were creating an invoice system that will need to process hundreds or thousands of invoices a month from charges or tickets already entered into the system, then a One-Table solution is far easier.
Invoicing with a spreadsheet, in 1995 maybe but in 2021...........frightening. I've not seen many spreadsheets without an error hidden away somewhere. In general, they are never checked or tested, just accepted as correct.

As CJ stated, and I will reiterate, you should at design stage calculate VAT on a line by line basis and not on the invoice total. An invoice system should allow for multiple VAT rates and VAT on the invoice total calculation cannot do that. Altering and testing it later will be a punishing and time consuming exercise .
Also, from your calculations of Quantity X Unit Price, I advise you to place the result into a Long field. The same with the VAT. So you are storing all calculated totals as pence or cents depending on where you are. It is the only way to avoid rounding errors. When you print the invoice simply string handle the values to place the decimal separator on the fly. You can also total those fields to write the Invoice and VAT Totals into the Header Table. You can save space by showing the VAT Code 1,2,3.. on the line with a notes explanation at the bottom. Unfortunately the VAT on the line may create a different VAT total than shown on the invoice total. Although I accept, maybe not so much at 20% as it did at 17.5%. Prepare yourself to give boring and detailed explanations to some dope just why that is.

If you intend to export invoices into any accounting system, for instance, SAGE, you will need to be able to handle that VAT split. Also, VAT rates can change. If the rate changes to 17.5%, or 15% can your system be updated by the user? Or have you hardcoded the VAT Rate in?.....not a good idea.

VAT can be a can of worms if not handled correctly. Really you need a VAT Code in the Part Number Table and a Standing Data VAT Table with VAT Rates, current and historical. That Table should have the capability to store VAT Codes and Rates with date ranges for each VAT Code. You can then reliably know the VAT Rate for the point-of-sale date.

It appears you haven't provision for a Client's Order Number. Some may require all charges for a period on one invoice and want to see their Order Number on each charge line to check the invoice. Others may only want all the charges relating to one order on one invoice. Which of course your example would do with an Order Number added. If you end up short of room across the invoice, you can always use two lines per charge item on the print.

Don't forget, test, test, test and test again.

It's all good fun!

 

[Jordonjd]

For an invoice system that will be just creating a series of random invoices, I can understand that a Two Table System maybe the solution. However, if you were creating an invoice system that will need to process hundreds or thousands of invoices a month from charges or tickets already entered into the system, then a One-Table solution is far easier.
Invoicing with a spreadsheet, in 1995 maybe but in 2021...........frightening. I've not seen many spreadsheets without an error hidden away somewhere. In general, they are never checked or tested, just accepted as correct.

As CJ stated, and I will reiterate, you should at design stage calculate VAT on a line by line basis and not on the invoice total. An invoice system should allow for multiple VAT rates and VAT on the invoice total calculation cannot do that. Altering and testing it later will be a punishing and time consuming exercise .
Also, from your calculations of Quantity X Unit Price, I advise you to place the result into a Long field. The same with the VAT. So you are storing all calculated totals as pence or cents depending on where you are. It is the only way to avoid rounding errors. When you print the invoice simply string handle the values to place the decimal separator on the fly. You can also total those fields to write the Invoice and VAT Totals into the Header Table. You can save space by showing the VAT Code 1,2,3.. on the line with a notes explanation at the bottom. Unfortunately the VAT on the line may create a different VAT total than shown on the invoice total. Although I accept, maybe not so much at 20% as it did at 17.5%. Prepare yourself to give boring and detailed explanations to some dope just why that is.

If you intend to export invoices into any accounting system, for instance, SAGE, you will need to be able to handle that VAT split. Also, VAT rates can change. If the rate changes to 17.5%, or 15% can your system be updated by the user? Or have you hardcoded the VAT Rate in?.....not a good idea.

VAT can be a can of worms if not handled correctly. Really you need a VAT Code in the Part Number Table and a Standing Data VAT Table with VAT Rates, current and historical. That Table should have the capability to store VAT Codes and Rates with date ranges for each VAT Code. You can then reliably know the VAT Rate for the point-of-sale date.

It appears you haven't provision for a Client's Order Number. Some may require all charges for a period on one invoice and want to see their Order Number on each charge line to check the invoice. Others may only want all the charges relating to one order on one invoice. Which of course your example would do with an Order Number added. If you end up short of room across the invoice, you can always use two lines per charge item on the print.

Don't forget, test, test, test and test again.

It's all good fun!

Thanks for the advice, I'll definitely be moving vat calc and suggested fields etc onto the lines instead of the header, the standing data vat table is something i hadn't thought of either. I do have the customer order number field within the table, but not all fields are showing in the current draft so that bit is ok.

This will eventually be a part of a upgrade/replacement of several parts of the existing system that we use so its great to get advice as i would like to make the new database as water tight as possible

 

[Jordonjd]

Update:

Hi everyone,
So for now i have added fields to the invoice details table, changed a few etc etc (fyi these are not the actual field names)


Gross line total,
Discount%,
Discount Value,
Vat Rate, (atm just a % number field but i will look at lookups etc with use of vat codes as well)
Vat Value,
Net Line Total

Normally i would have used actual calculated fields but that do not store the result. So on my form i also made some calculated textbox controls.

txt_ Gross line total =(Qty*Price)
txt_ Discount value =(Qty*Price)*Discount%
txt_ Vat value = txt gross line total * Vat Rate
txt_ Net line total = Txt Gross line total - discount value

If this is an acceptable method if there something better than writing hundreds on small after update events etc on all the controls

So my questions are:

Is there a suitable method of passing those textbox control values to the table controls, if so which event and how

Is the use textbox controls wrong and i should be doing this in another way?

As always any advice/critiques or comments are very welcome

Thanks in advance

 

[mike60smart]

Update:

Hi everyone,
So for now i have added fields to the invoice details table, changed a few etc etc (fyi these are not the actual field names)


Gross line total,
Discount%,
Discount Value,
Vat Rate, (atm just a % number field but i will look at lookups etc with use of vat codes as well)
Vat Value,
Net Line Total

Normally i would have used actual calculated fields but that do not store the result. So on my form i also made some calculated textbox controls.

txt_ Gross line total =(Qty*Price)
txt_ Discount value =(Qty*Price)*Discount%
txt_ Vat value = txt gross line total * Vat Rate
txt_ Net line total = Txt Gross line total - discount value

If this is an acceptable method if there something better than writing hundreds on small after update events etc on all the controls

So my questions are:

Is there a suitable method of passing those textbox control values to the table controls, if so which event and how

Is the use textbox controls wrong and i should be doing this in another way?

As always any advice/critiques or comments are very welcome

Thanks in advance

Hi
You are asking for advice on how to create various tables for your database.

Then you say "fyi these are not the actual field names"

This does not help us at all.

It is always best to state the actual names used so that the correct guidance can be given.

 

[Jordonjd]

Sorry,

So following advice i have added/moved more fields to my invoice details table.

I was also advised that it can be good practise for invoices to store some of these calculated values in the invoice details such as Vat value and Line total


Invoice details new fields now has:

1. Inv2_GLT (gross line total, Qty* Price)
2. Inv2_Discount (discount %)
3. Inv2_Discount_Value
4. Inv2_Vat_Rate
5. Inv2_Vat_Value
6. Inv2_NLT (Net line total, inv2_GLT - Inv2_Discount_Value)

I was going to use calculated fields but obviously if data is changed then so will they, So on the form i created some calculated textboxes of similar names to do the calculation, then pass that data to the corresponding table fields to be stored.

I may be taking the advice of storing the results a bit too far, i am not sure.

If it is good practise for invoicing to store these values then i am not sure of the best way to pass those results in the calculated textboxes to the table fields.

I could put after update events on the fields but because there is a few fields for my level, of VBA it would be a lot of IF statements to try and make sure there are no errors when the users create invoicing and the corrects calculations are saved.

So at this point the question has evolved from the tables and fields to:

If it is correct for me to store these values (calculated) then what would be the best method for me to do so from my form.

I can move away from this thread and start a new one if its better suited

 

[Jordonjd]

As an example:

the field labelled GLT is the table field Inv2_GLT

GLT_Value is a textbox with the expression in the control source.

of course not all the fields would be visible, i was the planning to update GLT with the 100.00 from GLT_Value but i am not sure if this is a good method or how i would achieve with room for error with 3 or 4 fields on the same row

 

[Jordonjd]

I deleted all the textboxes and made a small function to call on the after update event of all fields involved with calculating the values.

Function InvValueRefresh()
Me.Inv2_GLT = Me.Inv2_Qty * Me.Inv2_Price
Me.Inv2_Disc_Value = Me.Inv2_GLT * Me.Inv2_Disc
Me.Inv2_NLT = Me.Inv2_GLT - Me.Inv2_Disc_Value
Me.Inv2_Vat_Value = Me.Inv2_NLT * Me.Inv2_Vat_Rate
End Function

Might not be pretty but it seems to work, even if there is null or 0 value fields.
now onto more normalization reading

 

[Cotswold]

If your calculations are correct, then that is fine. How you arrive at the result is your preference. It may change with
experience, which is what this is all about.

It is my opinion that all of the data that someone needs to see should be available without needing to scroll about to see it.
Many users prefer to use the keyboard only on data entry by means of the Tab, Enter, PageUp/PageDn and Arrow keys. Plus,
if you are using a datasheet a <Control>+<Apostrophe> <Ctrl>,<'> will copy the field in the line above, which often saves
time. Being forced into Mouse, Keyboard, Mouse, Keyboard operations is a real pain.

Having enough screen space has always been a challenge. (Try fitting everything in with a screen of 24 lines and 80
characters across, which is how it was in the olden days!)

If you don't have enough room on one line, reduce the font or use two lines. Maybe consider using a continuous SubForm
instead of a datasheet? They need more time to setup but two lines in that may be easier.

Not sure what "room for error" means. However, in the main you're really on your own when it comes to screen layout because
you know everything about the analysis and the application, which we cannot.

Just make sure data entry is quick and easy, requiring little if any skill from the user. Plus, make sure they cannot enter invalid
data. For instance; if it is a number field don't let them leave a Null in it, but if they do, convert it to a zero in the AfterUpdate().
Basically, make sure the users cannot enter anything that you don't want them to.

I also attach a PDF here explaining the single table invoice which I replied to Jordon off the Forum in a Conversation, which shouldn't really happen.