Limitations of Filemaker

[genious]

I am evaluating Filemaker for a project. I need to know when will the likes of a sum function start to slow things down on a report.

If I have a reports with up to 30,000 sale lines in a year this would require several calculations to summarise each of these eg sum of qty, Price, Tax etc. what is performance going to be like? If these numbers start to rise, what will happen to performance then?

[gdurniak]

Unfortunately, it's best just to create the 30,000 records and try it

FileMaker is not designed for "number crunching" so sums tend to be sluggish

it helps if the summed fields are "stored" values

[LaRetta]

when will the likes of a sum function start to slow things down on a report.

The Sum() function or a summary field? They are very different. One would use a summary field for reporting (placed in sub-summary parts).

You would only want these summary fields on the report layout itself. It is best to use a null layout (no fields) to perform the find for the report records and then sort the records by the break field (field defined for the 'sub-summarize by' specification in the report layout and THEN switch to the report layout. But I've summarized several fields through 100,000 records and it only takes 3-4 seconds to render. But no, it won't be as fast as MySQL, for instance.

[bruceR]

Like most anything - it depends. Basically you're dealing with quite reasonable numbers, as has been mentioned by others. There are ways to do this task inefficiently and convince yourself that FileMaker can't handle it. But there are people routinely reporting on FAR larger data sets with good performance. But they know what they're doing.

[Lee Smith]

Is this a slam by a Access User?

[genious]

Thanks for all the replys. First things first, Access is not on the agenda, it is the best sales tool that Filemaker have in my opinion!

I keep a growing inventory database. So I will have many sum() functions used on a stock lines form and, as I say I have one line that is 25% to 30% of all sales lines. I will of course also be using a lot of summary functions on a lot of reports.

I will also want to be able to create a dashboard summarising turnover performance this year and last year. I also know that once I give them the overall turnover I will be asked in the next breath to also include salesperson peformance on the same dashboard and then best performing stock. In other words I will have to create a lot of calculations over the Sales lines.

If it is slow to load it will be the deccision makers that will get annoyed. As for not creating the sum()'s and summaries right, well I used to do a lot with FM5 but havn't gotten as up to speed with FM11 and it's differences although I am learning quickly. When I used FM5 I only felt that I just needed to understand the problem in order to give the solution.

If I had to think of putting in a mysql backend, is there some ways to learn how? I have absolutlely no experience with sql.

[eos]

My advice: create a dedicated table with stored fields for this purpose, and for each transaction, update the respective field.

e.g. create a table where each record is a combination of salesperson and sales quarter. With every transaction for this particular combination, update the fields for this record (which can include revenue, no. of individual customers or whatever you like). If the combination doesn't exist yet (new sales person or new quarter), create a new record for it etc.

Make it as granular as you need in your business on a regular basis. Since all the basic info is preserved in the original table, you can still do any kind summary on these data, albeit this is the "old-fashioned" slow solution. But for all reports you need to produce on a regular basis, use these derived data, or a combination thereof. (Need a summary of sales person per year? You simply have to add the fields in his/her four quarterly records …)

This way you have a very small performance hit with each transaction, but you avoid the big one associated with "number crunching". All you need is one new table, one new relationship and one new script, and you have to update your interface and/or reports to use the new fields, but that's it. Can be done on one afternoon, and it really pays off.

Edited September 21, 2010 by Guest

[bcooney]

FM is not billed as a MySql front-end. But, you can start here: http://www.filemaker.com/support/technologies/mysql.html

However, if you want to optimize performance, you can, as suggested by Gregory, use stored values in your summary calcs.

Here's a simple explanation. I have Parts, and PartsSold, related by PartID. To calc QtySold, I can create a calc field in Parts = sum ( parts_PartsSold:Qty). However, this an unstored calc.

I could (additionally) set a QtySold_static field in Parts when an Invoice is generated to the unstored calc. Now I have a stored QtySold in Parts, which I'll use in reports.

hth,

Barbara

[genious]

Ok this is what I will do. The Stored Calculations table is probably the way to go. It really is not that different to what I used to do when I worked on FM5 in the past. Just a little easier.

[LaRetta]

We don't use FM as a MySQL front-end but we DO use MySQL for large data crunching; hybrid system. It is simply a thousand times faster than FM and when working with large data-sets (as we do), FM is simply too slow. Nothing says one must use FM exclusively. :wink2: