5 Replies Latest reply on Jan 18, 2015 8:19 AM by MichaelShea_1

    Help - Results of formula works for a number of lines and then corrupts

    MichaelShea_1

      Title

      Help - Results of formula works for a number of lines and then corrupts

      Post

      I am running a formula that calculates a cumulative inflation factor, by looking at the current period inflation (in one column), and multiplying it by the cumulative factor from the previous period in the calculation column.

      The results are correct for a number of rows....and then corrupts and eventually gives a "?" response.

      Makes no sense?  Help!

      Formula:

      If(FM Tab 1::Indice de Correcao = "Nao";1; (1+Correcao por mes Corrente)*GetNthRecord ( Correcao Acumulado _ a aplicar na Parcela do Mes ; Get(RecordNumber)-1 ))

       

      Thank youu

      Screen_Shot_2015-01-10_at_8.26.12_AM.png

        • 1. Re: Help - Results of formula works for a number of lines and then corrupts
          philmodjunk

          It may not look like it, but this kind of use of GetNthRecord (I knew that was the likely culprit before opening this thread), is recursive. FileMaker has to store the partial results of the calculation record for the first record in memory before attempting to evaluate the result for the next record, stores that partial value before attempting to evaluate on the next, and so on...

          If you "chain up" your calculations over a large enough set of records, the need to store intermediate values overflows the available system resources and you get an error result--and the question mark starts appearing in your fields.

          The exact point at which you reach this upper limit depends on your system. iOS devices reach this limit in FM Go much more quickly than when used in FMP.

          You'll need to figure out an alternate method for calculating these values. If this were just a running total, you could use a summary field. In this case, I'd guess that you can probably get away with setting up a field in each record that uses GetNthRecord in an auto-enter calculation to copy the desired value from the preceding record. Then you use this added field in your calculation where you currently use GetNthRecord to access date from the preceding records.

          This has several significant draw backs, however, so look before you leap:

          For existing data, you'll need to use Replace field contents and getNthRecord to copy over the value from the preceding records as a one time update of your existing records.

          This method assumes that you do not frequently edit the data in existing records. If you do edit a record, you'll need to use a script to isolate and update the records affected by this change. (all records "downstream" from it)

          This method assumes that you do not mix and match different groupings of the same records in order to compute a different series of values in the table.

          • 2. Re: Help - Results of formula works for a number of lines and then corrupts
            MichaelShea_1

            Phil

             

            Many thanks for the response.  I have correct this specific problem,but I am still with the need to understand how set up a field in each record that uses GetNthRecord in an auto-enter calculation to copy the desired value from the preceding record as you suggested.  

            Any chance you can shed some light on this as well?

            Many thanks

             

             

            • 3. Re: Help - Results of formula works for a number of lines and then corrupts
              philmodjunk

              It's the same expression, you just change your field from being of type calculation to being a field of the result type currently specified for your calculation. Then open field options and select the "calculation" option on the auto-enter table. You'll find your calculation has been preserved intact so you just need to click this check box to enable this option. You probably will need to clear the "do not replace..." check box.

              • 4. Re: Help - Results of formula works for a number of lines and then corrupts
                MichaelShea_1

                Phil

                 

                My apologies...I did not follow your logic.  The formula does not work when the "Type" is set to calculation and the formula has the Getnth record referral to the previous period.

                 

                How do I set up the "AutoCalc" to achieve the performance you described?

                 

                 

                • 5. Re: Help - Results of formula works for a number of lines and then corrupts
                  MichaelShea_1

                  Phil

                   

                  Apologies...I now understand the meaning of your message, and did exactly as you indicated, but the field only returns blank results in each record.....