6 Replies Latest reply on Mar 29, 2013 11:48 AM by schamblee

    Record number



      Record number


           I imported a database from Excel.  The records in the Excel file were supposed to be sorted by date.  When I imported them and sorted by date, records 42 and 41 were out-of-order.  Not a big problem, except that I want to get the ending balance from a previous record and use it as the beginning balance of a current record.  

           I used the "Get (RecordNumber)" to get the number of the current record and "Get (RecordNumber) -1" to get the previous record number.  Then I used  "GetNthRecord ( Ending Balance ; Get Record Number Previous )" to get the value of the previous ending balance.

           Everything worked until the 41st record.  In the window in the upper left corner, it says record number 41.  HOWEVER, the "Get (RecordNumber)" is 42.  So, I have a circular computation.  

           QUESTION:  Why doesn't "Get (RecordNumber)" get the current record number?

           Note: I resorted the file using "Ending Balance" as the sort criteria.  "Get (RecordNumber)" doesn't change.

        • 1. Re: Record number

               Actually .... per Filemaker help...


               Returns the number of the current record in the current found set.
               The value returned is determined by the relative place of the record in the found set, and it changes depending on the find criteria and the sort order.
               To return a value that uniquely and permanently identifies a record in this table, use Get(RecordID).


          • 2. Re: Record number

                 Jim is correct.  I made a mistake in my test calculation and it may be the same one you are having.  Your calculation needs to be unstored so it updates when the order changes.   

            • 3. Re: Record number

                   Jim, I can read the Help description.  It doesn't work.  Or, should I say, I doesn't have ALL of the information necessary to make Get (RecordNumber) work.

                   PhilModJunk: [If Get (recordNumber) is used in a calculation field, it won't change if the calculation field is stored. Specify an unstored storage option and the field will update when your found set or sort order changes to put the record in a different spot in the found set's sort order.]

                   Here is what I have:

                      Field Name:  Get Record Number (Calculation);   Get (RecordNumber)
                      Storage Options:     Global= Not selected
                                                         Indexing= None.   Nothing else is turned on.  Default language: English

                   My results are that the record number returned by Get (RecordNumber) is the same as the number of the record I imported.  It never changes no matter what I do.  

                   Why can't  "Get (RecordNumber)" be the record I choose without a lot of convolutions to make it work?

              • 4. Re: Record number

                     Got it.  The field I USED Get (RecordNumber) in was stored.  

                • 5. Re: Record number

                       Try Get (RecordID) instead of Get (RecordNumber).


                  • 6. Re: Record number

                         If you change your calculation field to unstored. Click storage options, Click Do not store - recalculate as needed.

                         It is operating correctly.  It computes the record at time of import and stores the results.  The result does not update when sorted because the result is store. If it is not store then filemaker will update when sorted.

                         I made the same mistake when I tested, and posted an incorrect answer.  After Jim posted and I took more time to investiage. I was like dah.  I forgot about Filemaker storing the results.