10 Replies Latest reply on Sep 30, 2015 3:22 AM by Fred(CH)

    Exporting unstored calcs

    jasonsanders42

      We have an aging report for one of our clients. It includes calculated fields for each of several 30 day increments so that the outstanding balance shows in the appropriate column. These fields are unstored as the outstanding calculation references related line items and payments.

       

      The fields calculate correctly. However, when the open aging report is long, about 6,000 records, and we export to excel with a script, we get a chunk of about 300 in the middle of the report that don't show the calculated breakdown. We have checked those specific records and the calculations do resolve correctly when viewed in data viewer.

       

      It appears as though FIleMaker is not resolving the unstored calc for these few records in time for the export. Has anyone else seen this behavior? If so, any fixes or workarounds?

       

      Thanks,

       

      Jason Sanders

      Extensitech

        • 1. Re: Exporting unstored calcs
          wimdecorte

          export in chunks to see if that gives FM enough time?

          • 2. Re: Exporting unstored calcs
            jasonsanders42

            Hi Wim,

             

            Thanks for the response. For a troubleshooting strategy, I can see how this might shed some light on the issue, but we're trying to create a single spreadsheet with a column for each "age group". I don't think you can update an existing .xlsx file with FileMaker's Export Records script step, right? (Barring use of the Scribe plug-in or similar). We need a solution that will create a single file.

             

            Thanks again,

             

            Jason Sanders

            Extensitech

            • 3. Re: Exporting unstored calcs
              wimdecorte

              Understood, but it would let you know if this really is a timing / refresh issue.

               

              The only good solution I guess is to abandon the unstored calcs and use stored values.  Things like aging only need to be updated once a day so having them recalc is a waste of time.

               

              If that effort is too great, I would set up a routine where you loop through the found set and populate a scratch table.  Then do the export from the scratch table.  That way at least you have control over the timing since you can pause whenever you detect that the value is not being calculated.

              • 4. Re: Exporting unstored calcs
                jasonsanders42

                As an update to this post, I've worked around the issue by first setting a global field with the current date and having my calculated fields reference that instead of Get(CurrentDate). Works with the current record set, but this doesn't give me a great deal of confidence that some larger set won't produce the same problem once whatever computational threshold is crossed again (assuming, of course, that's even the kind of issue we've got here).

                 

                Thanks,

                 

                Jason Sanders

                Extensitech

                • 5. Re: Exporting unstored calcs
                  Extensitech

                  wimdecorte wrote:

                   

                  The only good solution I guess is to abandon the unstored calcs and use stored values.  Things like aging only need to be updated once a day so having them recalc is a waste of time.

                  With due respect, I don't know if this really makes sense. Any number of things, throughout the day, would change the aging report. Payments are received. Invoices are added. Payments and Invoices end up getting voided. Terms get renegotiated.

                   

                  That being said, an option we already have on the table is to set the values in each of the aging categories for open invoices just before export, but as there are quite a lot of them, we're hesitant to take the performance hit without exploring other options.

                   

                  Chris Cain

                  Extensitech

                  • 6. Re: Exporting unstored calcs
                    wimdecorte

                    Extensitech wrote:

                     

                    wimdecorte wrote:

                     

                    The only good solution I guess is to abandon the unstored calcs and use stored values.  Things like aging only need to be updated once a day so having them recalc is a waste of time.

                    With due respect, I don't know if this really makes sense. Any number of things, throughout the day, would change the aging report. Payments are received. Invoices are added. Payments and Invoices end up getting voided. Terms get renegotiated.

                     

                     

                    Data will of course change, but all of these would be fully transactional and would update the aging as static data.

                     

                    Most of these "manipulations" have effects on multiple entities so you do want that full roll-back that you get from transactions without incurring the performance penalty of unstored calculations.

                     

                    It's a little more work than relying on those unstored calculations but it is orders of magnitudes faster downstream.  It's an architectural choice.

                    • 7. Re: Exporting unstored calcs
                      Extensitech

                      In that case, if these values are being updated via script triggers (fully transactional, of course) one wouldn't need to update them once a day then, either, would one?

                       

                      This would, though, as you say, eliminate the need for unstored calcs. We'd have to rely on having script triggers anywhere on Payments or Invoices, or their line items, where prices, sales tax, invoice date, terms, due date and more could change, but that is, of course, doable. It's certainly a worthwhile architectural choice. It's similar to choices we've made before, but not something we considered worth the effort for figures used only in this one report.

                       

                      I think what we're trying to figure out, though, is why an unstored calc would simply stop resolving in the middle of a long export, and then start resolving again, and how to prevent that. When we navigate to the same records individually, the calcs resolve correctly, so it's not an issue with the calcs themselves.

                       

                      We'd like to understand if this is a limitation of unstored calcs, and whether it's a limitation we can mitigate, rather than just making a blanket decision to never use them. Generally, if I have a calc that resolves to a given value, I expect it to resolve to that value all the time, not just most of the time, or for most of an export. I see that as a problem. If there are known (or newly known) times when the FM calc engine is unreliable, I'd like to be aware of that and make sure I'm not placing undue faith in it.

                       

                      The suggestion of a workaround is appreciated, but we already know several. We're trying to figure out why a tool in our toolbox isn't working reliably, and just using another tool won't help us find that out.

                       

                      And really, it is appreciated and I'm not trying to be argumentative. :-)

                       

                      Chris Cain

                      Extensitech

                      • 8. Re: Exporting unstored calcs
                        wimdecorte

                        Extensitech wrote:

                         

                        In that case, if these values are being updated via script triggers (fully transactional, of course) one wouldn't need to update them once a day then, either, would one?

                         

                         

                        Sure you would, for those records that did not change.

                         

                        Extensitech wrote:

                         

                        We'd have to rely on having script triggers anywhere on Payments or Invoices, or their line items, where prices, sales tax, invoice date, terms, due date and more could change, but that is, of course, doable.

                         

                        No, not really.  A carefully crafted transaction approach does not rely on triggers at all. It's more of a workflow approach where you take the user to where they can make updates, submit them or cancel out of them.  The trick is in making that workflow intuitive.  The updating is done transactionally through scripts on the submit/cancel.

                         

                        I agree that triggers can be very finicky and difficult to maintain, but those are not needed for what sounds like a normal 'accounting posting' mechanism.

                        • 9. Re: Exporting unstored calcs
                          Extensitech
                          Sure you would, for those records that did not change.


                          D'oh! Of course! For those where the data didn't change, but time passed. I spaced.


                           

                          No, not really.  A carefully crafted transaction approach does not rely on triggers at all. It's more of a workflow approach where you take the user to where they can make updates, submit them or cancel out of them.  The trick is in making that workflow intuitive.

                           


                          OK, I think I (finally) see what you're saying here, although at first I thought you were suggesting that the user should put the amount in the right aging field... You're just talking about the "outstanding" amount, and making that stored. (In fact, in systems we've built more recently, we've employed this.) Then the various aging categories could be updated based on the current date, which we could update nightly(?) That way the aging columns could be stored, since the due date, etc. is stored as well. Regardless of the smaller details, I'm sure this is an option.


                          It's not that I think triggers are especially finicky (due, in part, to some helpful blogs of yours!) and in fact I'd probably favor an onRecordCommit over creating a special "edit" layout and making the user go there. I can still make that transactional. My overall hesitation in relying in interface logic to maintain data integrity is that it only takes one forgetful developer, perhaps years from now and not even working for us, to allow data to be changed outside of those controls.


                          Regardless, I'm not suggesting that updating with script triggers, and/or updating with a server-side script, aren't an option, and arguably a better one. I'm still interested to know why, in an export process, 5% of the records, seemingly in the middle of the exported data, exported blanks instead of their expected values.


                          I vaguely recall that the print script step used to have an option to not print until all summaries (and calcs?) were resolved. I noticed today that's not there anymore, but is there some sort of equivalent for exporting?


                          There's certainly benefit in limiting unstored calcs, but they are still part of the feature set, and understanding where and why they might not produce the expected results is important.


                          Chris Cain

                          Extensitech

                          • 10. Re: Exporting unstored calcs
                            Fred(CH)

                            +1

                            Extensitech a écrit:

                             

                            There's certainly benefit in limiting unstored calcs, but they are still part of the feature set, and understanding where and why they might not produce the expected results is important.