4 Replies Latest reply on Jan 10, 2017 8:31 PM by philmodjunk

    Updating Un-Stored Calculations via Server


      I have a series of date fields that expire (or need to expire). Currently they do not - UnStored


      I have them set to unstored for two reasons - First performance second I have conditional value lists only grabbing current dates.


      Not expiring is a big deal -


      The present idea is to run a server script at night the basically updates each record to grab a new stored value. The dates only need to update once per day to be current.


      Do I need to troll over each record in the table and copy a field to itself or will updating one record in the table update all of them?


      Is this the best option to handle this function?


      Please advise,


      Thanks in advance



        • 1. Re: Updating Un-Stored Calculations via Server

          I am confused. Unstored calcs are not performance friendly. As well you do not need to re-evaluate unstored calcs With a script?


          "Expiring" may mean something to you but I am having a hard time seeing what you are trying to do.


          You can force stored calcs to re-evaluate using another field or value. I have never tried with a global field. So I guess the answer is yes you can do it. someone else might have better info on the global value though.

          • 2. Re: Updating Un-Stored Calculations via Server

            If you described what you are doing in more detail, there might be other options that don't need such an update script.


            But answering the question that you asked, there are a pair of "batch update" options you can investigate. Both should only be run if you are sure that no one might have one of these records open for editing or if you still choose these options, you have to build in additional code to identify the error and find the records that did not get updated as a result.


            You can use Replace Field Contents to do a batch update of all records in your found set and your found set can be all records in your table.


            In some cases, you might be able to use the Relookup option to copy the value of an unstored calculation field into  a stored date field.


            Note that even with a looping script, you have to consider whether it may be possible that a user has one of the records open for editing and build in a way to deal with this. But in a looping script, you can at least log the ID of the locked record at the time the script gets a "lock error" on the specific record where with batch updates, you have to check for an error code and then find any non-updated records after the fact.

            • 3. Re: Updating Un-Stored Calculations via Server

              A date field can not "expire". It contains a date, doesn't it ?


              A whole record might change its status to "expired", based upon a date calc.


              The calc might be stored or unstored.


              Stored calcs are good for searching, bad for giving time-based info.

              Unstored calcs are good for giving info - every time you look at them they update - but very bad for searching.


              As philmodjunk said, please explain what your fields are and what you want to achieve.

              • 4. Re: Updating Un-Stored Calculations via Server

                Since you mentioned conditional value lists. I'm going to point out an approach for them that is relationship based (as are most, but not all CVLs) but which would not require such an update process:


                Say you want a value list from all records where an "expiration date" in a date field has not yet been reached.


                If you set up this relationship:


                LayoutTableOccurrence::cToday > DataTable::ExpirationDate


                ExpirationDate can be a stored date field and cToday can be an unstored calculation field with: Get ( CurrentDate ) as its expression.


                A CVL can then be set up on the Layout to show values from DataTable. If you specify the "include only related values, starting from LayoutTableOccurrence, you get a list of values only drawn from records where the expiration date indicates that the record has not yet expired.


                If you changed the relationship to <=, you could get a value list base on all records that have Expired.


                And no mass update of records is required.