Get (foundcount) calc field to count related records + bonus discussion about the ETS program

Continuing the discussion from Counting related records for a large data set:

@jormond I know this can give great performance, but at the same time, I have steered away from using this technique, considering it a bit of a hack and fearing it would stop working in a new release of the product.

Revisiting how I count related records in all the systems I support is not something I want to be facing.

What is your take on that?

Also can you confirm this works in a data separation model?


1 Like
  1. Separation model. Yes. I use separation almost exclusively.
  2. Possibility for change. Hmmm, I can't think of a reason why this would stop working, except for a terrible bug. But that possibility exists with any functionality. I've used this method for a long time. A field for Get ( FoundCount ) and Get ( RecordNumber ) is in every table. They both are very context sensitive functions. I use them with zero fear they are going to change. At it's worst, the optimization gets broken, and then it's as slow as Count ( relatedField ) or Last ( relatedField ) or whatever other technique one would use.
1 Like

Every function I fear change for, I tend to wrap in a CF (there are not many to be honest).

This specific case is not really a candidate for that, because you can't easily replace something like SetVariable "TO::Field" by SetVariable "CF(TO::Field)"

Copy XML, Find/Replace, Convert back to FM Clipboard, and paste. :slight_smile:

That would mean a lot of copy/paste. Targeting different TOs all the time would also make the pattern a bit harder to use in a brute Find/Replace. Sounds like I would need to get a bit more fancy about the Find/Replace pattern. I like learning new stuff, but don't like it when it involves applying it to stuff that I will want to automate (even if in this case I'm pretty sure I would never automate stuff like that).

This is an area that I've been working on handling more.

  • building unit tests for the many common and edge-cases. So when I make a change, I can see if it works on the unit tests. I can then make changes with little to no fear, for things I can either fully automate, or changes I have limited control over knowing all the changes. This is still a work in progress.
  • Some things I need to be able to inspect the results. So a manual paste works very well. If it's something I could reuse, I try to extract as much of it as I can into smaller steps that are reusable. Converting comments to Set Variable steps, and also at the same time generating Set Fields, means 80% of the scripts/code is already written for me.

An interesting historical tangent/tidbit:

Regarding this common usage: Sum( RelatedField ) or Count( RelatedField )

I first saw that pattern in, I believe, sample databases that shipped with FileMaker v.4.

I remember looking at that, and thinking it was crazy, and mystical, and I questioned that it would even work.

Prior to that, in v.3, the technique that I had seen and learned involved reaching across a relationship to, IIRC, a summary field which was defined in the related file. In other words, the arrangement was actually a bit more similar to the approach that @jormond mentioned, whereby one looks across the relationship to a field defined in the child/related table. I don't believe that at that time I had the notion of defining that field in the child table to something like Get( FoundCount ) -- actually, I can not remember if Get( FoundCount ) was available in v.3.

From time to time I recall this, and it makes me smile to think about how (at least for me), the tried and true pattern flipped around, as it did...

1 Like

A couple of thoughts:

  1. in a wider context, this is exactly why we need people to be more involved in ETS. It takes a (considerable) effort but if your livelihood depends to some extent on how well you know the platform then why not invest time in shaping its future AND knowing what is around the corner?
    Instead of being afraid of possible breakage we can show the Claris engineers how their features are being used.

  2. "premature optimization is the root of all evil". Trying to optimize your code for an unknown thing int he future may lead to living with slowdowns right now. Food for thought :slight_smile:


Thanks @WimDecorte for the thoughts you shared.

  1. If ETS is more open than in the past, going from invite only to public "here is the form to apply", I'll be happy to consider it. I did apply for the Claris Connect ETS and never heard back. Maybe Claris can improve on the communication side of the ETS? In the case of FM19, I might have been able to contribute to some extent, but my commitment is likely to go up and down as other stuff happens. I am not in a position where I can see myself dedicate a set amount of time to this. Tracking ongoing reports and making sure I am not reporting a duplicate is also not as easy as I would like. In all honesty, with the recent release, I was not even able to find the time to install the pre-release software that was issued a month before the official release. Please do not get me wrong, I LOVE being part of the ETS, I just don't want to be surfing it, I want to contribute. I recall finding a bug in the version of ETS where editing multiple script was first introduced and that felt good (it was a window glitch: mimizing a window, closing it and editing the same script would summon the window minimized instead of restoring its original dimensions).

  2. I will agree premature optimization is plain bad. That said, my avoidance of using get(foundcount) in a field and use it as a replacement of using the cout() function can hardly be considered "premature optimization". If anything, my strategy indicates I'm going for stability, at the price of performance. The help documentation (provided below for each function for the reader's convenience) makes it pretty clear which of the 2 function is meant to count stuff and which is returns "the number of records in the current found set". So event if get(foundcount) outperforms count()for this specific application ( disregarding using count on a repeating field or on multiple arguments separated by semicolons ), the documentation leads me to label that technique as a hack. If we were to line up 1000 solutions, made by pro devs or citizen devs, I am ready to bet the use of get(foundcount) applied this way ends up anecdotal (would it be present in 5% of the solutions? Your guess is as good as mine). Furthermore, if get(foundcount) systematically outperforms count() for this specific pattern, I don't believe it is my job to start changing how I code to get to the result I seek. Rather, it is a job for Claris engineers to discern that pattern where count is calling a single related field and leverage, for that specific use case, the most resource efficient way to get to that result (something everyone seems to agree exists here). Asking 95% (I'll settle for 50%+1 if 95% is too high), asking 95% of devs to code a different way to achieve optimization is not what I consider a good approach.

