9 Replies Latest reply on Feb 5, 2011 2:27 PM by BobMoffett






      Using Filemaker Pro 10 on A Mac PPC using system OS X 10.5.8

      The records are layed out as follows:

      Xact Date, Basic Mortgage, Trans Amount, Previous Principal Balance, Insur Escrow, Tax Escrow, Total Escrow, Principal Paid, Interest Paid, New Principal Balance, Comment, and there are running Summaries on Total Escrow, Principal Paid, and Interest Paid.

      Basic Mortgage is an auto-entered field and Total Escrow is a calculated field.

      The Previous Principal Balance uses GetNthRecord to reach back to the previous record and get the New Principal Balance from that record. If (Get ( RecordNumber ) - 1 = 0; 0; GetNthRecord ( NewPrncplBlnce; Get ( RecordNumber) -1))

      The Previous Balance field of the current record will be dated until the current record is closed AND the New Principal Balance of the current record is updated with a New Balance.

      It gets worse but let's go slowly.


        • 1. Re: GetNthRecord

          You've described what you've set up, but haven't actually asked a question.

          • 2. Re: GetNthRecord

            Sorry, I got ahead of myself; that last paragraph of explanation should read; The Previous Principal Balance field of the current record will NOT be updated until the  current record is actually closed AND the New Principal Balance of the current  record is updated with a New Principal Balance. Why? In any other calculation the calculated field is updated as soon as the igredients are present. This isn't even a calculation, it is a "reach back and grab". If I don't enter a New Principal Balance for this record than the Previous Principal Balance of this record also shows nothing.

            Sorry about that, hope that clears it up.

            • 3. Re: GetNthRecord

              I think that's just a quirk due to using the GetNthRecord function. Since it pulls data from another record, I think FileMaker delays the update until you commit the record since doing so, might cause the record to move to a different position in the found set due to an imposed sort order either in the portal or on a layout's record set. (In otherwords, FileMaker isn't "smart enough" to know that your edits aren't going to change the value of a field that's part of the current sort order once you commit the record.)

              • 4. Re: GetNthRecord

                I used to program something by Ashton Tate called DBIII+(it was strictly DOS [pre-windows] related) and when something like this was done you had to make sure the record was closed and you had to do a couple of "Previous Records" and than "Next Record" forward to the concerned record and than you could be sure that last record contained the correct content; this move was especially important if you were running a summary on this field. This gets worse in-so much as IF you leave the New Principal Balance as a data entry field than you MUST populate it in order to see something in both Principal fields. IF you change the New Principal Balance field to a calculated field you cannot see the values in either Previous or New Principal Balance; the field shows zero (0.00).

                Look forward to your thought's.

                • 5. Re: GetNthRecord

                  Get ( RecordNumber ) and GetNthRecord are functions that should be used only in calculation fields that are unstored. Are they in your case?

                  • 6. Re: GetNthRecord

                    How do you do that?

                    What is the value of the instruction if you can't take it with you or keep it as part of this record?

                    • 7. Re: GetNthRecord

                      You can explicitly create an unstored calculation field

                      Manage -> Database -> Fields

                      Select calculation field and click Options then Storage Options

                      Check Do no not store calculation results.

                      Get ( RecordNumber ) and GetNthRecord are functions that should be used only in calculation fields that are unstored

                      What is the value of the instruction if you can't take it with you or keep it as part of this record?

                      There are instances where you don't need to take the data with you such as cField = Get ( RecordNumber)

                      What would be the point of storing this value as it is susceptible to change e.g record in table deleted, different sort order etc. The field only requires calculating when referenced or displayed.

                      Unstored calculations can be used where the values need to be refreshed frequently. They also reduce disk space. One trade-off is that they use more CPU cycles to stay refreshed.


                      • 8. Re: GetNthRecord

                        Phil and Lee

                        See what your saying and I thank you much. Your thoughts have given me some thought's and brought back some ideas. Going to fool with them a bit and see what happens. I'll be back here posting my findings or failures but probably not for a day or two.

                        Thank you and check back now and than for my findings or failures.

                        • 9. Re: GetNthRecord

                          Phil & Lee

                          It would appear that most, maybe all my problems that I thought were due to the GetNthRecord were actually problems I inherited when I chose to accept someone else's calculations as being correct and than using their results to build mine. When I dumped all the records and their calculations and replaced them with my own, everything seems to be working fine. I even inadvertently put a wrong year in two different records, which is the primary indexing field, and the records were still correct BUT they did look strange in the middle of where they were.

                          So unless I run across something later on, I think the GetNthRecord is working just fine.

                          Thank you both