3 Replies Latest reply on Jan 1, 2014 12:20 PM by Mike_Mitchell

    Replace Field Contents or Relookup Field Contents in hosted files

    chrislow

      Happy New Year!

       

      Does anybody has a good solution how to refresh field contents in more than 4000 records stored in a hosted file?

      I need to refresh these fields for sorting.

      The given filemaker functions "replace field contents" or "relookup field contents" are too slow for hosted files.

      Importing is a little bit faster, a time trigger script on the server could be a solution, but I don't like this, because it's not in time.

      Sql?

       

      Thanks for any help!

       

      Best,

      C

        • 1. Re: Replace Field Contents or Relookup Field Contents in hosted files
          AlanStirling

          Hi Chris

           

          Do you need to refresh them all, each time?

           

          If only a few records have changed since the last refresh, then only those are in need of a new refresh.

           

          If you keep a record of the last refresh timestamp and look for records modified since then, you would have much shorter list to deal with.

           

          You have probably already discounted this method for practical reasons that your original post does not cover, but if not then it may help.

           

          Alternatively, you may be able to set up a Server-side script to do this, which might run faster than a client running the refresh.

           

          Best wishes - Alan Stirling, London UK.

          • 2. Re: Replace Field Contents or Relookup Field Contents in hosted files
            chrislow

            Hi Alan,

             

            thanks for your answer.

            Sometimes the refresh needs to be done only on a few records and if so they were sepetated before.

            But sometimes I need to refresh all. The server script is a quite good solution but this refresh should be done directly. Could be that my client wants to print the sorted records directly after doing changes on the sorting. On the other hand, these changes on sorting will probably be done once a month. So I think the script just should be execute when needed and not for example every hour..

             

            Best, Christoph, Munich Germany

            • 3. Re: Replace Field Contents or Relookup Field Contents in hosted files
              Mike_Mitchell

              Christoph -

               

              You didn't say how these fields are populated in the first place (although you alluded to a lookup). One option you might be able to consider involves using a trigger field or fields in an auto-enter calculation (rather than a lookup) to refresh the field during normal data entry operations. Combine this with a timestamp as Alan suggested, you can wind up with a very small set that actually needs refreshing. Something like:

               

              Let ( [ trigger = {insert field here} ] ;

              Case ( IsEmpty ( trigger ) or not IsEmpty ( trigger ) ; {insert calculated value here} )

              )

               

              Note that this simplified method will only work with fields in the same table. If you want the lookup to refresh based on a change in a related table, you'll need to use a Script Trigger instead. (This is still manageable, of course.)

               

              HTH

               

              Mike