3 Replies Latest reply on Jul 22, 2014 12:13 PM by rouelf_1

    FM Bugs Down With Mathematical Calculation Involving Iteration

    rouelf_1

      Title

      FM Bugs Down With Mathematical Calculation Involving Iteration

      Post

           Have defined fields with calculations (with multiplication) that utilizes the fields previous record content (numbers) to result in the fields next record content (numbers) as per below. In browse mode, scrolling thru the records past records that would not be empty, takes progressively much longer after the 7th incremental record scroll it takes over a minute to calculate and takes much longer for each successive record. With a 1000s of records it would take days or longer to perform the calculations for successive records. In other words the database bugs down. Likely a database is not designed to perform this type of calculation. A spreadsheet performs this very efficiently. However, I would like to do it with Filemaker.

            

           So perhaps someone can suggest a different approach to perform this calculation, hope so.

            

           Rot11 =

           Case ( Ksa_ID = Glbs::g_InitState ; GetNthRecord ( uEx ; Ksa_ID ) ; Ksa_ID > Glbs::g_InitState ; GetNthRecord ( Rot11 ; Ksa_ID - 1 ) * B11 + GetNthRecord ( Rot12 ; Ksa_ID - 1 ) * B21 + GetNthRecord ( Rot13 ; Ksa_ID - 1 ) * B31 ;  "" )

            

           Rot12 =

           Case ( Ksa_ID = Glbs::g_InitState ; GetNthRecord ( uEy ; Ksa_ID ) ; Ksa_ID > Glbs::g_InitState ; GetNthRecord ( Rot11 ; Ksa_ID - 1 ) * B12 + GetNthRecord ( Rot12 ; Ksa_ID - 1 ) * B22 + GetNthRecord ( Rot13 ; Ksa_ID - 1 ) * B32 ;  "" )

            

           Rot13 =

           Case ( Ksa_ID = Glbs::g_InitState ; GetNthRecord ( uEz ; Ksa_ID ) ; Ksa_ID > Glbs::g_InitState ; GetNthRecord ( Rot11 ; Ksa_ID - 1 ) * B13 + GetNthRecord ( Rot12 ; Ksa_ID - 1 ) * B23 + GetNthRecord ( Rot13 ; Ksa_ID - 1 ) * B33 ;  "" )

            

            

        • 1. Re: FM Bugs Down With Mathematical Calculation Involving Iteration
          philmodjunk

               There are two alternatives to this approach that you can consider.

               IF this data is rarely if ever modified, you can use an auto-enter calculation to copy data from the preceding record into fields of the current record. your calculation would then compute a value from this copied data. This "breaks the chain" of referring to a record, that then must refer to a record which must then refer to yet another record... in order to compute a value, but also opens the door to potential update issues so consider carefully.

               You can also use a relationship to refer to the ID -1 record (which is better than what you have here, as a found set may not always have the ID-1 record as the preceding record in your found set and then what you get will not be the expected result.) This is a better way to refer to the data, but I suspect that you'll have similar update delays unless you copy data from the preceding record as described in the preceding paragraph.

          • 2. Re: FM Bugs Down With Mathematical Calculation Involving Iteration
            rouelf_1

                 Thanks Phil, will try both approaches to see which works best for my solution.

            • 3. Re: FM Bugs Down With Mathematical Calculation Involving Iteration
              rouelf_1

                   Hey Phil, Thanks again, your suggestion lead me to use a script to define the content of the fields using the calculation as defined earlier. It works very fast, seconds; am happy !!!