12 Replies Latest reply on Aug 6, 2014 11:59 AM by fmpro_novice

# Help with a script

### Title

Help with a script

### Post

For each record, I have a field called Place and a field called Total.

The Total field is sorted by Ascending order, and the Place field is a result of the sort order. Ex:

PLACE        TOTAL

1                     65
2                     66
3                     67
4                     68
5                     69
6                     70
7                     71

If the Total fields are all different it is simple to create the Place field. I simply use "Replace by Serial." BUT, if some of the Total fields happen to be the same, then the Place results need to be quite different. Ex:

1T                   65
1T                   65
3                      66
4                      67
5T                    68
5T                    68
7                      69

For both of these examples, the starting number is always a 1 or if a tie, 1T and the final number is equal to the total number of records - unless - in a very rare instance - all the Totals would happen to be the same. In this instance, the result would be. Ex:          (Example two is the most normal of all scenarios.)

1T                 65
1T                 65
1T                 65
1T                 65
1T                 65
1T                 65

Does anyone have any idea what a script would look like to accomplish this? If this looks familiar, I posted this a while back, but the script I ended up with did not work.

• ###### 1. Re: Help with a script

I think that an UNSTORED calculation can do the job:

Let([
\$condition1 = GetNthRecord ( total ; Get ( RecordNumber ) - 1 ) = total  ;
\$condition2 = GetNthRecord ( total ; Get ( RecordNumber ) + 1 ) = total
];
Case(
\$condition1 ; GetNthRecord ( place ; Get ( RecordNumber ) - 1 )  ;
Get ( RecordNumber )
)
& If ( \$condition2 ; "T" ) & Let ([ \$condition1 = "" ; \$condition2 = "" ] ; "" )
)

• ###### 2. Re: Help with a script

It seems to be okay so long as there is not more than 2 of the totals that are the same. For instance, I just ran the calc against the following totals and this is what it returned:

1T            63
1T            63
2T            63            ( Should also be 1T )
3T            63            ( Should also be 1T )
4              63            ( Should also be 1T )
6T            64
6T            64            ( Should also be 6T )
7T            64            ( Should also be 6T )
8              64             ( Should also be 6T )
10T          65             ( Should also be 9T )
10T          65             ( Should also be 9T )
11            65             ( Should also be 9T )
13            66

Seems like any equal totals more than 2 causes a problem.

• ###### 3. Re: Help with a script

Perhaps a pair of self join relationships would work better.

If you had these two relationships between three occurrences of the same table:

Table|ValuesLess>------Table------<Table|ValuesEqual

Table::Value > Table|ValuesLess::Value
Table::Value = Table|ValuesEqual::Value

Then your ranking plus Tie value indicator would be:

( Count ( Table|ValueLess::Value ) + 1 ) & If ( Count ( TableValuesEqual::Count ) > 1 ; "T" )

• ###### 4. Re: Help with a script

OK. I need to really think about this one. Phil, I know how good you are, and I'm sure you'd have it working, but this one may be out of my skill set. I'll work on it, though.

• ###### 5. Re: Help with a script

It's not really all that complex to set up. Just keep in mind that Table|ValuesLess, Table, and Table|ValuesEqual are three different "boxes" in Manage | Database | Relationships. You create the other two by selecting your box for "Table" and then clicking the duplicate button (Two green plus signs). You can double click the new table occurrence boxes to open a dialog where you can rename them to be something more descriptive than the original name followed by a number.

Your layout would be based on "Table" and the calculation field using the calculation that I specified would specify "Table" as it's context in the drop down at the top of the specify calculation dialog.

• ###### 6. Re: Help with a script

"Seems like any equal totals more than 2 causes a problem."

Did you remember that the result of my calculation must be UNSTORED ?

If yes, try to do a "refresh window".

BTW: this file contains both solutions, mine and Phil's :

Note that the Phil's solution is indipendent from the sort order of the "total" field, while mine depends from it and recalculates the place, so that 66 of your last example becomes the first.

• ###### 7. Re: Help with a script

Thanks to both of you. I have been away for 2 days so have not been able to get back onto this. I will be back on it tomorrow and will respond asap. Thanks again.

• ###### 8. Re: Help with a script

Ray - Thank you so much for the demo file. I understand most of what you did. I am having some trouble implementing into my solution. I think it is because of the tables / relationships. How did you create the Tables|ValuesLess and Tables|ValuesEqual? The database shows only one table -  Tables - but in the Relationships, the other two are showing up. Forgive my ignorance, but once I understand this part, I think I'll figure out the rest.

• ###### 9. Re: Help with a script

Phil said:

Perhaps a pair of self join relationships would work better. If you had these two relationships between three occurrences of the same table: Table|ValuesLess >------Table------< Table|ValuesEqual

table::value > Table|ValuesLess::Value
Table::Value = Table|ValuesEqual::Value

You can have many occurrences of the same table in the relationship graph.
Just push the green plus symbol.

• ###### 10. Re: Help with a script

Table occurrences are the "boxes" found in Manage | database | relationships. To create a second occurrence box for the same table, select an existing table occurrence box for the same table and click the duplicate button (Two green plus signs). This does not duplicate the table, it duplicates the reference to that table so that you now have an additional reference to that table that you can use in relationships. That's why these are 'self joins'. They link a table to itself.

For more on table occurrences and how you can use them in FileMaker: Tutorial: What are Table Occurrences?

• ###### 11. Re: Help with a script

Thanks. Back to work. We'll see what happens.

Thank you both, again.

• ###### 12. Re: Help with a script

I got it. Thanks a lot to both of you!