Calculating single value from multiple databases/records

[Ben Martin]

Hi there,

Im relatively new to filemaker pro. Im creating a database to take and process orders, and i have most of the database in place part from one thing i cant seem to fix;

The database has a stock list, with each item defined by product code, and a stock level (quantity). What i need to happen, is for the Quantity in stock to be calculated based on the quantity of that product that has been processed in the purchase orders. For example;

Purchase order 1 - product 000001, quantity 10

Purchase order 2 - product 000001, quantity 15

Stock level = current stock level - purchase order 1 - purchase order 2, based on the product code (000001 in this example).

I had a calculation setup where by;

Stock level = base quantity - purchase orders:product quantity.

This worked fine, for the one record visible on the page, it didn't take the other records available on the database into account. Is there anyway i can have the stock database updating from all records on my purchase orders database? Thanks in advance, Ben.

[mr_vodka]

You have to add all the purchase order qty together.

Something like. base quantity - Sum (purchase orders:product quantity).

However, you may want to think about how your process will work. What about accomodations for orders but not complete. IOw at what point in your process does it go from a "hold" to a deduction from your inventory supply. There are a few posts on here addressing this issue.

Also, how will you accommodate for your resupply. It would be very easy just to have a number you type in for stock and subtract all the qty sold from it. But then what about when you get more supply? you would have to have a supply table that tracks all your reorder supply.

Then it could be the sum of all the supply ordered for the product and subtract the sum of all completed "sold" qty.

[Ben Martin]

Hi there,

Thanks for your reply. I've literally just set up the calculation with Sum in it, and it appeared to fix the problem of only adding the current page. However i have had the issue of restocking the supplies, and im trying to decide a suitable, and simple solution at the moment.

The idea of a restock table works, but over time, and this database could be used for many years, some items would end up with about 300 repititions.

I quite liked the idea of having a button that can 'Add' 'x' amount to the current stock, but i cant seem to find a way to do this. For example;

Current stock 101. Button = Add (x) amount.

Clicking the button takes the typed amount and adds it to current stock. That can work find but it seems each time i change the amount, the Current stock level deducts the previous added amount before adding the new.

For instance, current stock = 100. I add 10 which = 110. Next update i type 20 into the add 'x' box, but the calculation appears to minus the 10 before adding the 20. It doesnt accumulate. Any easy way around this? Thanks again, and i apologise if that didn't make a great deal of sense.

Edited March 15, 2007 by Guest

[mr_vodka]

The idea of a restock table works, but over time, and this database could be used for many years, some items would end up with about 300 repititions.

Personally, IMO it makes more sense to have a history of all restocked orders.