Table fields (Page 1 of 2)

Hi Adam,

Ask 100 people and you'll get 100 different answers!  And while the theory of relational database design MIGHT be a good place to start, there are also practical reasons why you might end up doing things differently.

To give a brief response solely to your diagram, I would make the following comments (just my view, not saying it's right or wrong!)
1. I would remove 'invoice' table and have 'invoice_header' table (with fields for invoice number and invoice date) and 'invoice_line' table (with fields for qty).  Curently, you are repeating the invoice number and invoice date - a 10 line invoice will have the invoice number and the date repeating 10 times
2. Storing Net price is redundant -it is simply the result of the calculation of invoice_line.qty*products.unit_price
3. Obviously, you'll need your joins (customer to invoice_header, invoice_header to invoice_line, invoice_line to products)

But now the fun starts.  Consider these scenarios:
1.  The price of the product changes - you probably don't want the new price reflected in the invoice.  So you might deliberately choose to copy the unit_price from the 'products' table and then hold it on the invoice_line table (introducing data redundancy).
2.  The same goes for tax_rate - that could change as well.
The theorists would probably have you create new tables for product price changes and tax rate changes with 'from' and 'to dates and relate it back to the invoice date.  The pragmatists would probably suggest you initially look-up the 'products' table to get the unit_price and the tax_rate, but then actually hold unit_price and tax_rate on the 'invoice_line' table.
3.  Do you need to know the value of the invoice?  The theorists might have you perform invoice_line.qty * products.unit_price and then sum up the result for all of the lines in the invoice.  The pragmatists might say that it's something you frequently need to know and it takes too long to perform the calculation every time.  So, for the sake of a quick response time for the user, it's okay to deliberately introduce data redundancy and have a field called invoice_header.invoicetotal where the calculation is performed once and then stored. 

These are just a couple of simple issues you need to consider when designing your application. 
Even more important, you might have to consider whether MVD does it easily (with no script) one way, but doing it another way might involve significant complexity and a lot of script writing.   I know the software SHOULDN'T determine the approach you take, but it's a fact of life!

Not sure if any of that helps and, as I said, just my view!

Derek.

Hi Derek,

Wow... There are lots of info. It would take me some time for all that info starting to make better sense - as I learn more. Thanks a lot...

I'm always interested in your ingenious solutions. I have attached a sample blank project with the hope that you'd apply your approach. It may open whole new horizons for me in respect of tables and fields organization, calling in data from different tables, displaying and saving them to different table.

I came to understanding that creating an application beyond basics with MVD scripting is required. This sample project definitely requires some script. I learn by copy paste from you guys. Your scripts help a lot. I really appreciate your kind help.

----------------------------------------------------------------------

Hi JB,

Thanks for the info...

What I could understand from your info, I should create relational field on invoice table for all customer and product fields. However, I am not sure how I could make them work when it comes to pulling data in invoice form and displaying then on invoice table grid then saving. Perhaps you could show on attached sample project by making it work as intended.

Hello Adam,

You're already in good hands with Jean and Derek and, as Derek already stated, you'll get as many different answers as people you ask.

As Derek and Jean also already stated, you need relationships between your tables, you can not just save in a table an invoice, a client and all the products involved in the ordrer.

Attached is what I'd do, very simple schema that can be further improved.
I name the table ORDER because, before being an invoice, that document will probably be a QUOTE and then, if validated by the client, become an INVOICE : that's the reason of the boolean fields "is_quote", "is_ordre" and "is_paid" just to make the difference between what's coming and what's have already been accounted for.

You might also want to had some dates to your scheme, like order date, max payment date and so on.

I'll leave you with that for now. have fun

Mathias

Hi Guys,

Please see the attached sample project. I have done basics. Thanks to Dmitry's kind help with script, I have managed to get customer and product details on inv and inv item windows I think.

1. However, stuck on adding invoice item. On save of inv item it returns error. So I couldn't test the inv window yet.

2 Also I'm not sure how to resolve the issue of calculating totals. I don't know if it's possible to get net total from table net totals column sum, tax total needs to be calculated field and inv total needs net total and tax total.

3. I Tried counter field for simple inv num and product code but it didn't work for me. (I'm assuming it increments +1 with every record)
It'd be much better to use texbox with numbers and prefix letters. Is this possible if so how? Perhaps there is an easier way to assign an auto incremental method for product, inv etc numbering and assigning code.

Please help.......

Hi Adam,
Just had a quick look (rushed for time) and a few issues stand out.  However, the 2 that immediately caught my eye are:
1.  the invoiceitem table needs to be joined to the taxrates table (that should fix the error you're getting when saving the invoice item)
2.  the invoiceitem table needs to be joined to the invoiceheader table (the invoiceline currently doesn't know which invoiceheader it belongs to).
Hope that moves you on a bit.
Derek.

Hello.

I made some changes in your project:

AD1408
Done.

Hi Dmitry,

Once again thanks a lot for the fix...

I wanted add thousand separator for prices. I managed tgrid columns sums but couldn't do calc fields.

What do I need to add the following calc formulas to format them so that they are showing thousand separators beside decimals and right align them on the tgrid.

printf("%.2f",
  (
  SELECT TOTAL(InvoiceItems.unit_price * InvoiceItems.qty * InvoiceItems.tax_rate / 100 + (InvoiceItems.unit_price * InvoiceItems.qty)) FROM InvoiceItems
  WHERE InvoiceItems.id_InvoiceHeader=InvoiceHeader.id
  )
)


printf("%.2f",
  (
  SELECT TOTAL(InvoiceItems.unit_price * InvoiceItems.qty * InvoiceItems.tax_rate / 100) FROM InvoiceItems
  WHERE InvoiceItems.id_InvoiceHeader=InvoiceHeader.id
  )
)


printf("%.2f",
(SELECT TOTAL(InvoiceItems.unit_price * InvoiceItems.qty) FROM InvoiceItems WHERE InvoiceItems.id_InvoiceHeader=InvoiceHeader.id)
)

Thank you very much Dmitry......

Wonderful !!!.

Hello,

please attach a screenshot to show the problem.

Done.

Hi Dimitry,

Sorry to bother you again. I searched with the hope that I could do it myself but no success.

I wanted if invoice header tgrid empty it doesn't save the invoice. Instead it shows error dialog with message 'You must add at least 1 Invoice item to save' When invHeader tgrid is null (no item added or added then deleted) then only option for the user is to cancel without saving.