Is Filemaker the product that I need to create an application managing a stock of many thousands (up to 100,000 lines) of articles with many thousands of sales (100,000) in a year.  Stock reports would have to be run on a basis of todays stock but also looking into past stock levels on a particular date.  I have tried to make these type of systems before on previous versions FM7 but found that the calculation engine was just to slow for this, has it become faster in the last few years?

 

M

What are you comparing it against?

 

The system that you are describing is highly transactional and should not rely on calculated fields.  It's a common pitfall for beginning/intermediate level developers.  Once something goes in and out of stock, the data is static.  Stock levels need to be updated through a transactional scripting method, not "calculated on the fly".

When you do it this way; then yes, FM is able to handle what you are describing with ease.

Thanks for getting back to me Wim.  What do you mean by a transactional method? I have a table called movements that catalogues the movements of stock,

 

Date of movement:Quantity carried forward+ Returns+ deliveries - sales - exits + any corrections=stock  I have the same formula for the value of stock.  This way I can look up stock between dates.

Transactional = whenever there is a movement (delivery, return, sale,...) that transaction is recorded in its own record and the same scripted process updates the stock level quantity, which is a static number field, not a calculated field.

 

That scripted workflow follows a "transactional model": if any part of the transaction fails (for instance because of a locked record, or some validation rule,...) then the whole transaction is undone: the movement is not recorded and the stock level is not updated.

For more info on the transactional model and how to implement it in FM: http://www.modularfilemaker.org/2013/04/transactions/

 

This way you can avoid all the performance-hog calculations and have a high-volume, very fast AND accurate system.

But you have to avoid calculated fields like the plague to get there.

 

So in that sense it is not FM-the-platform that is not suited for the job, it's the general architecture with calculations that is bogging things down.

Hi Wim:

I have an inventory / purchasing / sales database based on calculations, it works Ok because records are seldom more that a couple of hundred for any particular item.

I have thought about going transactional, but I have a question, what happens if you find a mistake in an old record? With the calculation model, everything gets corrected when you change a quantity on any record, how would that work on a transactional model?

 

Thanks and regards

Carlos

For a mistake you'd add an adjustment record as a new transaction.   The added benefit here is that it is completely transparent that is an adjustment.  In your model and unless you have good audit logging set up the adjustment is lost.

 

In a scripted workflow these adjustments can be reported on and audited.

Thanks for the reply

 

Carlos