First off thanks for reading the post. A while back I created a simple invoice system for the family retail business. The system is not designed to handle each transaction, rather it is intended to handle special orders and mail order items. I have learned about a few pitfalls in my original database and now I am looking to redesign the database with some more room to grow.

In this solution I am looking to create a few tables including Customer, Inventory, Invoice, Distributor, and Line Item. I still intend for this program to not be used as the primary transaction solution for the retail operation. The business has an very large inventory (say 10,000 items). Many of these items already exist in a table that I have been compiling, but many new items get added on a daily basis and many items I will not have the time to catalogue.

I would like to set up relationships between the tables so that when a salesperson creates a special order, s/he may have access to existing inventory items and be able to submit new items to the existing database. The idea is that if it doesn't already exist in the inventory list, one will be able to populate the inventory list through creating new invoices.

It is quite possible that I am going about this all the wrong way. Any ideas or tried and true file examples would be greatly appreciated.

Thanks in advance for any help.

The idea is that if it doesn't already exist in the inventory list, one will be able to populate the inventory list through creating new invoices.

How will the user know if the item is already in the inventory list? Won't they have to go to the inventory list anyways to lookup the product number? Or do you work with UPC codes?

If you go to the products database and it's not there, then you can simply create a new record.

If you're using UPC codes and you scan the item into the invoice and it doesn't show up, I'd set up a button on the line item to "add product".

There's several ways you could go about this, but here's what comes off the top of my head...

Go to related record (show only related)

Perform script, external (line items), "prepare to add a new product"

Refresh screen (bring to front)





Line items script: "Prepare to add a new product"

Set field, globalUPC, lineUPC

Perform script, external (inventory list), "add a new product from a line item"





Inventory List script: "add a new product from a line item"

enter browse mode

new record

set field, productUPC, lineitems::globalUPC (this will be a constant relationship)

refresh screen (bring to front)

display message, "please enter additional information then hit "enter""

pause/resume

Perform script, external (line items), "refresh line item"





Line items script: "Refresh line item"

Relookup (lineUPC)

 

...note that this last script could be combined with the previous script in line items. I separated them for simplicity. This last step will force the line item to relookup the rest of the information based on the UPC relationship (description, price, etc.)

Hope that helps!