5 Replies Latest reply on Apr 15, 2013 8:16 AM by philmodjunk

    Calculated to e-mail

    rudihoremans

      Title

      Calculated to e-mail

      Post

           In a script i loop through all records and depending on the text in a certain field another script is called and performed to send an e-mail with a fixed text plus variable text extracted from certain fields. This works fine. The problem i have is that depending on the content of a field "department", the recipient of the e-mail should be different. I tried to have the TO field as a calculation:

           case (department = "A" ; "recipient1@mail.com ; recipient2@mail.com" ;

           department = "B"  ; "recipient3@mail.com ; recipient4@mail.com" )

           This does not seem to work. What am i missing ?

           i am using FM12 advanced server and mails are sent via smtp.

        • 1. Re: Calculated to e-mail
          philmodjunk

               Isn't that

               case ( TableOccurrenceName::department = "A" ....

               ??

               And the name you use in place of "tableOccurrenceName" could be why it doesn't work. It will depend on the layout that is current when the script executes, and could depend on relationships that link to "tableOccurrenceName".

          • 2. Re: Calculated to e-mail
            rudihoremans

                 Got the mail part working. The field on the current layout was not correct.

                 However, this part has problems:

                 Go to Record/Request/Page [First]
                 Loop
                 If[GetField ( Get ( LayoutTableName ) & "::Z_Foto" ) = "NOK"]
                 Perform Script ["Mail1"]
                 Else If[GetField ( Get (LayoutTableName) & "::Z_Staal" ) = "NOK"
                 Perform Script ["Mail2"]
                 End if
                 Go to Record/Request/Page [Next; Exit after last]
                 End Loop

                 The IF part treats every record the same way, regardless of the contents of the field "Z_Foto" or "Z_Staal", and executes the script to send mail for all records instead of just for those records where the field "Z_Foto" or "Z_Staal" equals "NOK".

                 I am using the GetField etc. because the same script should be used for a large amount of tables.

            • 3. Re: Calculated to e-mail
              philmodjunk

                   Take a closer look at your getField calculations. From what you describe Get ( LayoutTableName ) & "::Z_Foto" does not evaluate to a valid field reference when the script is performed. If you have FileMaker Advanced, you may want to test this in the Data Viewer with a watch expression. If you do not have Advanced, you may want to insert a show custom dialog step at the beginning of the script that displays the values returned by your Two GetField calculations to see what values are actually being returned when the script is performed.

              • 4. Re: Calculated to e-mail
                rudihoremans

                     Thanks for the comment. Realised that the logic was correct but that in fact FM cannot calculate GetField within the IF statement if it is not between quotation marks, and there is no way to put it within quotation marks.

                     However, I think I found a workaround (don't know if it's the most efficient or elegant way, but it seems to work):

                     Loop
                     Set Variable [$Fotocheck; Value:GetField ( Get ( LayoutTableName ) & "::Z_Foto" )]
                     Set Variable [$Staalcheck; Value:GetField ( Get (LayoutTableName ) & "::Z_Staal" )]
                     If [$Fotocheck = "NOK"]
                     Perform Script ["Mail1"]
                     Else If [$Staalcheck = "NOK"]
                     Perform Script ["Mail2"]
                     End If
                     Go to Record/Request/Page [Next; Exit after last]
                     End Loop

                      

                      

                • 5. Re: Calculated to e-mail
                  philmodjunk
                       

                            Thanks for the comment. Realised that the logic was correct but that in fact FM cannot calculate GetField within the IF statement if it is not between quotation marks, and there is no way to put it within quotation marks.

                       This is not the case. You do not need quotation marks, but you do need the calculation inside the parenthesis to return text that correctly names a field in the layout's table or in a related table or no value will be returned.

                       If[GetField ( Get ( LayoutTableName ) & "::Z_Foto" ) = "NOK"]

                       and

                       Set Variable [$Fotocheck; Value:GetField ( Get ( LayoutTableName ) & "::Z_Foto" )]
                       If [$Fotocheck = "NOK"]

                       Should produce identical results. I'd check to see if your original expression might have had the following syntax error:

                       If[GetField ( Get ( LayoutTableName ) & "::Z_Foto" = "NOK" ) ]