Find decimal range

[LaRetta]

I have two global number fields. Our Invoices have standard number TotalDue and standard number BalanceDue. I want to find all invoices who were short-paid by 2%. But, because of rounding of the decimal, not all invoices are EXACTLY 2%, thus the decimal number range search. Some of our large chain stores require extending decimal to 5 places!

I've attached a test file. I decided to use the relationship approach (unless others have better idea). But it bothers me that I couldn't perform the 'Find 2% Range' script.

Data is entered as .0195 and .0295. Range is gPercentFrom & ".." & gPercentTo ] but it doesn't work. For future, how can I find on a decimal number range?

FindPercent.zip

[comment]

That's a very nice quirk you have discovered - certainly worthy of an entry in the "Things not in the manual" section.

Change the ".." (two periods) into "..." (three periods) or "…" (true ellipsis) and you'll be set.

[LaRetta]

Well now, that made me chuckle. I've spent three hours and didn't think of THAT! I've been spoiled using two on everything. Thanks for saving my sanity!

[LaRetta]

It bothered me why this breaks. Originally I figured some FM engineer just forgot to activate the two-periods-are-okay theory on decimals but, while while watching with debugger, the mislogic is apparent.

Using two dots produces .0195...0295 which was then misinterpreted literally as 'true' ellipsis and it was correctly finding records in the range (FM thought) was specified (.0195 to 295). In fact, it should be .0195....0295 so using two periods will only break on decimal if the 'To' number is decimal. By planting the vision of how many periods will be generated, I'll probably never get caught on it again. Surprisingly, it's been there from the beginning (vs. 7 as well).

I find it quite funny and it proves that even great FM engineers can occasionally forget to consider all of the places which might be affected by one simple change they make (allowing two-period ellipsis). We face this issue in our work every day as well. :wink2:

[comment]

It's a combination of two things, each designed to make your life easier, but when you put them together...

You can use either 2 or 3 periods in a range find. And you can write a fraction as ".123" instead of the full "0.123". As a result, when the range is "...123", Filemaker has no way of telling if you meant "to 123" (using 3 periods), or "to 0.123" (using only 2 periods to indicate a range, and another period to indicate a decimal point).

Another way to resolve the ambiguity is to type "0.123" expressly into the global To field - but I don't think this is good practice, because GetAsNumber ("0.123" ) returns ".123".

[LaRetta]

I just realized I have a few scripted finds which accept user input into global numbers for searching a range on dollar amount field. Script provides the ".." between them. It might produce incorrect results. I'm going to change those scripts to use true ellipsis. :blush2:

[comment]

It cannot hurt. But this problem will only come up when:

a) the searched field is a number; AND

: the range end is a positive number between 0 and 1 (excl.).