5 Replies Latest reply on Apr 4, 2011 2:12 PM by philmodjunk

    Let Statement Limit

    TracyKeenan

      Title

      Let Statement Limit

      Post

      A member of this forum helped me with the following:

      Let ( [ PrevRank = GetNthRecord ( Rank ; If ( Get ( RecordNumber ) = 1 ; 0 ; Get ( RecordNumber ) - 1 ) ) ;          PrevScore = GetNthRecord ( Total Points; If ( Get ( RecordNumber ) = 1 ; 0 ; Get ( RecordNumber ) - 1 )  ) ] ;          If ( PrevScore ≠ Total Points ; Get ( RecordNumber ) ; PrevRank )      )

      This calculates the rank of tennis players.

      However, the let statement quits at the rank of 121 with the remaining records showing a ?

      Is there a limit?  It works amazingly well for the players with a rank of 121 or less.

      Thanks for your help.

      Tracy

      Picture_1.png

        • 1. Re: Let Statement Limit
          arnoldkegebein

          I do not know about a limit with the Let function. It rather might be a problem with the the GetNthRecord function.

          But besides the question about any limits, you could solve the problem in a different way.

          1. Create a value list "Total Points", listing the total points player have. The value list will show only different total points. The list will also be sorted, show the highest value in the last line.
          2. The calculation to get the rank would be:
          Let( [
             _list = ValueListItems( Get( FileName ); "Total Points" );
             _pos = Position( ¶ & _list & ¶; ¶ & Total Points & ¶; 1; 1 )
          ];
             ValueCount( _list ) - PatternCount( Left( _list; _pos ); ¶ )
          )

          What is this calculation doing? First, it gets the entries of the value list, then it calculates the position of the current total points within this list. With the function PatternCount it calculates the line number (index) of the position in the list. This value is substracted from the number of values in this list.

          There are different ways how you can calculate the last step, but basically this should give you the number you are looking for.

          ~Arnold Kegebein

          • 2. Re: Let Statement Limit
            TracyKeenan

            Hello Arnold,

                I am such a newbie, I am uncertain if a Value List is a table, layout or if it is done by the Let function.  I tried sorting by Total Points and then put that function in the calculation value for the rank field and the result was all zeros.

            I appreciate the help.  Is there anyway I could make it a script and just run and store it when I need to update that field?

            The original function I received from Phil worked up until the value rank=121 - which is strange to me because there were other players with the same total points - so why it would fail, seems illogical.

            Thanks for your time.

            Tracy

            • 3. Re: Let Statement Limit
              philmodjunk

              There can be an upper limit to getnthrecord, but I wouldn't expect you to hit it so soon (but maybe that's just my ignorance showing here.) Are you by any chance trying to use this on an iPhone or iPad with FileMaker Go?

              • 4. Re: Let Statement Limit
                TracyKeenan

                Hi PhilModJunk,

                    No...This is all in an effort to avoid a convoluted excel spreadsheet with heaps of manual data entry that I inherited in this volunteer role....

                Do you know what the "?" result means?  The Total Points field is numeric, its sorted.

                Could you help me turn it into a script that I could run that would select the rows, sort them and then apply the let function, saving the result in the rank field?   Do you think that would cure the 121 blockage?

                Thanks again.

                Tracy

                • 5. Re: Let Statement Limit
                  philmodjunk

                  ? means that file maker cannot display the value. It can be as simple as a field that is too narrow. It can also be due to divide by zero in the calculation--neither of which would seem to apply here...