Scenario: Database with over 5,000 companies, each with multiple products.
I want to be able to Sort the database by the number of products.
Need help with:
How can I add a Field with the number of Records for each company?
Assuming that a Scrip will need to 'crawl' through the database (over 100,000 products), using the Value of the Found Set for each company and enter the result of the "Get(FoundCount)" Function into the new Field.
Apologies to all veteran developers for posting here - after all those years f tinkering, I'm still learning...
I would avoid looping in FileMaker at all costs as it's often quite slow -- especially for this many rows.
Instead, why don't you use SQL's "COUNT()" function? As long as you can join your two tables on a key, you can get the count you want (possibly needing a nested query) and, again, using SQL, you can "ORDER BY" to get the results in the order you want.
Maybe something as simple as this (pseudo code). Just an idea to get you started.
SELECT COMPANY, COUNT(PRODUCTS) AS PROD_COUNT FROM COMPANIES C, PRODUCTS P WHERE C.COMPANY_PK = P.PRODUCTS_FK ORDER BY PROD_COUNT
SQL is relatively easy to learn and it's supported by ALL relational database vendors....so learning it is a great investment.
And, perhaps best yet, is that SQL is "declarative"....meaning you don't write code. Instead, you just pass the SQL to the engine (FileMaker in this case) and let FileMaker figure out how to run it.
Now, I've read that SQL in FileMaker is just a wrapper around regular FMP functions so SQL like this might be slow in FileMaker as well, but I'd try it before considering writing a loop statement.
In MySQL, for comparison, I believe a query like this with 100K-200K rows would take a second or two.
If you post more information about your database set up (with a few rows of non private data for each table), I'll try to help you with your query.
I think there are two questions in there probably:
how do you set it initially?
how do you keep it updated?
To set it initially I would just loop and set it based on the relationship you have. A simple count() will do.
If there won't be many products per company and if you won't use the field for reporting then you could just consider an unstored calc. But generally I'd be cautious with those because they often have a tendency to become a performance dog over time.
To keep the count up to to date you could add setting the count as part of the scripted workflow when you add / register a new product for a company. That way the count field remains stored and static, and fast to search and report on.
To add to this, I often have a calculated field in the child table that is an unstored calc that is simply Get ( FoundCount ). With that I can always see the count of records through that relationship, just by grabbing that value from the first related record. It seems that FileMaker doesn't even trigger a download of the entire record for it to work. So there is typically great performance.
The count_product field in the company table could be a field that is set by a script or a calculation field. Heck you could have one of each!
Most of our calculated "count related record" fields use the Last function because they are tied for speed in the browse mode (using an unsorted relationship) and the also work in the find mode.
I use this method - no detriment to DB performance - set up a field in the related table - say z_foundCount - make it an unstored calculation “Get(FoundCount)“ (it is not a problem - Honest) - you can now show the number of related records - note that field does not even need to be in a portal to get a result
Thanks for that!
BTW I think you can only make it an unstored calculation, as the system blocks you from changing this setting.
I tried it but the only result is that after a search, the found set in represented in the new Field as a zero ("0").
No problem. It will work correctly all the time though. My suggestion needs to be an unstored calculation for sure. If it returns “0” as a found count after a search - and you know there are some records - then it is 100% to do with the context. Look at Layout table and make sure that the unstored calculation field is in that context. Sorry if I am not being clear enough.
I tried the Calculation and Summary Field tips that you and others have suggested (Thank YOU!).
Alas, instead of counting the number of Related Records, it gives me the combined value of all values in the related Field; e.g. the value 19669903 is the result of adding up all the 267 values found in the Related ValueField of all CompanyEx's Records!
Without seeing it, I'm not sure what you have done exactly. Attached is a quick demo file. There is a z_FoundCount field in both tables. Notice how the context affects what it shows you. Look at both the People layout, and the State layout.
