6 Replies Latest reply on Nov 8, 2012 10:09 AM by philmodjunk

    Last Repeating Field that has a value...

    jrpmedia

      Title

      Last Repeating Field that has a value...

      Post

           I need to calculate the last repeating field that has a value in it.

           Not the value itself BUT the field number.

           e.g:

           I have a repeating field of 10 repetitions...

           1,1,F, , , , , ,1,  = 9 as 9 is the last repeat filled in

           , , , ,A, , , , , = 5 as 5 is the last repeat filled in

           How can I do that please?

            

        • 1. Re: Last Repeating Field that has a value...
          philmodjunk

               It would be easier to use a related table of records. Then Count (relatedTable::NeverEmptyField) will tell you the number of related records--the equivalent of what you are requesting here.

               To do what you want with a repeating field requires either a script that can loop through your repetitions or a recursive custom function (Requires FileMaker Advanced to install in your file).

          • 2. Re: Last Repeating Field that has a value...
            JimMac

                 Note that Count (RepeatingFieldName) returns the count of "non empty" fields, but doesn't prevent the user from entering data into the 10th field alone, thus leaving the first 9 repeats blank.  The Count function returns a number, not the placement.

                 If you do a loop as Phil suggested, it would still not do "easily" what you want.  You would first have to "fill in the blanks" so to speak.

                 Jim....

            • 3. Re: Last Repeating Field that has a value...
              jrpmedia

                   Thank you, thought so...

                   Though I like the challenge, I always try for the very simplest solution.

                   I can never remember or document enough for when I return to the client project, some times years later :-O :-(

              • 4. Re: Last Repeating Field that has a value...
                philmodjunk

                     Please note that I did not recommend using count with a repeating field. I suggested redesigning this part of your database to replace a repeating field with a related table. Such "skipping" won't be possible with a related table--among many other advantages.

                • 5. Re: Last Repeating Field that has a value...
                  JimMac

                       @Phii  wink I know you abhore repeating fields, but there are uses for them as part of the robust FMP tools package and there is always a "workaround" using related tables.  The related tables method are more robust for sorting and finding for sure.

                       @jrpmedia  Phil did mention the "script and loop" idea to which I was referring.  I use repeating fields quit often for quick and easy user access but don't use them for sorting, finding, or calculations [ as was their original use].  Have fun with whatever tool you use!

                       Jim...angel

                  • 6. Re: Last Repeating Field that has a value...
                    philmodjunk

                         I don't abhore repeating fields. I use them. Just not when there's a better alternative and there almost always is. Your last post talked about the same function I mentioned--count, but in the context of a repeating field. I saw a potential for confusion in that and wanted to clarify.