5 Replies Latest reply on Nov 18, 2016 5:17 PM by justinc

    Let Function Confusion


      Hi All,


      We have a GO App that runs on iPhone and iPad, it has but one function and that is to gather email addresses from our customers. A customer simply enters their email address into a single field and it emails the address to our email server, initially Outlook. the email we receive looks like the following:


      My Business

      By submitting, I agree to receiving email/s.



      Sent from my iPhone


      The variables are the email address first line and the business name in this case My Business, each franchise has a different name and this is set in the variable script that sends the email. The line breaks/returns are consistent with the device adding the "Sent from my iPhone/iPad" and a trailing line break/return after the last line automatically.


      We then have a scheduled server script that extracts the two pieces of information from Outlook into an FM database the field is called body, we then have several calculations that extract the email address into a field called RB-06_Email and the business name into a field called RB_07_Source.


      I have struggled with creating a let field that will perform this in one or two actions, one for each line? Before going down the calculation route, have searched for a custom function that may do it but that has not been successful either; any help in resolving would be greatly appreciated as we are about the move from using Outlook to a SMTP/POP plugin option and updating this would great.


      Thanks, SRBENET

        • 1. Re: Let Function Confusion
          David Moyer


          I think I'd need to see how you are extracting the data currently in order to help you with doing it in "one or two actions".

          Besides that, switching to SMTP is a great idea - but you don't need a plug-in to do it. depending on your FM version.

          • 2. Re: Let Function Confusion

            Thanks David,


            There are 7 Calculation fields the first is:


            RB_01 :

            Case( RightWords ( body; 1 )  ="iPhone" ;

            Trim (Substitute ( body; "By submitting, I agree to receiving email/s.


            Sent from my iPhone" ; "" ));


            PatternCount ( body; "iPad" ) ;

            Trim (Substitute ( body; "By submitting, I agree to receiving email/s.


            Sent from my iPad" ; "" )))


            RB_02 :

            Substitute ( RB_01 ; Char(10) ; "|")


            RB_03 :

            Length ( RB_02 )


            RB_04 :

            Replace ( RB_02 ; RB_03 ; 1 ; "" )


            RB_05 :

            Position ( RB_04 ; Char(124) ; 1 ; 1 )


            RB_06_Email :

            Left ( RB_04 ; RB_05 - 1 )


            RB_07_Source :

            Right ( RB_04 ; ( RB_03 - RB_05 ) - 1 )


            We do use the FM SMTP but unless I am wrong FM15 does not have a incoming POP option? We are using FM15 and Server 15.

            Hope this is enough detail.



            • 3. Re: Let Function Confusion

              Since you are putting each value into separate fields (a good thing)...then doing it all in one Let() statement doesn't seem to make sense.  Unless it is your script that is doing the calculation, and then it is defining these fields?  (I wasn't clear if you were using an auto-enter calc on the field itself to extract the data.)


              Doing it in a script (assumes that you have the email-body information in a variable named $BodyData):

              set variable [$Email ; getvalue ( $BodyData ; 1 ) ]

              set variable [$Company ; getvalue ( $BodyData ; 2 ) ]


              set field [ Destination::EmailAddress ; $Email ]

              set field [ Destination::CompanyName ; $Company ]



              Now, you could extract each separate local script variable inside of a single Let() statement, but I'm not sure what you would gain with that.  There would be a tiny bit of speed increase, but that would be negligible unless you are extracting many more variables and running this over many many records.

              • 4. Re: Let Function Confusion

                Ah, just saw your updated reply.  I think you could perhaps do away with all of those calculations and just use the "getvalue()" function to extract the data from each line of the source data.  That assumes your source data is in the form (which you specified):



                by submitting...

                sent from iphone


                And perhaps your request to do this in a single LET() statement makes more sense now.  Here's an example of how you could refactor your code:


                Let ( [

                     _step1 =

                Case( RightWords ( body; 1 )  ="iPhone" ;

                Trim (Substitute ( body; "By submitting, I agree to receiving email/s.

                Sent from my iPhone" ; "" ));

                PatternCount ( body; "iPad" ) ;

                Trim (Substitute ( body; "By submitting, I agree to receiving email/s.

                Sent from my iPad" ; "" )))

                   ;  _step2 = Substitute ( _step1 ; Char(10) ; "|")

                   ;  _step3 = Length ( _step2 )




                Right ( _step4 ; ( _step3 - _step5 ) - 1 )



                The "_step1" and "_step2" items are local 'Let' variables, similar to script variables, but particular to the Let() statement (and only in scope inside that same Let statement).  And there are some ways this could be condensed down even further, e.g. combining function steps, like Length( Substitute ( <stuff> ) ).

                • 5. Re: Let Function Confusion

                  EXCEPT...I would re-write all of those calculations into a single line, again assuming your data is in the form you specified:


                  getvalue ( $Body ; 1 )          // for the email address

                  getvalue ( $Body ; 2 )        // for the company name