8 Replies Latest reply on Oct 31, 2016 11:24 PM by user19752

    Get Nth -1 Not empty

    kentanderson

      I need to set a field from a previous record in the same table and same field, but nth -1 won't work for this because I only want to get nth record that was not blank.  Thanks

        • 1. Re: Get Nth -1 Not empty
          Mike_Mitchell

          FileMaker Pro 15 Help

           

          The Last ( ) function is your friend here.

          • 2. Re: Get Nth -1 Not empty
            kentanderson

            Any chance of a example

            • 3. Re: Get Nth -1 Not empty
              Mike_Mitchell

              What determines the record you want as the source?

              • 4. Re: Get Nth -1 Not empty
                philmodjunk

                It appears that you are referring to the previous record of the same table--presumably your found set and not a related table so I don't think Las() will work here.

                 

                I see two different issues and need clarification on the 2nd:

                 

                When you refer to the previous record via getnthrecord, if there is no such record, you get a ? result and this is not empty. You can handle this by checking the value of Get ( Recordnumber) and don't use GetNthRecord when the record number is 1.

                 

                But if you mean that you reference the previous record and a field in that record may be blank, then, should that be the case, what do you want to take place that you can't get to work for you? Do you then want to search back until you reach a record that doesn't have that field empty or do you want some other result to take place at that point?

                • 5. Re: Get Nth -1 Not empty
                  Mike_Mitchell

                  The reference to Last ( ) was meant for a self-join (since the OP specified the last populated record). But I wanted to get more information about exactly what the "last" record is prior to giving the full solution. (Probably should have explained that more.)

                  • 6. Re: Get Nth -1 Not empty
                    David Moyer

                    Hi,

                    I need to set a field from a previous record

                    Are you simply on a record and need this capability, or are you in a script, looping through records?

                    In a loop, you'd store the "last" non-empty value in a variable and use that.  Otherwise, a script that looped backwards through your found set until it found the "last" non-empty value, and returned to record n, wouldn't be that difficult.

                    • 7. Re: Get Nth -1 Not empty
                      taylorsharpe

                      set variable ( $pKey ) = [ Table::_pKey ]    // use whatever is the primary key for your table

                      Show All Records

                      set error capture on

                      Go to Previous Record

                      If ( Get ( LastError ) > 0

                           Enter Find

                           Set Field [ Table::_pKey ] = $pKey

                           Perform Find

                           Exit Script ( "No previous Record" )

                      End If

                      set error capture off

                      Omit Record

                      Show Omitted Only

                      Set Field [ Table::Field ] = whatever

                      // if you want to return to the original record then:

                      Enter Find

                      Set Field [ Table::_pKey ] = $pKey

                      Perform Find

                       

                      //  This assume you want whatever was the previous record stored in the table regardless of sort, etc.

                      • 8. Re: Get Nth -1 Not empty
                        user19752

                        Something like this

                         

                        make "ListOf" summary field of the field, then

                        GetValue( listOfF1 ; ValueCount( listOfF1 ) )

                         

                        but this can't be auto-enter calculation since when I do that the field can't be empty