6 Replies Latest reply on Dec 3, 2013 10:18 AM by lkeyes

    FIND records with more than 35 characters?

    lkeyes

      Hi....

       

      bashing away at mailing lists, these days...

       

      Is there a way within the FIND dialog to specify a LENGTH(<fieldname>) > 35?

       

      I know I could create an extra field, do a replace field contents function referencing the field that I'm trying to limit to 35 chars, but that seems kinda clunky.

       

      --- L

        • 1. Re: FIND records with more than 35 characters?
          Mike_Mitchell

          Well, since the "@" represents any single character, you could string 36 of them together in the Find dialog ...   

           

          More practically, you could use ExecuteSQL to specify a query.

           

          HTH

           

          Mike

          • 2. Re: FIND records with more than 35 characters?
            lkeyes

            @@@@ -- clumsy. That would just return records that have exactly 35 chars; I suppose I could AND them together to include 36, 37, 38, 39, 40, etc.

            but yes....that would work,  thanks. 

             

            Now, with ExecuteSQL:

             

            So, if I used SQL (would love to, of course.... I'd have something like:)

             

            select * from  addresses

            where LENGTH(company)>35

             

            But this just dumps the records into a blob somewhere....it doesn't create a found set...er,  right?

            • 3. Re: FIND records with more than 35 characters?
              DavidJondreau

              I'm not saying the @ solution is the way to go, but if you choose that, what about 35 @ and 1 * ? That may work.

              • 4. Re: FIND records with more than 35 characters?
                ryantittle

                Also a little clunky, but you could always calculate another field to be equal to Length ( fieldname ) and then search on that field with a > 35

                • 5. Re: FIND records with more than 35 characters?
                  erolst

                  lkeyes wrote:

                  So, if I used SQL (would love to, of course.... I'd have something like:)

                   

                  select * from  addresses

                  where LENGTH(company)>35

                   

                  But this just dumps the records into a blob somewhere....it doesn't create a found set...er,  right?

                   

                  It doesn't by itself, but you could define a utility relationship with a global (text) field to your target table's primaryID, use "SELECT primaryID FROM sometable WHERE length () …" to get list of IDs. Write these into the global and do a GTRR to land at a found set. Use same relationship for other querys you want to translate into a found set.

                  • 6. Re: FIND records with more than 35 characters?
                    lkeyes

                    Hi, Ryan..... this is more or less what I ended up doing as you suggested.

                     

                    1. Create a scratch field called clength

                    2. replace field contents with RIGHT(company,(LENGTH(company)-35)))

                    3. Find within clength any record that has anything in it. 

                    4. Deal with found records.

                     

                    I'm also intrigued by erolst's suggestion about the GTRR method.

                     

                    However, I guess I'd love to see the ability to put calculations in FIND queries, in FM13, whoops...  FM14. To my knowledge, there really is no query mechanism that is robust as SQL, and it would really be nice to have that option. (and if it isn't SQL.then  the ability to query in a manner similar to SQL.

                    And if it is SQL, the ability to have the results dumped into a cursor that behaves identically to a Found Set or that *is* a Found Set.