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

    Help with a script

    fmpro_novice

      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
          raybaudi

               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
            fmpro_novice

                 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
              philmodjunk

                   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
                fmpro_novice

                     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
                  philmodjunk

                       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
                    raybaudi

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

                         https://dl.dropboxusercontent.com/u/93934293/Table.fmp12

                         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
                      fmpro_novice

                           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
                        fmpro_novice

                             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
                          raybaudi

                          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
                            philmodjunk

                                 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
                              fmpro_novice

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

                                   Thank you both, again.

                              • 12. Re: Help with a script
                                fmpro_novice

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