Commit data and update inventory

[AndyCan]

Hi,

I'm going to add another topic on a subject that has come up many times, but having searched the forums i haven't yet found a solution.

I am building a database to manage a business of mine in the bar industry. In our particular case, good inventory management is key to our success.

My relationship graph can be viewed at the following address so as to have a better idea of what i'm trying to achieve:

http://www.chacal.co.uk/Relationships.png

My question has to do with committing data to the database (in this case the sales/saleLines and deliveries/deliveryLines tables) and updating the running total in the inventory table.

Rather than have Sales or Deliveries(Invoices) records updated when i click outside the field, i would like some kind of commit button that would both reduce/increase the running total in the inventory with a respective sale/delivery.

Whilst it may be relatively easy to create an 'update inventory' button and script that updates the running total in the inventory table, i'm not sure how i can make a user click it before adding another delivery or sale. Ie. Some sales and deliveries may be added without clicking on the update inventory button. Therefore i would prefer that the only way records can be added to the database would be by a script that at the same time takes care of changing the running total in the inventory.

Is it possible to prevent filemaker from adding records when i leave a field?

Take for example a sale. A user creates a new sale and sets the date and salesperson. They then start adding lines to the sale via a portal. They add the productID and the quantity, click enter and continue with a new line. At this moment i have both a sale and a fresh sale line added to the database, but the inventory has not been touched. How then do i oblige the user adjust the inventory by clicking a button? The user may decide to go to lunch, there may be a power cut, they may change to another layout without committing the changes or they may simply continue to create a new sale without ever looking at the 'update inventory' button.

In retrospect, the most important thing for me is that the inventory is updated. I'm not that concerned with atomic procedures and half updated records in the case of a power cut.

NB. Whilst it is simple enough to update the inventory with a delivery, a sale must reference the recipes table as one drink may contain different quantities of different products. I haven't tried this yet, but i guess i'll cross that bridge when i come to it!

Thanks for taking the time to read my question... Hopefully someone can point me in the right direction.

Also, having looked at my relationship graph, any comments/criticisms of my database logic would be gratefuly received. Even if just to let me know that i'm going about it the right way!

Andy.

[David Jondreau]

I think this solution is a prime candidate for an anchor-buoy approach. Search the forums for that phrase and check it out.

You goal will be easier if you get a copy of 9 Advanced which allows you to create custom menus. That way you can script new record creation. Something simple that checks the last order enter and confirms that the inventory was confirmed after that. (Your "confirm" script should set a flag indicated the sale has been confirmed and the "new sale" script can check that field).

Of course, if someone enters a sale and doesn't confirm it, then inventory will be incorrect until the enter a new sale, which may or may not be critical.

Without 9 Advanced, you can create a "New Sale" button and force the users to use it by either limiting users to "Editing menus" only or prohibiting the creation of records in the Sale table via Access Privileges. The New Sale script would run with Full Access privileges and run your confirm sale script if necessary.