10 Replies Latest reply on Apr 15, 2015 3:02 PM by philmodjunk

# Generate Sorted List from Field Values

### Title

Generate Sorted List from Field Values

### Post

I have fields h0, h1, h2, h3, h4, h5, h6, h7, l0, l1, l2, l3, l4, l5, l6, l7, l8. Total - 16 fields with values. Those numbers are similar, so I want to get 3 most similar number pairs (or there may be 3 very similar numbers) out of those 16. I propose my own thought process of how can this be achieved (just don't know how to implement it in Filemaker). If you know any easier way - let me know, it doesn't matter how, it only matters that it is accomplished. so Let's start:

1) I would like to make a list of all those h0, h1, etc. values and sort it from the largest to smallest. I will add Excel screenshot for explanation.

2) Then I would like to 'compare' those numbers (1st with 2nd, 2nd with 3rd, 3rd with 4th adding and subtracting 0,11% and if, as you see in example picture 7th number's lower part (with -0,11%) is lower than 8th number's higher part (with +0,11%) or 41,23 < 41,30, then the pair is considered similar.

3) List the smaller number of the pair for the output, space delimited, in my example, output should be: "42,74 41,25 40,68"

My highest issue here is get those 16 numbers to the list and sort. So how could I do that?

• ###### 1. Re: Generate Sorted List from Field Values

I gather that 42, 79 represents two values in the same field separated by a comma?

I would like to make a list of all those h0, h1, etc. values and sort it from the largest to smallest.

What "it" are you sorting? What is the significance of Columns B, C and D--which all seem to store similar data...

• ###### 2. Re: Generate Sorted List from Field Values

A is the number field title, if I added database name, in filemaker it would be like: calc::h0, calc::h1, calc::h2, etc.
B is the actual number, so in this example, h0 = 46,31; h1 = 46,01, etc.
C is B number * 1,0011 (that is +0,11% deviation from B number)
D is B number / 1,0011 (that is -0,11% deviation from B number)

And that similar data is actually the case. C and D columns helps me find out if the numbers in the sequence are similar. For example, 41,25 and 41,28 are similar, because there's less than +/- 0,11% deviation between them. And if you take 1,25 and 1,28 - they, according to such rule, are not similar, because if you add up 0,11% to 1,25 you will still not get more than what you get when you subtract 0,11% from 1,28.

I may just have A (field title) and B columns (field's result) in Filemaker without C and D columns, I just made an example in Excel to show the logic.

• ###### 3. Re: Generate Sorted List from Field Values

let's start with generating sorted list. If you have an idea how to populate 16 field data into sorted list - it would be highly appreciated.

• ###### 4. Re: Generate Sorted List from Field Values

I see that I forgot that the comma is used as the decimal point outside the US.

If I understand you correctly, you want to sort values stored in 16 different fields into a sorted list. Best way I can think of is to move those 16 values into 16 different records where a sorted relationship, executeSQL, or Sort Records can then be used to put the values into order. Note that is is possible to move data into such a set of records, sort them and then copy them back into a field in order to produce the sorted list.

• ###### 5. Re: Generate Sorted List from Field Values

but that would need a script and I need it to be in a calculation format.

Because what it is - those 16 fields of h0, h1 I will have for every record, and records may be hundreds and thousands and I want to automate it as much as possible, without scripts running in the background.

So how to do that in calculation field? (Except for that checking, like if h0 > h1, then h0 & " " & h1, else h1 & " " & h0", because it would be way too long to check all possible scenarios)

• ###### 6. Re: Generate Sorted List from Field Values

not necessarily as you might have your field in individual records to begin with. Moving them in and out of a set of related records is just a possible option. And you'd only need to run this script once unless one of the values in the set changes.

And any alternative method for sorting 16 different fields rather than records that I can think of would also use a script to sort the values, but would do so much more slowly.

• ###### 7. Re: Generate Sorted List from Field Values

Could you explain the 1st method or maybe even write a script here?

• ###### 8. Re: Generate Sorted List from Field Values

and again I found the custom functions on briandunning BubbleSort_SlideValue and BubbleSort, or maybe even better - non-dependant custom function formula for sort, from which we could derive a calculation to sort records. I wouldn't like to use it as a custom fuction, I prefer having calculations for the particular field.

• ###### 9. Re: Generate Sorted List from Field Values

And BubbleSort is the name of the simplest, oldest algorithm for sorting values. DB's don't use it as the time required to sort objects with this algorthm is directly proportional to the Square of the number of items. For sorting 16 items, that shouldn't be too awful--and maybe much faster than a script that moves (copies) data to related records. But I have an idea that might use a relationship and no sorting  scripting or custom functions. Let me see what a test case with just a few values does...

• ###### 10. Re: Generate Sorted List from Field Values

I'm not sure that is idea is better than a custom function but you can take a look at it and decide for yourself:

https://dl.dropboxusercontent.com/u/78737945/FieldSortingDemo.fmp12