Fields Updating Automatically HELP!

[cbielich]

Ok here is a long drawn out question but hopefully someone out there can answer it.

 

I have created to files. One is a purchase-ordering database for an online bookstore and the other is an inventory database for that store. I have got everything right were I want it except for one thing that I cannot figure out. In the purchase Ordering file there are several forms such as Order Sheet, Invoice, Mailing Labels, Etc. In the Order sheet all the user information is entered here such as name, address, credit card info, Products ordering, etc… Here is my problem. I am trying to set up some fields which pertain to the quantity of things that they want to order. I have the fields set up but I would like them to do is when a certain number of quantity is entered into the field I want it to automatically update the quantity in the Inventory, that way when the Inventory person looks at the list it is current always. Also is there a way to make it so that the quantity field is not updated until the user specifies so. That way the field does not change as soon and the numbers are entered but in fact change the page is processed, that way there are no mishalfs. Can you help

[Doug]

A VERY CAREFULLY crafted script and a button is probably the best way to go.

 

Remember, it is relatively easy to add up the quantities for a specific part and subtract it from your inventory, BUT, you need to account for changes to a line item, cancellation of a line, returns, etc...

 

I would create a field which shows pending sales (if you want you can show inventory - pending) to show how much of your inventory is "allocated". Then I would have a quantity shipped field which would be the one to actually deduct from inventory.

 

Do you anticipate frequent changes to orders before shipment?

[cbielich]

Well,

I dont anticipate to many changes but you never know. There was something else I wanted to ask but I forgot in the last letter.

I already made the quantity fields doing the exact thing you said. Here was the main problem I was having. I created 5 fields with Product #, Description, Quantity, Price Per Unit, and Total. Now all these fields are going horizontal. I made each of those fields (through the field format option repeat Vertically 8 times. Then the Inventory list (which is a seperate file) has fields were each product information including quantity is put in. Those 8 fields grab the data from that inventory list when entering a certain product number, (hopefully I have not confused you to much by now ) The problem I am having is that since those fields repeat 8 times downward they are all actually the same field. So when I put in a quantity in other field downward it is acting as the same field, So even though I have two different orders with two different quantities it changes all the fields in the inventory list because it is technically the same field. I could create a bunch of different fields for each line instead of reapeting but there has to be an easier way to do this. Maybe I can email you some screen captures to show you what I am talking about. Email me at cbielich@yahoo.com and I will show you. I have to get this going by the end of the week and if you can help. I will get you any beer you want . Help me!!

[Doug]

First, I would strongly recommend using a portal relation instead of repeating fields. Although possible, repeating fields are difficult to work with in this type of scenario.

 

Yes, I would be happy to have a look at your solution. You can email me a clone or compressed copy with some sample data if you wish.