Calulation To Select a Record When a Target Number is Entered

[Filemakernewbie8]

I need help to see if a calulation would work but I don't know if creating a script is my only way to accomplish this. Here's my dilemma. Each record in my database has 26 individual text fields that contain a number. Then there's another text field called the "target number" that has a number in it.

The question is, how do I create a new calulation field that will select a number out of the 26 individual text fields thats closest to the target number field.

Thank You for Taking the Time In Reading My Question.

[LaRetta]

Those 26 number fields would probably work better if they were related records. I highly recommend pulling them out of the table and creating another table to hold them. Sometimes a solution is so far into design, it would be a major rework ... sometimes it's early enough that it can be done. Where are you at in this design? Can you give us a better picture of what you have? What do those numbers represent?

In any case, if none of the number fields go OVER the target, you could use calculation:

Max ( num1 ; num2 ; num3 ; num4 ... etc )

The result of this calc will be the one number which is the highest amongst them. Why do you use text fields for numbers? Math works fine on text fields but some day it may bite you. And it is confusing to say 'I have numbers which are text fields.' You see? The struggle you have getting information from this setup is just a symptom of a structural problem that you will hit time and again. Any time you have many 'like' fields within a table, it USUALLY indicates they should be split out.

LaRetta

[Filemakernewbie8]

Right now I'm creating the fields so I can make any changes with no problem. The text fields I will change them into number fields after I solve this problem with the 26 fields finding the closest number from the target. Some on the numbers do go over the target number. So the Max or Min function won't work for me. The numbers represent points on a line and when the enduser enters a target number, I want Filemaker to spit out the closest point on the line.

[comment]

I would also suggest that you put the numbers in a related table. Then you can find the closest number by defining 2 (additional) relationships to the related numbers: one relationship (Low) filtered by target ≥ number, the other (High) by target < number. Sort the relationships by number descending (Low) /ascending (High). Then the closest number can be calculated as:

Case (

TargetNumber - Low::Number ≤ High::Number - TargetNumber and Low::Number or not High::Number ;

Low::Number ;

High::Number

)

Note that (a) there's a preference here for the lower boundary in case of equality, and ( this will not work if Number is a text field, because for text 11 < 2 is true.

Doing this with 26 fields in the same record would be much more difficult.

[coconutt2000]

How about using a calculation to calculate the absolute difference between the target number and the numbers in each of the fields and then selecting the field with the smallest difference?

I can't advise as to the best way to do this, but consider using a script that loads the number from a field, performs the calculation to see the difference, saves the number and the difference in variables, then repeats, comparing the relative differences from field to field but only replacing the stored value in the variable that contains the actual number only when it finds a number with a smaller difference. Have it perform this through every field, and then output the last contents of the actual number variable into a field.

That's hardly the best way to do it, and frankly - using a relationship is far more elegant. I've never tried anything like this myself, but it sounds like fun.

[comment]

You can do essentially the same thing without a script, using a couple of repeating fields.

Say the values are in a repeating field. Next, you have a repeating calculation of the absolute differences. The next (non-repeating) calculation returns the minimum difference. Then a repeating field returns the original value/s, but only if the absolute difference equals minimum difference. Finally, the last (non-repeating) calculation returns the minimum value of that (again, with preference for lower in case of equality).