You are on the correct track.
Start with the Client Table.
From there perform a reference to the invoice table.
Make sure there is a lookup in the invoice table to get Client Name, Client Address....
Make the document button in the Invoice Table.
Now comes the more difficult part, adding items to the invoice.
To do this you are going to have to have 2 more tables.
[products] - Each item for sale, UPC, description, price etc.
NOTE: I highly recommend, do not use the ID column for anything, people have been tempted to use it for product ID, or employee ssn etc. It works "in theory" but in reality it eventually causes issue. There is no need to do it either, just make a new column call it product ID and set it to unique.
You now have.
[Invoices] and [Products], you need to marry them up.
You need a Many to Many relation that uses a LINK table. The Link Table is going to be your invoice items. The link table at its minimum has two columns (invoice relations) (Product Relation)
So it looks sort of like this
[Invoice] => Item 1 (invoice relation) - (product relation) [Product]
----------- =>Item 2 (invoice relation) - (product relation) [Product]
----------- =>Item 2 (invoice relation) - (product relation) [Product]
You can get the Product Details using lookups in the LINK Table. [Invoice Items]
You will still print the invoice from the invoice table. When you make the Word Template it will include the invoice items.
One problem you will need to consider. The setup above does not accommodate product changes well. If I see Advice at $10 an email. I sell 10 of them today to one client. I one invoice 10 items $100, Day 1 $100, sales to date $100.
Tomorrow I decide to raise the price to $20 (inflation)
I sell 10 of them. I have an invoice for $200 Day 2 $200 , sales to date $400..
Wait what....?? the printed invoices, add to 300 computer says 400! Which is correct, BOTH
The problem "linking" to the product price, if you change the price there it changes is everywhere. Every single invoice to the beginning of time.
There are a couple of ways to handle this.
You can just understand this and use paper copies to refer to an old invoice. (bleah, yuck). I suppose the could be industry an where historical invoices are not important. (still bleah)
If you almost never change prices, you can never change a product once created, If price changes you make a new product and archive the old product. DO NOT delete the old product! Make an archive checkbox and filter them out of the Invoice Item selection.
If you delete the product it will "orphan" all the links in the old invoices, this would be VERY VERY BAD. In most cases people do not notice the "orphan" issue until they delete multiple records. And maybe made new products using the same ID, (because they used the ID column for something other than record id, Leave the ID column alone.). Typically this is a time consuming expensive thing to fix.
A better way,
in the link table [invoice items] create some new columns. (Item Description) (Item Price) etc.
Set the column Default/Calculate to a formula . In the formula simple put the Lookup from the product table.
Item Description = Product Description.
Hide the Lookup columns from the views and forms,
Now you have "captured" the information (ie) price. Changing product prices will not affect old invoices.
Done correctly, the (Products) table is only a reference. You "could" delete products and not orphan invoices. BUT... I still highly 100% recommend setting up a way to archive products, deleting anything from a database typically should be avoided. At least very very carefully considered.
I am certain what I wrote above may be a bit confusing, probably a lot. We made some tutorials here.
https://www.teamdesk.net/videosThe ones about relations cover this subject.
there are also some sample database that have relations.
You can load one up and play around with it for a couple weeks and then delete it.
