Recently I needed to produce a large range of consecutive numbers. FileMaker doesn’t provide a built-in function for this but it’s easy enough to accomplish, for example, using the While function or a recursive custom function. In this case, I decided to go a different route based on a tip I’d seen someone post years ago (I’ve forgotten whom so cannot give proper credit) that you can leverage a couple of FileMaker’s JSON functions to facilitate this task, with the advantages being blazingly fast performance and an opportunity to think outside the box.
Demo file: fastrange-cf-v1.04.zip
THE CORE CONCEPT
As you probably know, when you push a value into a new array using JSONSetElement, if the address is greater than zero, the array will be created with nulls leading up to the address you specified…
…and if you point JSONListKeys at the preceding you will get a list of numbers like so:
And from here you can easily massage the result, for example:
Unfortunately life wasn’t that simple in my case, because I wanted to be able to accommodate negative as well as positive numbers, which brings us to…
THE CUSTOM FUNCTION
The challenge was how to deal with negative values, ideally leveraging the same concepts that worked so well to produce positive ranges. What I needed was a way to produce a “mirror image” so to speak of a positive range, something like this:
And it turns out that the SortValues function provides exactly the “looking glass” I’d been hoping for, enabling the CF to process the negative portion of a range as if it were positive and then reverse the sequence using SortValues ( ______ ; -2 ) which tells FileMaker to treat the values in the list as numbers and sort them in descending order. From there it’s just a matter of prepending minus signs via simple substitution.
For more info re: SortValues see the Claris help page, or this article: FM 16: SortValues and UniqueValues.
Wow. Amazing. Thanks for sharing. Learned 2 things I didn’t know previously besides the customF you developed. Surely I will be using these 2 pieces of knowledge down the road. 👍 Many thanks.
I’d like to echo this. Innovative and amazing.
Hi Kevin,
The tiniest detail: count should probably be “end – begin + 1”
Thanks Ethan. Demo and screen shot have been updated. Really appreciate you pointing that out.
Question- why do you have the If statement checking for IsEmpty (sep) for both the pos and neg sections, when you’ve already set sep to be a line break at the beginning of the Let() statement?
Very cool technique, though, and far more performant than other methods I can think of.
Hi Tom, thank you for pointing that out. Clearly an oversight on my part. Demo and screen shot have been updated accordingly.
below function is better customer list
－－－－
Evaluate(
Substitute(
Substitute( FastRange( – 10000; – 1; “”); “-“; “Let ([ CLNum = “);
“¶”;
“] ; GetNthRecord ( field ; CLNum )) & Char ( 13 ) & ”
) & “] ; GetNthRecord ( field ; CLNum ))”
)
－－－－
CustomList ( 1; 10000; “GetNthRecord ( Field ; [n])”)