How BIG and COMPLEX can FM7 handle?

[onefish]

Thought I might just ask this question to some of you that have a fair bit more experience than me with FM. I am coming to the point of completion on a solution I have been working on for some time. It has turned out to be an absolute monstrosity though. About 60 odd actual tables, literally thousands of fields and even more relationships (the gragh is almost impossible to make sense of). Hundreds of value lists, and I have to scroll down for 30 seconds or so to choose a layout from the bottom of the list. I am preparing the reports now and have worked out that the only way to produce one of the reports is to create a further 338 calculation fields, 169 relationships between the same 2 tables, 21 self join relationships in one of the tables and 169 value lists. This is a couple days work and I guess I want to put my mind at ease that FM will still perform when there are hundreds of thousands of records throughout the tables.

How BIG and COMPLEX will FM handle?

[Ocean West]

You may consider the creating another file for the "reports" function you seek and only create the necessary TO's in that file as need all referenced to your file. Use this to generate your reports.

This separate file could contain layouts that would be needed and necessary scripting and printing your reports.

[Ender]

I agree with Stephen, that a report file might be a good way to go. I do have some concerns about your estimated structural needs for your reports:

Reports usually don't require very many additional fields or relationships, and I don't ever recall adding a value list just for a report. Can you give an example of why so many of these are necessary?

A note about your one-file design: While this is supported in FM7, it may not be optimal. In my opinion, it's better to design a large system like you described by putting groups of tables into separate files, building modules that are mostly self-contained. This way the relationship graph, value lists, layouts, and scripts only show those items that pertain to the current module. Another advantage of modularity like this, is being able to upgrade whole modules at once without necessarily having to bring down the whole system. Also, in the event of a module upgrade, you may only need to import data from the 5-10 tables from the one module rather than all 60 tables.

As far as how your solution will perform with hundreds of thousands of records, well, that's hard to guess at. Performance depends on the efficiency of your design and amount of data having to cross the network. Certainly there are things may run fine with a few records, but end up being too slow with lots of records or lots of users. It is also common to run into record locking issues when moving a solution to a shared environment for the first time.

[onefish]

I haven't had to create additional fields, relationships and value lists for reports before either but this time I cannot see an acceptable alternative. It is due to the layout requirements of the report. The 169 valuelist contents will be shown in 169 of the calculation fields and placed in the report in various areas. The report is a survey form that must show any outstanding items that exist in the database so they are not logged again, directly below a row of blank fields for entering new data for that relationship. The tricky bit is that the blank fields (drawn grid actually) must be shown at all times. There are 169 of these on the form and being intended for field use it must be kept as small and easy to manage as possible so that means the records need to be organised in columns and not just horizontal rows. I mucked about with the label report layout but I could not get it to do what I needed. Unless there is a way to have more than one body in a report I can't see another way. I'll attach a MS Word copy of the form that I am working with in a couple hours when I get to my other computer and maybe you can advise of a better way.

[onefish]

PDF form attached. The form uses the same relational structure as the database. There are 21 possible areas of a house. Only areas related to the asset are shown in the drop down box for data entry and will also be shown on the survey form for the asset. I anticipate achieving this with a portal that will slide for printing when the area is not related to the asset. There are 45 possible parts of a house. Only the parts that are related to the area are shown in the dropdown box for data entry and also on the survey form. There are 1282 possible maintenance problems for the asset. Only problems related to the part and area are shown in the dropdown box for data entry and also in the legend (not attached) that the surveyor uses in the field. So, the surveyor looks up in the legend area = kitchen & part = door and there are only about 12 of the 1282 items to choose from. He/she then writes the problem ID number (corresponding in the database in the code field for that area and part on the survey form.

Data entry is done the same way in the database. Dropdown box 1 - possible communities, dropdown 2

ICHS_Forms.zip

[onefish]

Well, I think I have just answered my own question. Filemaker will handle it if you have plenty of time to sit around and watch the little hourglass. I just finished off the relationships and they slowed the system down too much to be bearable. And that

[iflurker]

From what you've written here I don't see that your database is doing anything especially complicated. It sounds like you are always very eager to add gazillions of fields, layouts, etc whenever you run into a problem. That's always an easy if tedious solution to everything, but if you were more reluctant to keep throwing more fields into your database I suspect you would find a more clever way to do things. I've never dreamed of anything like adding 169 relationships between the same two tables (!?). Filemaker was certainly never intended to be used in this manner, and the Filemaker developers were careful to add in enough advanced database features to almost always provide a simpler way to do things whenever the temptation arises. So in my experience that kind of thing tends to indicate deep design flaws.

You haven't yet told us how the list of areas, parts, maintenance problems are stored in your database; it's possible a poor choice of storage method for these is at the root of your problems. It seems clear to me given your needs that each type should be a record in a small special-purpose table called AreaType, PartType, MaintenanceType or whatever. PartType would contain basically only the part name string and a foreign key called AREA_ID, MaintenanceType would contain the maintenance name and a foreign key called PART_ID.

Now for your data entry layout, instead of value-lists and dropdown boxes you can use some selection portals (that's a portal with a hidden button behind it that sets a field --- usually global but not in this case --- to the portal row's primary key). The script of each selection portal would set the current record's appropriate field to the primary key of the portal row (the name is not copied but grabbed via relationship), and refresh the screen to make only the related records of the next portals appear. So I see a need for only roughly 4 relationships to solve this problem. It seems to me also that right now (although I haven't tested this) your users probably have to click on the layout background to commit the records and refresh the other value lists, and the selection portal would solve that problem too.

As for your report... The ability to have vertical as well as horizontal parts is a missing feature from FM7 so any attempt to have multi-column reports is bound to be an ugly hack. But I don't think having 169 sliding portals (if I understand your plan correctly) with value lists is the best way to do it. Consider adding a new special-purpose table for this layout, each record of which contains two foreign keys, one for each column. Then you can use a list view layout displaying records from this table, and have the generate-report script loop through all the records of the found set of PartType etc records, creating one record of the special-purpose table for each 2 records of the PartType records. That would let you have the columnar report and have the blank grid "fields" since you could tell the script to keep generate more "fake" empty records until you reached a given number. This script will be a little slow but no more than a few seconds I think. It has the disadvantage that only one user can view the report at a time; I'm not sure how much of a problem that is for you.

Well, I don't know the whole story of your database, but tell me if you think the above solutions are reasonable.