1 2 Previous Next 18 Replies Latest reply on Aug 12, 2016 11:48 AM by TSGal

    Script Import Deleting Calculations

    CICT

      Using FMPA v15 on Windows.

       

      We have a database file we need to import a script into, the destination file has some fields using different names and possibly a custom function missing (although I can't confirm this as yet).

       

      Having imported the script to check the number of errors, all the <Missing Field> and <Unknown> layouts are highlighted in red text and red markers on the scroll bar (nice new feature).

       

      However, the import log was reporting some errors within calculations, but using 2EmpowerFM we couldn't find these. Finally, we established that the set variable calculations these should have been in (which would normally be disabled at this stage) were blank and no errors were displayed within the script workspace.

       

      Is this expected behaviour of v15? I've no recollection of this happening before and missing fields or functions would normally be disabled in the calculation. I've attached an extract from the log entries This could be reliably repeated.

       

      The first line error was caused by a missing field 'FMEmailAddress' and 'zConfig::kcCfgConstant ' field name did not match that of the destination file. After adding the missing field in the destination file and renaming the other field in the source file the calculation did import during a further attempt. We haven't got as far as the next log errors as yet.

       

      Many thanks

      Andy

        • 1. Re: Script Import Deleting Calculations
          CICT

          I can now confirm that the 2nd error on line 62 was resolved by adding the missing custom function.

           

          However, the original question remains as to whether importing a script with non-matching fields should result in blank calculations?

           

          Many thanks

           

          Andy

          • 2. Re: Script Import Deleting Calculations
            TSGal

            CICT:

             

            Importing a script should not result in blank calculations unless it is an Evaluate function.  What is the calculation being used in the original file?

             

            TSGal

            FileMaker, Inc.

            • 3. Re: Script Import Deleting Calculations
              CICT

              Hi TSGal

               

              I can post the calcs here if needed, but the extract from the log file attached to my original posting contained these - script lines 31 and 62.

               

              Kind regards

               

              Andy

              • 4. Re: Script Import Deleting Calculations
                TSGal

                CICT:

                 

                Can you check those line numbers?  I show line 31 as:

                ] ;

                 

                Line 62 is in the middle of a Case calculation beginning with Middle function.

                 

                TSGal

                FileMaker, Inc.

                • 5. Re: Script Import Deleting Calculations
                  CICT

                  Hi

                   

                  I guess I wasn't clear enough, I was referring to the line number of the script that is displayed within the log info, not the physical line numbers. I'll post each calculation separately, copied from each variable calculation below, but these are the same as posted within the log files - each calculation is preceded by a timestamp.

                   

                  Andy

                  • 6. Re: Script Import Deleting Calculations
                    CICT

                    Here's the first, it was setting a variable called $inAddress on line 31 of a script called _inProcessIncomingEmail:

                     

                    Let ( [

                      sql =

                    "SELECT z." & SQLField ( zConfig::FMEmailUser ) & ", z." & SQLField ( zConfig::FMEmailAddress ) & "

                    FROM " & SQLTable ( zConfig::kCfgConstant ) & " z" ; //no WHERE needed as single record config table

                      res = ExecuteSQL ( sql ; "¶" ; "" ) ;

                      add = If (

                      IsEmpty ( GetValue ( res ; 2 ) ) ; //the user name may be the email address

                      GetValue ( res ; 1 )  ;

                      GetValue ( res ; 2 )

                      ) ;

                      pos = Position ( add ; "@"; 1 ; 1 ) ;

                      $inDomain = Middle ( add ; pos + 1 ; Length ( add ) - pos )

                    ] ;

                      Middle ( add ; 1 ; pos - 1 )

                    )

                     

                     

                     

                     

                     

                     

                    /*Superseded 13/05/16

                    Let ( [

                      sql =

                    "SELECT z." & SQLField ( zConfig::Cfg_FMEmailUser ) & ", z." & SQLField ( zConfig::Cfg_FMEmailAddress ) & "

                    FROM " & SQLTable ( zConfig::kcCfgConstant ) & " z" ; //no WHERE needed as single record config table

                      res = ExecuteSQL ( sql ; "¶" ; "" ) ;

                      add = If (

                      IsEmpty ( GetValue ( res ; 2 ) ) ; //the user name may be the email address

                      GetValue ( res ; 1 )  ;

                      GetValue ( res ; 2 )

                      ) ;

                      pos = Position ( add ; "@"; 1 ; 1 )

                    ] ;

                      Middle ( add ; 1 ; pos - 1 )

                    )

                    */

                    • 7. Re: Script Import Deleting Calculations
                      CICT

                      And here's the second from the same script, setting a variable $eFrom:

                       

                      //19/05/16 AH - Trim added due to additional space occasionally appearing after address

                       

                       

                      //29/04/16 AH Substitute square brackets added as addresses were being wrapped in this, need the custom function to be updated eventually

                       

                       

                      //Uses custom function cict_ExtractSenderFromMessage ( RecipientField ; SenderField ; MessageContent ; Occurrence )

                       

                       

                      //Replaces original calculation, as a small amount of recursion required to overcome situations where some messages forwarded to SaleFaith

                      //have a prefix containing the sender's details, the recursion skips this and extracts the original senders details

                       

                       

                      Trim (

                      Substitute (

                      cict_ExtractSenderFromMessage ( Email2Comm2Contacts::To ; Email2Comm2Contacts::From ; Email2Comm2Contacts::cc ;  Email2Comm2Contacts::BodyText ; 1 ; Globals::zCfg_g_FMEmailUser ) ;

                      [ "[" ; "" ] ; [ "]" ; "" ]

                      )

                      )

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                      //FOLLOWING SUPERSED BY ABOVE CUSTOM FUNCTION, ALLOWS A SMALL AMOUNT OF RECURSION TO OVERCOME PREFIXES FROM SOME FORWARDED EMAILS

                      //Utilises the EM_From field unless message has been forwarded to Salefaith where it:

                      //Strips out the sender email from the body text. Has to accommodate 3 formats:

                      //<sender@domain.co.uk>

                      //[mailto:sender@domain.co.uk]

                      //sender@domain.co.uk

                       

                       

                      /*

                      Let ( [

                      //Following extracts the original senders email address from the email message when it has been forwarded to SaleFaith by a member of staff

                        theBody = Email2Comm2Contacts::Em_BodyText ; //Original body text

                        fromStart = Position ( theBody ; "From:" ; 1 ; 1 ) + 4 ; //position of start of original sender of message within the forwarded body text

                        subBody = Right ( theBody ; Length (theBody ) - fromStart ) ; //Message content commencing at From:

                        theAt = Position ( subBody ; "@" ; 1 ; 1 ) ; //position of @ in the sender's mail address

                        mailPrefix = Middle ( subBody ; 1 ; theAt ) ; //string between from: and @

                       

                        mailStart = //identifies the non domain part of the email address, caters for mailto: and < separators

                        If (

                        PatternCount ( mailPrefix ; "mailto:" ) > 0 ; Position ( mailPrefix ; "mailto:" ; 1 ; 1 ) + 7 ; //Outlook, webmail format

                        Position ( mailPrefix ; "<" ; 1 ; 1 ) + 1 //Apple Mail, Entourage format

                        ) ;

                       

                        mailUserName = Middle ( mailPrefix ; mailStart ; theAt + 1 - mailStart  ) ; //non domain string from email address including the @ e.g. user.name@

                       

                       

                        mailEnd = //identifies the domain part of the email address, caters for ] and > separators

                        If (

                        PatternCount ( mailPrefix ; "mailto:" ) > 0 ; Position ( subBody ; "]" ; 1 ; 1 ) ; //Outlook, webmail format

                        Position ( subBody ; ">" ; 1 ; 1 )   //Apple Mail, Entourage format

                        ) ;

                       

                       

                        originator = mailUserName & Middle ( subBody ; theAt + 1 ; mailEnd - theAt - 1 ) ; //The original sender of the forwarded message

                       

                       

                        sender =

                        If (

                        PatternCount ( Email2Comm2Contacts::Em_To ; $inAddress ) > 0 ;

                        originator ;

                        Email2Comm2Contacts::Em_From //The sender of a non forwarded message

                        ) ;

                        theStart = Position ( sender ; "<" ; 1 ; 1 ) ;

                        theEnd = Position ( sender ; ">" ; 1 ; 1 )

                      ] ;

                       

                       

                        If ( theStart > 0 and theEnd > 0 ;

                        Middle ( sender ; theStart + 1 ; theEnd  - theStart - 1 ) ;

                        sender

                        )

                      )*/

                      • 8. Re: Script Import Deleting Calculations
                        TSGal

                        CICT:

                         

                        Thanks for the clarification.

                         

                        The error 1205 is no ending comment.  That is, it did not add a "*/" to the end of the imported calculation.

                         

                        Please send in a clone of your original database file.  If you want, remove all other scripts and layouts so I can replicate the importing of your script(s).  This will let me know what is causing the "*/" to not be added to the end of the calculation.  I have sent you a private message with instructions where to send the file.

                         

                        TSGal

                        FileMaker, Inc.

                        • 9. Re: Script Import Deleting Calculations
                          CICT

                          Hi TSGal

                           

                          Although we're unable to send over our complete CRM system, we have replicated the same behaviour with custom function imports and have emailed over a file for you to import custom functions from into a new empty file, which has reliably repeated the empty calculations.

                           

                          Many thanks

                           

                          Andy

                          • 10. Re: Script Import Deleting Calculations
                            CICT

                            Hi again

                             

                            Using the example file we've sent you, we've made further progress and traced part of the problem to the fact we've retained the superseded version of the calculation at the end disabled with the standard  method using /* */

                             

                            By deleting this older disabled version, the custom function calculation imported successfully.

                             

                            However, this does cause significant problems in terms or retaining an audit trail of changes throughout our solutions.

                             

                            Regards

                            Andy

                            • 11. Re: Script Import Deleting Calculations
                              TSGal

                              CICT:

                               

                              I received your file.  Thank you.

                               

                              I am able to replicate the issue.

                               

                              Your last post is a little confusing.  Is this change for all three calculations or a specific calculation?  If you want, send me an updated file and I'll compare the changes to understand what are the differences.

                               

                              TSGal

                              FileMaker, Inc.

                              • 12. Re: Script Import Deleting Calculations
                                CICT

                                Hi

                                 

                                Glad to hear the file has demonstrated the problem and I'll try to be clearer on my last post.

                                 

                                The cict_ExtractSenderFromMessage custom function consists of:

                                 

                                //Descripion

                                Let (

                                     <custom function contents>

                                )

                                 

                                Then below this - after Trim() - is the previous version disabled in the format:

                                 

                                //Superseded message

                                /*Let (

                                     <old version custom function contents>

                                )*/

                                 

                                If you do the import as supplied the import will fail. However, if you remove the older disabled code below the active calculations (between /* and */) from the custom function calculation, then a subsequent import will succeed.

                                 

                                It seems to be the commented out calculations at the end of the overall calculation that is causing the import to fail. These CRM frameworks have evolved over 8 years, therefore many of our calculations have superseded versions retained for audit purposes.

                                 

                                If this still isn't clear, please let me know and I'll send over a second file to demonstrate this.

                                 

                                Many thanks

                                 

                                Andy

                                • 13. Re: Script Import Deleting Calculations
                                  CICT

                                  I have now emailed over a modified file with a simple demonstration added to it.

                                   

                                  I hope this helps

                                   

                                  Andy

                                  • 14. Re: Script Import Deleting Calculations
                                    TSGal

                                    CICT:

                                     

                                    Thank you for the clarification.  Much easier to understand.

                                     

                                    I did receive the updated file, and the new calculations are easier to understand the cause.  I have sent your observations and file to our Development and Testing departments for review.  When I receive any feedback, I will let you know.

                                     

                                    TSGal

                                    FileMaker, Inc.

                                    1 2 Previous Next