7 Replies Latest reply on Jul 6, 2014 2:36 PM by erolst

    Trimming email text fields - more than white space needs to go

    slayden@msn.com

      Thanks in advance for any help.

       

      I need to export a bunch of email addresses, then upload them to e-vite for an upcoming event.

      When I do, I get unspecified errors at e-vite (and of course, no customer service.)

       

      So, I THINK that the problem has to do with this little renegade characters I am seeing after the email addresses when I open them up in excel.

       

      Apparently, the extra characters residing in my email text fields are not just while space.

       

      Is there a way to create a loop to trim out all white space, and all characters except letters, a period (.) or an @ sign?

       

      Scott

        • 1. Re: Trimming email text fields - more than white space needs to go
          taylorsharpe

          Filter ( [Field or Variable] ; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz012345689_.@-" )

          • 2. Re: Trimming email text fields - more than white space needs to go
            taylorsharpe

            You may want to validate email addresses too.  This is a validation that came over from the Brian Dunning web site quite a while ago:

             

             

             

            Let (

                [

                theEmail = [Insert Email Field or Variable] ;

                _validDomainCharacters = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-." ;

                _invalidLocalCharacters = "!\"#$%&'*,/:;<>?@[\]^`{}|~" ;

                _theLocalPart = Left ( theEmail ; Position ( theEmail ; "@" ; 1; 1 ) - 1 ) ;

                _theDomainPart =  Right ( theEmail ; Length ( theEmail ) - Position ( theEmail ; "@" ; 1; 1 )  ) ;

                _theTopLevelDomain = RightWords ( Substitute ( _theDomainPart ; "." ; " " ) ; 1 ) ;

                _ValidTopLevelDomains = "AC¶AD¶AE¶AERO¶AF¶AG¶AI¶AL¶AM¶AN¶AO¶AQ¶AR¶ARPA¶AS¶AT¶AU¶AW¶AZ¶BA¶BB¶BD¶BE¶BF¶BG¶BH¶BI¶BIZ¶BJ¶BM¶BN¶BO¶BR¶BS¶BT¶BV¶BW¶BY¶BZ¶CA¶CC¶CD¶CF¶CG¶CH¶CI¶CK¶CL¶CM¶CN¶CO¶COM¶COOP¶CR¶CU¶CV¶CX¶CY¶CZ¶DE¶DJ¶DK¶DM¶DO¶DZ¶EC¶EDU¶EE¶EG¶ER¶ES¶ET¶EU¶FI¶FJ¶FK¶FM¶FO¶FR¶GA¶GB¶GD¶GE¶GF¶GG¶GH¶GI¶GL¶GM¶GN¶GOV¶GP¶GQ¶GR¶GS¶GT¶GU¶GW¶GY¶HK¶HM¶HN¶HR¶HT¶HU¶ID¶IE¶IL¶IM¶IN¶INFO¶INT¶IO¶IQ¶IR¶IS¶IT¶JE¶JM¶JO¶JOBS¶JP¶KE¶KG¶KH¶KI¶KM¶KN¶KR¶KW¶KY¶KZ¶LA¶LB¶LC¶LI¶LK¶LR¶LS¶LT¶LU¶LV¶LY¶MA¶MC¶MD¶MG¶MH¶MIL¶MK¶ML¶MM¶MN¶MO¶MP¶MQ¶MR¶MS¶MT¶MU¶MUSEUM¶MV¶MW¶MX¶MY¶MZ¶NA¶NAME¶NC¶NE¶NET¶NF¶NG¶NI¶NL¶NO¶NP¶NR¶NU¶NZ¶OM¶ORG¶PA¶PE¶PF¶PG¶PH¶PK¶PL¶PM¶PN¶PR¶PRO¶PS¶PT¶PW¶PY¶QA¶RE¶RO¶RU¶RW¶SA¶SB¶SC¶SD¶SE¶SG¶SH¶SI¶SJ¶SK¶SL¶SM¶SN¶SO¶SR¶ST¶SU¶SV¶SY¶SZ¶TC¶TD¶TF¶TG¶TH¶TJ¶TK¶TL¶TM¶TN¶TO¶TP¶TR¶TRAVEL¶TT¶TV¶TW¶TZ¶UA¶UG¶UK¶UM¶US¶UY¶UZ¶VA¶VC¶VE¶VG¶VI¶VN¶VU¶WF¶WS¶YE¶YT¶YU¶ZA¶ZM¶ZW"

                ] ;

             

             

                Case (

                    IsEmpty ( theEmail ) ; 1 /*"no value supplied"*/    ;

                    PatternCount ( theEmail ; "@" ) <> 1 ; "invalid address" ;

                    PatternCount ( _theDomainPart ; "." ) = 0 ; "invalid formatting" ;

                    Length ( Filter ( _theLocalPart ; _invalidLocalCharacters ) ) ;

                        "invalid characters in the local part" ;

                    Length ( Filter ( _theDomainPart ; _validDomainCharacters ) ) <> Length ( _theDomainPart ) ;

                        "invalid characters in the domain part" ;

                    Left ( _theLocalPart ; 1) = "." or

                        Right ( _theLocalPart ; 1 ) = "." or

                        Left ( _theDomainPart ; 1 ) = "." or

                        Right ( _theDomainPart ; 1 ) = "." ;

                        "invalid formatting" ;

                   IsEmpty ( FilterValues ( _theTopLevelDomain ; _ValidTopLevelDomains ) ) ; "invalid top level domain" ;

             

                    1

                )

            )

             

             

             

             

            /*

             

            History:

            Created by Paul Turnbull

            Creation Date: 13-10-2005

            Modified Date: 13-11-2005 paul@fourofus.ca - fixed numerous bugs.

             

            Purpose: Returns 1 for True if the email supplied is valid.

             

            Parameters:

            theEmail

             

            Important Notes:

            List of TLDs from http://data.iana.org/TLD/tlds-alpha-by-domain.txt

            Will not handle urls in the form contact@domain.com/some.name although you could edit to handle this if you like.

            */

            • 3. Re: Trimming email text fields - more than white space needs to go
              beverly

              Don't forget those numerals! They can also be in email addresses, as Taylor says, & including: "-", "_", "." 

               

              To see what valid email is research JavaScript and or PHP routines that might check these. They can often be translated into FMP custom functions that rule out "correct characters, but invalid" according to the RFCs. http://en.m.wikipedia.org/wiki/Email_address . For the most part, the FILTER() will get what you want.

               

              -- sent from myPhone --

              Beverly Voth

              --

              • 4. Re: Trimming email text fields - more than white space needs to go
                slayden@msn.com

                Using the Filter ( [Field or Variable] ; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz012345689_.@-" ) method above, what is the correct protocol?

                1. Create a "holder field", looping through set field, then later if I like the results, looping back to re-set the original field, or....
                2. Can I clean up the field where it is by setting variables, cleaning the the variable in memory, then re-setting the same field?

                Thanks!

                Scott

                • 5. Re: Trimming email text fields - more than white space needs to go
                  erolst

                  slayden@msn.com wrote:

                  Using the Filter ( [Field or Variable] ; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz012345689_.@-" ) method above, what is the correct protocol?

                   

                  You can clean the field itself directly by applying the above expression as an auto-enter calculation (with “Do not replace…” unchecked).

                  • 6. Re: Trimming email text fields - more than white space needs to go
                    slayden@msn.com

                    In Options for Field [fieldName]. correct?

                     

                    would I then

                     

                    1. Auto enter a calculated value writing back to itself
                    2. Choose "always" for validate data?
                    3. I don't see "do not replace...'  where is that?

                    thanks!

                    • 7. Re: Trimming email text fields - more than white space needs to go
                      erolst

                      Go to

                       

                      Manage Database > Options > Auto-Enter tab > checkbox “Calculated Value” (for the expression), with sub-checkbox “Do not replace …”.

                      slayden@msn.com wrote:

                      1. Auto enter a calculated value writing back to itself

                      Well, you don't enter a calculated value, but rather the above expression, and it does the calculating, based on the field contents; then the writing back happens courtesy of FM