• Registered: 2015-12-25
  • Posts: 211

Topic: Inventory and Invoice, how do i start doing this? Any ideas please? :)

hey guys, so my database currently has an invoice "part" that is working fairly well.
But sometimes i have to "manually" check if i have something in stock in order to see if i have to order a part or not.
I want to make a table for inventory, that works in conjuction with the inventory table, so once i add a specific item to the "invoice" sql table, i want the same quantty of items to vanish from the "stock sql table"
Also, i want it to be able to add stock back in case an invoice doesn't go through and gets canceled.

Can i follow an example from anyone so i can take any ideas?

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

Re: Inventory and Invoice, how do i start doing this? Any ideas please? :)

Invoicing with Inventory, etc. can take on various looks and approaches depending on the business or industry using it. It can become quite intricate. It would have been beneficial if your project was included. However, a few weeks ago someone on this forum needed to be able to have stock and non-stock invoice items. I provided my solution to that. So I used that same project and extended it with qty onhand and reorder points for stock items to show how you can update inventory quantities from invoices. It requires sql scripting to update the inventory and I commented the sections to show the various inventory updates depending on the invoice item action.


For new invoice items, the input qty will be subtracted from the onhand qty. If a qty changes, then the onhand qty will be adjusted based on the difference in the qty being changed. If a stock item is changed to another stock item on an invoice item, then the qty onhand is adjusted for the old and new stock items.  If an invoice item is deleted and it is a stock item, then the onhand qty will be increased by the deleted invoice qty.


Hopefully it will help you with your project.

Post's attachments

Attachment icon Invoice_Inventory.zip 607.9 kb, 472 downloads since 2017-08-28 

  • Registered: 2015-12-25
  • Posts: 211

Re: Inventory and Invoice, how do i start doing this? Any ideas please? :)

This works perfectly, did you just build this up in a jiffy so you can show me an example?! yikes yikes

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

Re: Inventory and Invoice, how do i start doing this? Any ideas please? :)

Yes.

  • Registered: 2015-12-25
  • Posts: 211

Re: Inventory and Invoice, how do i start doing this? Any ideas please? :)

wow, you must be an awsome programmer, many thanks! smile

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

Re: Inventory and Invoice, how do i start doing this? Any ideas please? :)

Thank you. I appreciate your compliment. But I consider myself as just average. I've been around a lot of business applications using a lot of development systems so it usually does not take me long to learn a new environment and apply it to a business process. Thanks again. Glad I could help.