6 Replies Latest reply on Jan 13, 2009 10:39 AM by daniel.cunnings

    Searching an unstored calculation field (quickly)

    daniel.cunnings

      Title

      Searching an unstored calculation field (quickly)

      Post

      I have a requirement to search an unstored calculation field but, with around 7000 records to trawl through each time, this process isn't especially quick. I appreciate that this isn't a good solution but I am struggling to think of an alternative.

       

      Specifically I need to be able to search the status of client quotes (a calculation field) which changes based on calculations and manual user intervention. The status shows the progress of the quote, whether the client requires a visit, if the quote is passed the due date, when the quote requires follow-up and whether it is lost or accepted amongst other things.

       

      I wish to be able show a list of only those quotes which require action (such as those needing a visit to be arranged, those which are outstanding a quote and those which need following up). However I wish to omit those which don't currently require action (such as those which have a visit arranged or have been sent a quote or where the quote is lost etc).

       

      Setting the status is not a problem but it is based upon other calculations such as whether the current date is after the visit date but before the due date (meaning a quote is required) or if the status is manually set to being lost etc.

       

      FileMaker won't let me index the field (no surprise) as it is a calculation field relying on other calculations fields. Also I don't really want to copy the calculated data out to a 'static' field as the information may change on an hourly basis and this would mean users are relying on 'outdated' information.

       

      Some of the status updates could be set via a script but this wouldn't allow for calculations based on the current date or calculations based on other calculation fields.

       

      As users need access to this information on a regular basis I need to find a solution without the massive search!

       

      Any suggestions would be greatly appreciated!

       

      Big-D

        • 1. Re: Searching an unstored calculation field (quickly)
          Jens Teich
             The standard solution to this problem is creation of a text field 'xyz_stored' and a script which transfers the actual state of 'xyz' (unstored) into the stored variant which can be searched quickly. The layouts show only the stored field and a button 'update' so users are motivated to click this button when browsing data and so keep the data in the stored field up to date.

          Disadvantage is that you possibly search outdated information. This could be minimized with a script running every night updating all records.

          Jens

          • 2. Re: Searching an unstored calculation field (quickly)
            daniel.cunnings
              

            Thank you for these suggestions Jens.

             

            I think I understand the principle. This would work well for older records where the status would change infrequently or not at all but would not be so good for newer records where the status of different records changes regularly throughout the day.

             

            I presume the script to update the status would take just as long as searching all the records and omiting the ones I don't want (otherwise I could add the 'update' into my existing script to search and omit).

             

            Is there any way to only search or only update records where the status has changed from last time? Thinking 'out loud' I suppose that would still need to compare an indexed version of the status with the un-indexed version which will take just as long as the 'date modified' won't change where the status is based on a calculation.

             

            I will certainly try out your suggestion and see how it goes but I can see that not having live status updates could prove frustrating for users.

             

            Are there any other options to consider?

             

            Big-D

            • 3. Re: Searching an unstored calculation field (quickly)
              Jens Teich
                 The script to update every record will indeed take quite long. This is why I suggested it to be run over night.

              You have a chance to apply this technique if you find the point where you have to update single records. The update of a single record (click on button 'update status of this record') is quite fast and the critical question is wether there is any chance that this might fit your needs.

              Jens




              • 4. Re: Searching an unstored calculation field (quickly)
                daniel.cunnings
                  

                Hmm, that would be a possible alternative.

                 

                I suppose I could offer both 'Update All' and 'Update Record' options to users.

                 

                It would still be nice to find a solution which doesn't require user interaction.

                 

                Incidently, once I have created a script, how do I set it to run automatically?

                 

                Big-D

                • 5. Re: Searching an unstored calculation field (quickly)
                  Jens Teich
                     > Incidently, once I have created a script, how do I set it to run automatically?

                  FileMaker Server 9 and 10 are able to run FM scripts according to a time schedule.

                  Jens

                  • 6. Re: Searching an unstored calculation field (quickly)
                    daniel.cunnings
                      

                    Jens,

                     

                    Thanks for the advice - I think I have found a work around based on your suggestions. I have also added a simple flag to show if the live status is different from the indexed status to encourage manual updating. 

                     

                    Just have to wait and see how users respond to it.

                     

                    I have FM Server 8 - is there any other way for me to automate scripts?

                     

                    Big-D