Jump to content

Find decimal range


LaRetta
 Share

This topic is 5702 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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".

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

This topic is 5702 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.