9 Replies Latest reply on Nov 15, 2011 8:02 AM by rnovakov

    Un-stored calculation stop calculating after the 174 record

    rnovakov

      I have an un-stored calculation that for some reason stop calculating after the 174 record. I start getting ? instead of values, but if click on the first 2-3 cells with the ?, the field looks like it has a correct value, but after a while I can't even click in the field.

       

      I am trying to group the items by group depending on the found set and sort, in order to create some visual grouping of the items that belong to the same group, similar to alternating color but across groups.

       

      Go to layout Items, and look at the z_ItemGroup calc.

       

      Any advise would be appreciated.

        • 1. Re: Un-stored calculation stop calculating after the 174 record
          ariley

          You might be getting that because the data cannot fit in the field on the layout. You might want to try to enlarge the field.

           

          See if that solves your problem.

          • 2. Re: Un-stored calculation stop calculating after the 174 record
            Stephen Huston

            I tested your file and found that if I tried to enter the field (which is large enough) of a record where the previous record had also failed to display the calc result, I was not even able to ENTER the field!

             

            I think that the calc itself is part of the problem, with a Let statement that requires the result of the same calc from the previous record or it cannot resolve.

             

            I suspect there is a recusion issue here. The further down the list you move, the more calcs must resolve for EVERY previous record before the next one can  calculate and resolve.

             

            If I understood better what you were trying to accomplish, I might better understand why you used this approach. Unfortunately, it's a very inefficient calc for use in a list view for more than a very small list.

            • 3. Re: Un-stored calculation stop calculating after the 174 record
              ariley

              Perhaps you ought to check into generating your results via scripting instead of calculating. Sounds like you're short-circuiting your database.

               

               

              Regards,

               

              Agnes B. Riley

              FileMaker 10 and 11 Certified Developer

              http://www.zerobluetech.com

              ZeroBlue Technology Solutions

              where your data goes to work

               

              Member, FileMaker Business Alliance

              Member, FileMaker Technical Network

               

              FileMaker Advanced 11.3 on Mac OS X 10.6.8

               

              "Imagination is more important than knowledge..."

                 /Albert Einstein/

              • 4. Re: Un-stored calculation stop calculating after the 174 record

                Roumen,

                 

                Please show us the calculation. I'm sure the problem is in there!

                Maybe you use GetNthRecord?

                 

                 

                Winfried

                • 5. Re: Un-stored calculation stop calculating after the 174 record
                  taylorsharpe

                  I have run into the same situation using unstored calculations and GetNthRecord.  The example I had was a type of running total calculation that depended on several fields in an accounting solution plus the previous value.  It works fine for a couple hundred records and when one of the situations resets the value, it does fine.  But apparently the GetNthRecord can only remember back so far and then it runs out of memory.  If I reset the value every hundred or so records, the report is fine.  I found this quite annoying, but there are stored workarounds.  FileMaker has some other undocumented memory limitations like the number of rows for a chart can't exceed around 2000.  Maybe when FileMaker goes 64 bit some of these memory limitations will go away and we'll have more memory and functions for things like this and others like large unstored arrays. 

                   

                  But, yes, you are not crazy and I've seen this happen.  The question always is how do you want to make the workaround.  The frustrating part is that storing new values for a report with a loop through all the records is quite slow.  There are things I love about FileMaker, but this is a time when I wish it were a little more robust.  Here's hoping FileMaker 12 will give us some more power and speed!

                  • 6. Re: Un-stored calculation stop calculating after the 174 record
                    RayCologon

                    Hi rnovakov,

                     

                    It's not at all surprising that your unstored calc runs out of steam after a few records.

                     

                    Unstored calculations are re-evaluated each time they're displayed or referenced, and since your calc formula references the previous record's instance of itself, the first instance need only be evaluated once, but the second must evaluate itself and also evaluate (again) the first. By the time the third evaluates it must evaluate itself and the second, but because the second must first evaluate the first before it returns a result, the number of evaluations doubles. By the time you are on record 174, the cumulative number of evaluations required [edit] will run to many thousands.

                     

                    Small wonder the calc engine abandons the cause at this point given the accumulating number of operations required. What you're asking it to do here is really not a good idea!

                     

                    Depending on the actual use-case for the number you're trying to calculate, there are a number of rather more efficient ways you might go about calculating it. I'm attaching a modified copy of your example file (see below) that shows one of the alternative approaches you might consider. This method assigns group numbers sequentially (in serial order) regardless of the found set or sort order - and although still somewhat burdensome to calculate, does not stall after a few records.

                     

                    Regards,

                    Ray

                    ------------------------------------------------

                    R J Cologon, Ph.D.

                    FileMaker Certified Developer

                    Author, FileMaker Pro 10 Bible

                    NightWing Enterprises, Melbourne, Australia

                    http://www.nightwingenterprises.com

                    ------------------------------------------------

                     

                    Message edited by Ray Cologon.

                    • 7. Re: Un-stored calculation stop calculating after the 174 record
                      sporobolus

                      on 2011-11-10 10:35 Ray Cologon wrote

                      By the time you are on record 174, the number of evaluations required is 2^173 - which, if you're interested, is:

                       

                      11,972,621,413,014,756,705,924,586,149,611,790,497,021,399,392,059,392

                       

                      FileMaker would stall or blow its stack many orders of magnitude before that

                      number of evaluations!

                       

                      i expected this recursive calculation would produce simply 174 evaluations for

                      the 174th record (since it simply must eval each preceding record once), but i

                      thought i'd test it to check for hidden factors ...

                       

                      i took the OP's sample database, cleared it of records, and added

                       

                         $$x = $$x + 1;

                       

                      to the top of the Let() in the calculation in question; i then tracked the

                      value of $$x as i added new records (zeroing it before each new record); here

                      is the series:

                       

                      1     1

                      2     5

                      3     9

                      4     14

                      5     20

                      6     27

                      7     35

                      8     44

                      9     54

                      10     65

                      11     77

                      12     90

                       

                      very unintuitive; after the second record, the number of evaluations done for

                      record N, call it e(N), is e(N-1) + N + 1; sort of a modified Fibonacci sequence

                       

                      it turns out this is because there are two ways the eval is triggered; one is

                      when using getNthRecord to retrieve the previous value of the calc — this

                      indeed triggers each previous record's eval recursively, for one eval per record

                       

                      but when in List View, each record displayed is also re-evaluated (not sure

                      whether this is done for the whole found set or is related to the records in

                      view), and the result is combinatorial; if the layout is switched to Form View,

                      the number of evaluations is equal to the number of records, matching my

                      initial expectation

                      • 8. Re: Un-stored calculation stop calculating after the 174 record
                        RayCologon

                        Hi Steve,

                         

                        I appreciate you chiming in to pick up on that point, and I've now edited the earlier post (as marked) for accuracy.

                         

                        Meanwhile, the sequence of evaluation is an interesting discussion point. I agree that the number of evaluations depends on the layout view, as each calc is evaluated as it is displayed (and may be therefore be evaluated multiple times in list view, but only once per record in form view, providing you scroll to each record only once...). And you're correct that in form view advancing through the records once in a forward direction, the number of evaluations is combinatorial and by the time the 174th record is reached, is only a (relatively) puny 15,224 evaluations. In list view it may be a considerably higher value depending on how the list is displayed and scrolled.

                         

                        While the actual number of operations is of interest, the essential point remains - that an approach that reduces the cumulative burden on the calc engine is needed to address the problem. Hence the modified example I posted.

                         

                        Regards,

                        Ray

                        ------------------------------------------------

                        R J Cologon, Ph.D.

                        FileMaker Certified Developer

                        Author, FileMaker Pro 10 Bible

                        NightWing Enterprises, Melbourne, Australia

                        http://www.nightwingenterprises.com

                        ------------------------------------------------

                        • 9. Re: Un-stored calculation stop calculating after the 174 record
                          rnovakov

                          Agnes,

                           

                          Thank you for you input.

                           

                          Scripting is what I am trying to avoid.

                           

                          It might work for a small list, but if the list has thousand of records, that would not be very efficient.

                           

                          Roumen