3 Replies Latest reply on Nov 16, 2009 9:03 AM by TSGal

    Documentation missing: maximum number of nested substitutes in Substitute() function

    MartinBrändle

      Summary

      Documentation missing: maximum number of nested substitutes in Substitute() function

      Description of the issue

      FileMaker Product(s) involved All FileMaker Pro and Advanced versions 7-10 Operating System(s) involvedall OS compliant with FM products given above Detailed description of the issue Tests show that at maximum 999 or 1000 nested substitutes seem to be possible in the Substitute() function: Substitute(field; ["s001";"r001"]; ["s002";"r002"]; ... ; ["s999";"r999"]) s001, r001 etc. are arbitrary search and replacement strings.  The size of the tested calculation function was smaller than 30'000 chars, so this limitation can be excluded.  This limit of the Substitute function is nowhere documented, neither in the Technical Specifications for FileMaker (http://www.thefmkb.com/7061) nor in the FM Help.   Expected ResultUpdate of FM Help or KB article 7061. Please confirm these limits here in the forum. Thank you.

        • 1. Re: Documentation missing: maximum number of nested substitutes in Substitute() function
          TSGal

          Martin Brändle:

           

          Thank you for your post.

           

          Just so I can forward the information correctly, what are you trying to accomplish when nesting 1000 Substitute functions?

           

          TSGal

          FileMaker, Inc. 

           

           

          • 2. Re: Documentation missing: maximum number of nested substitutes in Substitute() function
            MartinBrändle
              

            TSGal, 

             

            filtering search queries against a lengthy stop word list.

             

            I could find a workaround for this particular problem (by doing batches of substitutions), but the question remains where the limit is.

             

            Of course, there would be other ways to accomplish this:

            - scripted finds of the query terms in the stop word table to create a stop word filtered query

            - using a multikey relationship from the rewritten query to the stop word table to generate a new query  

            - in a web-based (XSLT or PHP) solution, use a hash to match the query terms against the stop words

             

            However, in a web-based solution (as used here) these would be too cumbersome and probably not fast enough.

             

            In one web-enabled solution, I use a short stop word list since mid 2005 for filtering search queries. 10-20 stop terms do a fine job there.

             

            For another web-enabled, very large solution (>10 GB database, text only) a much better stop word list is needed. Certain stop words occur in up to 80% of the records, and several million times. One would like to avoid them being searched or indexed.

             

            Best regards,

             

            Martin 

            • 3. Re: Documentation missing: maximum number of nested substitutes in Substitute() function
              TSGal

              Martin Brändle:

               

              Thank you for the clarification and explanation.  I have forwarded the information to our Development and Software Quality Assurance (Testing) departments, and I have received information that this has been previously reported.  The limit is 999 nested Substitute conditions.

               

              TSGal

              FileMaker, Inc.