• dbk
  • Member
  • Offline
  • Registered: 2016-04-26
  • Posts: 119

Topic: Help with table / database schema invoice application

Hello friends,


I am struggling with a table layout for a simple invoice application.


What i basically want to achieve is:


1) When making an invoice (eg in frm_Invoice) the user can select products from a dropdown box, these products were added through a seperate form (eg frm_AddProducts), have fixed names / prices etc > no problem


2) User can also add products directly in the invoice (frm_Invoice) without first adding these products through frm_AddProducts. For example, the user can fill in the text boxes with a description and a price as he chooses and it will be added to the grid. This doesn't get added to the products table for re-use or stock counting later on.

> This is the part i am struggling with. How can i achieve this? Should i add an extra table for this?


https://s17.postimg.org/pbbqir7fz/schema.png

  • Registered: 2014-11-13
  • Posts: 370

Re: Help with table / database schema invoice application

http://myvisualdatabase.com/forum/viewt … 390#p12390

Domebil

domebil's Website

  • dbk
  • Member
  • Offline
  • Registered: 2016-04-26
  • Posts: 119

Re: Help with table / database schema invoice application

domebil wrote:

http://myvisualdatabase.com/forum/viewt … 390#p12390

Hello, thanks for your reply, but i think this isn't quite what i am looking for

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,674

Re: Help with table / database schema invoice application

Hi DBK,
In its simplest form, I think you only need to add a new field to the invoice_details table to hold the 'non stock item' and create your relationship to the stockitems table as optional.  Then in your input form, simply have a combobox (for stockitems) and an edit field (for nonstocked items).
Obviously, you could do a lot to smarten up your forms and grids (perhaps display both stocked and nonstocked items in the same column etc) but as a basis, I can't see why this sort of approach shouldn't work for you.
Regards,
Derek.

Post's attachments

Attachment icon dbkinvoice.jpg 217.41 kb, 401 downloads since 2017-07-26 

5 Reply by dbk (edited by dbk 2017-07-26 18:17:45)

  • dbk
  • Member
  • Offline
  • Registered: 2016-04-26
  • Posts: 119

Re: Help with table / database schema invoice application

derek wrote:

Hi DBK,
In its simplest form, I think you only need to add a new field to the invoice_details table to hold the 'non stock item' and create your relationship to the stockitems table as optional.  Then in your input form, simply have a combobox (for stockitems) and an edit field (for nonstocked items).
Obviously, you could do a lot to smarten up your forms and grids (perhaps display both stocked and nonstocked items in the same column etc) but as a basis, I can't see why this sort of approach shouldn't work for you.
Regards,
Derek.

Hello Derek,


Yes, this is exactly what i meant. Thank you! Now that i see your solution it is so obvious.

Thank you for this solution, it would have taken me so much longer since i'll admit that i am not the brightest bulb in the box tongue

  • Registered: 2015-11-13
  • Posts: 652

Re: Help with table / database schema invoice application

DBK, Derek,

I threw together a quick project to demonstrate a possible solution for you. First and foremost this project is by no means anywhere close to a productional app. My solution is along the same lines as to what Derek mentioned. I used a slightly different approach than the normal combobox methodology in MVD. The combobox for products is still there in order to make the connection to invoice items, but it is hidden. The lookup is done using a tablegrid instead. So on the invoice detail form the user can either type in a freeform text field or do a lookup into the product table. The lookup will fill in the invoice line item text field and price field from the product table. The invoice detail form will let you know whether you have a stock item or a non-stock item. If you have a stock item assigned to the line, you have the ability through a button to remove the stock item and put in a freeform text description in it's place. Hope it helps. If not, it was good practice for me anyway. LOL.

Post's attachments

Attachment icon Invoice.zip 587.95 kb, 666 downloads since 2017-07-26 

  • dbk
  • Member
  • Offline
  • Registered: 2016-04-26
  • Posts: 119

Re: Help with table / database schema invoice application

ehwagner wrote:

DBK, Derek,

I threw together a quick project to demonstrate a possible solution for you. First and foremost this project is by no means anywhere close to a productional app. My solution is along the same lines as to what Derek mentioned. I used a slightly different approach than the normal combobox methodology in MVD. The combobox for products is still there in order to make the connection to invoice items, but it is hidden. The lookup is done using a tablegrid instead. So on the invoice detail form the user can either type in a freeform text field or do a lookup into the product table. The lookup will fill in the invoice line item text field and price field from the product table. The invoice detail form will let you know whether you have a stock item or a non-stock item. If you have a stock item assigned to the line, you have the ability through a button to remove the stock item and put in a freeform text description in it's place. Hope it helps. If not, it was good practice for me anyway. LOL.

Hello ehwagner,

Wow this is great!! This is actually almost exactly what i was trying to achieve! How did you read my mind? Thank you for your effort, this is really helping me a lot!

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,674

Re: Help with table / database schema invoice application

Hi DBK and EHW,
Also knocked up a quick demo project (see attached) - I clearly have too much time on my hands - LOL! 
I was trying to see how close it could get to the requirement without using any script but in the end I had to give in and add a few lines!!!
Anyway, hope it helps to move things forward for you.
Derek.

EHW - neat way how you toggle between the edit field and the combobox for the stock or non-stock items.  Just one thing - can you explain  what does the Prod_Lookup.TableGrid1.PopupMenu := nil;  line do?  It's not something I've come across before. 
Thanks.

Post's attachments

Attachment icon dbkinvoice.zip 344.27 kb, 771 downloads since 2017-07-27 

  • Registered: 2015-11-13
  • Posts: 652

Re: Help with table / database schema invoice application

Derek (the master of little to no script and I say that warmly)

Prod_Lookup.TableGrid1.PopupMenu := nil disables the right-click popup menu from showing.

10 Reply by dbk

  • dbk
  • Member
  • Offline
  • Registered: 2016-04-26
  • Posts: 119

Re: Help with table / database schema invoice application

derek wrote:

Hi DBK and EHW,
Also knocked up a quick demo project (see attached) - I clearly have too much time on my hands - LOL! 
I was trying to see how close it could get to the requirement without using any script but in the end I had to give in and add a few lines!!!
Anyway, hope it helps to move things forward for you.
Derek.

EHW - neat way how you toggle between the edit field and the combobox for the stock or non-stock items.  Just one thing - can you explain  what does the Prod_Lookup.TableGrid1.PopupMenu := nil;  line do?  It's not something I've come across before. 
Thanks.

Hello derek,

Wow!! This is amazing!! This is really helping me so much, truly appreciate your time and effort. I'm looking into this example you made and figuring it out. This is actually almost exactly what i wanted to make LOL!

You are all so helpful i am really amazed by it. Thank you so much!

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,674

Re: Help with table / database schema invoice application

Hi EHW,
Guilty as charged - LOL!  And thanks for the explanation.
Derek.