Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
  
Invoice Document
Perhaps I'm missing something, or I can't find a proper explanation on how to create an invoice document
ID
1255
Category
User Experience
Author

Francois Conradie
Date Created
2/12/2019 8:46:28 AM
Date Updated
2/13/2019 9:05:52 AM
Comments
cooper collier  2/12/2019 9:02:46 AM
Good Morning.

Is the issue creating the actual document that is printed or emailed?
or
Building a database to actually make invoices to start with?


Francois Conradie 2/13/2019 1:04:18 AM
So the question is, I accept that I would have to multiple tables
Client (typical client details)
Invoices (Should reference to client info and invoice items, date and invoice number)
Invoice Items (Should have item code, item description, amount)

The challenge:
On an invoice you have the client details, you have, say 5 items, these items have a total amount that should be displayed at the bottom. I assume you create a Custom document button when pressed the selected items will be used on an invoice document template.

How do I only show the client info once and how do I calculate the total and display it at the bottom. I understand how 5 items will be added line after line.

Do I understand the process correctly?
1. Create invoice in invoice table
2. Add invoice items
3. Select client

I know how to create custom documents so that is not a problem

Hope it makes sense what I would like to achieve


cooper collier  2/13/2019 9:05:52 AM
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/videos
The 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.









Feedback
Back to Search Results