7 Replies Latest reply on Jul 28, 2014 3:46 PM by lkeyes

    Mailing Lists - Find and SQL Quirks

    lkeyes

      Hi....

      Just wanted to confirm a couple things that I believe are true...

       

      1. There is no way to use Find or Quickfind to find the contents of a field which exceed a length of 40 characters.

       

      I'm simply trying to ensure that my fields don't exceed a certain character count for a mailing list.

       

      The only way I seem to able to do this is to create a separate field in my table (I called it addressfix) and then do a replace all using a function like:

      IF(LENGTH(address1)>40; RIGHTWORDS(address1;2))

       

      Is there a more refined way to accomplish this?

       

       

       

      2. When deduping, I created a "dupettest" field which was the concatinated contents of the firstname, lastname, and address1 fields.

      First I defined this as a calculated field.

       

      When I queried this field using SQL, I ended up with a bunch of digits, in the results which I'm assuming were some internal representation of the results of the calculated field.

       

      SELECT dupetest, count(dupetest) as num from labels, Group by count(dupetest) order by num desc.

       

      As described, first, I got garbage. When I redefined the field as a text field, and then did a field replace with the calculation, firstname&lastname&address1

      it worked, of course.

       

      So, I think this means....no SQL queries on FileMaker fields which are described as calculations.

       

      --- L

        • 1. Re: Mailing Lists - Find and SQL Quirks
          beverly

          1. You can set up validation on the field in the define dialog, so that upon entry it's limited in length.

           

          Or you could set a variable with the records that might exceed the limit (loop thorough the found set). Then do something with the list of records (your choice).

           

          2. yes, you can make ExecuteSQL queries on calculated fields.

           

          Your error may be elsewhere in the query. GROUP BY is for the column NOT counted

               SELECT

                    dupetest, COUNT(duptest) AS num

               FROM labels

               GROUP BY dupetest

               ORDER BY num DESC

           

          and/or your concatenation calculation may be excessively long (no space breaks) and FM can't index. did you try:

           

               firstname & " " & lastname & " " & address1

           

           

          Beverly

          1 of 1 people found this helpful
          • 2. Re: Mailing Lists - Find and SQL Quirks
            TomHays

            lkeyes wrote:

             

            1. There is no way to use Find or Quickfind  to find the contents of a field which exceed a length of 40 characters.

             

            I'm simply trying to ensure that my fields don't exceed a certain character count for a mailing list.

             

            The only way I seem to able to do this is to create a separate field in my table (I called it addressfix) and then do a replace all using a function like:

            IF(LENGTH(address1)>40; RIGHTWORDS(address1;2))

             

            Is there a more refined way to accomplish this?  

             

            Since you are creating a separate field, why not make that separate field be the length of the address?

            AddressLength = Length(address1)

             

            Then do a Find AddressLength>40.

            You can then fix the results in the found set by editing the original address1 field (or a copy of this field in addressfix if you need to preserve the original address1).

             

             

             

            lkeyes wrote:

            2. When deduping, I created a "dupettest" field which was the concatinated contents of the firstname, lastname, and address1 fields.

            First I defined this as a calculated field. 

             

            When I queried this field using SQL, I ended up with a bunch of digits, in the results which I'm assuming were some internal representation of the results of the calculated field.

             

            In your calculated field "dupettest", was the calculation defined to return a result as "Text" or did you leave it the default "Number"?  If so, then these were probably the digit characters that were present in the address1 field (and any that might have been in the name fields).

             

            -Tom

            • 3. Re: Mailing Lists - Find and SQL Quirks
              lkeyes

              Hi, Bev, 

               

              Since you are creating a separate field, why not make that separate field be the length of the address?

              AddressLength = Length(address1)

               

              Then do a Find AddressLength>40.

              You can then fix the results in the found set by editing the original address1 field (or a copy of this field in addressfix if you need to preserve the original address1).

               

              That is indeed how I ended up solving this particular issue. Make extra field, replace contents of the new field with using IF(LENGTH(Address1)>40) calcuation.

               

              I should have said up front that I was manipulating a mailing list obtained elsewhere; so I wouldn't put initial restrictions on the FM field lengths because I got the records from a comma-delimited import.

              Of course, if I was using FM for the data entry, I might restrict the field length to 40 chars...on the other hand it is just for this particular list that we're restricted to 40 chars.

               

              Replicating this, leaving out the GroupBy.  .  

               

              1. Created the DupeTest field as a calculated field usin the caclulation:

              Upper(Trim(firstName)&Trim(lastName)&Trim(address1))

               

              I noticed a couple points of interest, 

              If the address1 field contains a slash the DupeTest calculates as a scientific number.  Example:

               

              FirstName, LastName, Address1, DupeTest

              "Attn:", "Prinicpal", "125 Schroeders Ave", "ATTN:PRINCiPAL125SCHROEDERS AVE" <-- expected

              "Attn:", "Principal", "757 US HIGHWAY 202/206 STE 101", 7.527e+11 <--

               

              But if you click within the field, on the table it changes to display the concatenated value

              "ATTN:PRINICPAL757 US HIGHWAY 202/206 STE 101"

               

              I suppose this shoudln't be unexpected, as it is defined as a calculated field.

               

              And yet, now if I query this with SQL, using the following query

               

              SELECT dupetest from maillist order by dupetest;

              I get a series of digits, from -37397195 to 757202206101, with additional blank records.

               

              The query is executed with a one line script:

              Insert Calculated Result[Select;SQLWB::Results;ExecuteSQL(Evalutate(SQLWB::FMQuery);Char(9)&Char(9);"")]

               

              where SQLWB is the name of a table that has two fields "fmquery", and "results",

               

              which makes me wonder, maybe the problem is the script? 

               

              SQLWB.png

              • 4. Re: Mailing Lists - Find and SQL Quirks
                lkeyes

                HI, Tom,  see my reply to Bev regarding the new field; this was an imported set of records. The 40 character restriction was temporary so we could fit the output onto a mailing label of a certain size.

                 

                In your calculated field "dupettest", was the calculation defined to return a result as "Text" or did you leave it the default "Number"?  If so, then these were probably the digit characters that were present in the address1 field (and any that might have been in the name fields).

                 

                Thats it.   Thank you so much.

                 

                ---- L

                • 5. Re: Mailing Lists - Find and SQL Quirks
                  TomHays

                  lkeyes wrote:

                   

                  The only way I seem to able to do this is to create a separate field in my table (I called it addressfix) and then do a replace all using a function like:

                  IF(LENGTH(address1)>40; RIGHTWORDS(address1;2))

                   

                  Is there a more refined way to accomplish this?  

                   

                   

                  If it would be helpful to keep as many words as possible that fit within the 40 character limit, perhaps you might want to use the following replacement for your RIGHTWORDS(address1; 2) expression.  This calc will extract the maximum rightmost words that fit within the 40 character limit.

                   

                  If the rightmost word is longer than 40 characters, the expression will be empty.  This differs from your original expression that would keep the rightmost 2 words no matter how long they were.

                   

                  Let(

                  [

                  srcString = Trim(address1);

                  charLimit = 40;

                  cutString = Right(srcString; charLimit);

                  wordCountCutString = WordCount(cutString)

                  ];

                  Case(

                  RightWords(srcString; wordCountCutString) = cutString; cutString;

                  RightWords(srcString; wordCountCutString - 1)

                  )

                  )

                   

                   

                  -Tom

                  • 7. Re: Mailing Lists - Find and SQL Quirks
                    lkeyes

                    Tom...this is great. Many thanks for your function.  I did indeed just byte off the last 2 words of the string and it could be more intelligent for sure. 

                    --- L