I added some notes the the results page (posted earlier) that compares Count, FoundCount, and Last.

One additional thing to consider is that there are advantages to having your "count" field local to the table as those fields can be used elsewhere in your code.

Well during the November Roadmap presentation - different than before and much useless - they told that everybody could be part of ETS. I applied . . . an never heard back. In French we say "les bottines doivent suivre les babines" which is explained as the actions must follow the words. Looks like that was not the case with last ETS.

@WimDecorte wrote:

in a wider context, this is exactly why we need people to be more involved in ETS.

But it didn't happen. I know what ETS is, I have been involved in for the upcoming V 17 at the time. If more people were involve in ETS more bugs would get discovered.

  1. The whole process, while still under NDA, is much more collaborative that I feel it has been in the past.

  2. I think Claris understands that people have other things to do. If you have zero time ever, then don't apply. However, if you can spend time testing and reporting issues, do it. The more quality testing we do, the more improvement we will see on Day 1 releases. This is one of those things, it's built into my schedule, and you have to have a process to isolate the builds.

  3. Don't use the Claris Connect ETS as a sample of how it works. Keep in mind it was a brand new product, with a new team, an entirely new process. While we would all like to think that's easy, it's not, at all.

@Bobino expressed the same sentiment. Just like our lives go up and down, so does Claris'. Not making excuses for them clearly because I've been equally frustrated with how ETS has ran over the many versions.
But at the end of day: don't give up just because it's hard to navigate and they don't communicate back the way we'd like it. I can't stress enough how absolutely critical it is to get involved. We have a chance to do it and they do listen, even if it sometimes feels like pulling teeth.

Talk about it at every opportunity, with every Claris staff member you encounter. What is they say: "You don't fail until you give up".


I totally get it, that is true for everyone. But to push a little on this: it's a matter of setting priorities. And those too will differ from person to person.
Very early on, I recognized both the opportunity and the importance to my own career and well-being for getting as close as I could, so that I am well armed - and ultimately have less stress about the future - to keep evaluating whether I'm going in the right direction.
Tactical vs. Strategic; and yet both need to happen.


Emphasis is mine.

For V 19 ETS, we had to send an email to someone, what if that person doesn't read his email, and do not read all of them because he is busy. I think that applications for ETS should proceed through a form and these forms should be handle by someone who will make sure they get the attention needed. This is communication 101.

Speaking of communication, I remember a few years ago when FMI changed licensing for the first time. A lot of people were having a hard time understanding the new licensing - communication - even people with FMI, I was told that by a FMI employee.

It's not that I want to rant, but communication seems an issue at FMI/Claris. I still remembrer the documentation that was sent about ETS where the functionalities to test were described, some notes were clearly expressed, but some others were in no way clear. For a proper ETS, things need to be correctly communicated.

Still on the matter communication, we were told that FDS will be replaced, and we still don't know what's coming. It's hard to follow Claris when they don't themselves where they are heading.

Sorry, but that needs to be said.

1 Like

3 posts were split to a new topic: Communication & Perception

That's too easy and too dismissive. I do believe they know where they are heading and they are constantly adjusting just like we all are. But knowing what your strategy is and and executing on it are two different things. I think it is clear that the major turnovers in the last 12-18 months have led to poor communications and "death by a 1,000 cuts" sentiments in significant parts of the community.

I totally hear you on the rant; a lot of it needs to be said and more importantly, directly to them. Just saying it here in these forums isn't going to get sufficient traction. And of course it only works if it is brought forward in a constructive fashion with ideas and options instead of "don't do this and don't do that". Which unfortunately also takes the most effort and time.


Thinking a bit more about the count() vs get(foundcount) thing, I suspect Claris could not optimize for the scenario of using count() on a related field, because the function is meant to disregard blank values.

So it sounds like to get anything optimized for that use case would require a new calc function, one that would count records no matter if the target field is blank or not for some records.

The more I think about it, given how frequent that specific use case is, the more I am surprised there is not a dedicated function for that.

  1. count() is slow.

  2. get(foundcount) does NOT work in the find mode

  3. We use the Last function technique as documented by Claris...

"...If field specifies a related field, then it returns the last non-blank value in the related set."

The "Last method" (properly implemented) seems to be ~2.5% slower than get(foundcount) in the Browse mode and in the Find mode it is infinitely faster (works vs. does NOT work).

What is the use-case for needing it to work in Find Mode?