5 Replies Latest reply on Feb 20, 2015 2:27 PM by philmodjunk

    Help with If/then or case script step. Send Mail script.

    EricClute

      Title

      Help with If/then or case script step. Send Mail script.

      Post

      Hello!

       

      I'm very new to Filemaker and script writing, so please play nicely.  I'm writing a script that will compose an email automatically for the user. However I cannot figure out the calculation I need to grab the correct email addresses.  

      I have a field called "paper authors::pacontactauthor" that, when a "1" is in the field (it's a checkbox), it indicates that this specific author should receive the email.  I need the script to enter another layout and grab any email addresses associated with the contact author. We have 2 separate fields for email addresses, and I want both (if there are any).  

      What I have tried hasn't worked.  I either end up with no emails, every email for every author (not just the contact author), or with a "blank" email intermixed with the emails I want.  

       

      If ( PaperAuthors::PAContactAuthor = "1" ; GetField ( If ( Authors::Email1 ≠ "" ; Authors::Email1 ; Authors::Email2 ) ) )

       

      The above is my best guess, but alas, it doesn't work. Should I be using the Case function?

      I'm running FM 13.0v5 Pro Advanced in OSX 10.9.5.  

      Thanks!

        • 1. Re: Help with If/then or case script step. Send Mail script.
          philmodjunk

          This is not the correct use of GetField. GetField expects text in the form of TableOccurrenceName::FieldName that identifies the field from which to "get" the data it then returns.

          It's also important to keep in mind that a "layout" and a "table" may have exactly the same name but they are not the same thing. It appears that you need to get data from a different table--possibly a related record of that table, but it is not clear from your post what, if any relationship links PaperAuthors to Authors.

          If a valid relationship links the current record of PaperAuthors to the correct record in Authors and your script is currently on the PaperAuthors layout, then this expression should be what you would use:

          If ( PaperAuthors::PAContactAuthor = 1 ; If ( Authors::Email1 ≠ "" ; Authors::Email1 ; Authors::Email2 ) )

          If PAContactAuthor is defined as a number field, it can be simplified to be:

          If ( PaperAuthors::PAContactAuthor ; If ( Authors::Email1 ≠ "" ; Authors::Email1 ; Authors::Email2 ) )

          • 2. Re: Help with If/then or case script step. Send Mail script.
            EricClute

            Thanks for the response! Though I apologize for not being clear.  The relationship I am using is as follows:

            Papers - PaperAuthors - Authors

            with "PaperAuthors" acting as the join table, as there can be multiple authors associated with multiple papers.  The script needs to be run on a layout from the "Papers" perspective.  On that layout, I am using a portal to view the associated authors for that paper via the "PaperAuthors" table.  Through this association, I have an author marked as the "Contact" for that specific paper.  That's why the "PAContactAuthor" field is in the "PaperAuthors" table. 

            The field "PAContactAuthor" is currently a text field, not a number field.  

            What needs to be changed with the above calculation?  I tried it and it did not work. An email was created, but it was empty and without email addresses.  

            • 3. Re: Help with If/then or case script step. Send Mail script.
              philmodjunk

              It's a matter of the fact that your relationships are not those that work for what you want to do. They work as a basic many to many relationship between papers and authors so that one author can be linked to multiple papers and a paper can be linked to multiple authors.

              But in this case, you need to link to just one specified author and your current relationships do not support that. There are several approaches that might be used to correct that problem:

              a) The ExecuteSQL() function can be used to query your Authors table for email addresses with a WHERE clause that limits the result to the join table record with 1 in the PAContactAuthor field.

              b) You can add a different occurrence of PaperAuthors and link it to Papers like this:

              Papers::constOne = PaperAuthors|Contact::PAContactAuthor
              and this new occurrence of PaperAuthors would then link to another occurrence of Authors with the same match fields as you already use.

              Then you can refer to email address fields from this added occurrence of Authors to refer to the correct email address.

              c) but here's a simpler way:
              Open manage | Database | Relationships.
              Double click the relationship line linking Papers to PaperAuthors to open up a dialog box for relationship details
              Click the sort option for PaperAuthors and specify a sort order that sorts these fields by PAContactAuthor in descending order. This should make the record where you specify a "1" in this field the first related record for a given record in Papers.
              The calculation that I specified previously should now work.

              • 4. Re: Help with If/then or case script step. Send Mail script.
                EricClute

                (C). It worked! However I just realized that there are instances where 2 or more contact authors are indicated, and the script does not work in that case. Side note: would messing with this sort option be an issue later on?  It seems to me this is more of a workaround rather than a solution.  But as I said, I'm very new to filemaker, so I could be way off. 

                (B). By "Papers::constOne" do you mean the same match fields I already use? You mention at the beginning to create a new occurrence of PaperAuthors, but later mention an occurrence of "Authors" which are two different tables. I am confused, do I create a new occurrence for both?  Additionally, how could I reference the newly created occurrence (if you did mean "PaperAuthors") for the email fields when they reside in the "Authors" table?

                Thanks again for your guidance. 

                • 5. Re: Help with If/then or case script step. Send Mail script.
                  philmodjunk

                  b) constOne would be a field that always has the value 1 so that this relationship always matches to the join table records with 1 in the specified match field. I typically define such a field as a calculation field and give it the number 1 as its sole calculation term.

                  But my answers all assumed 1 and only one author record for this email. If you have multiple authors to email for a given paper, you'll need something like substitute ( List ( author|email::EmailField ) ; ¶ ; "; " )

                  To generate the needed list of email addresses separated by semi colon delimiters. But for some reason, you have two email addresses for a given author record shown in your original example. That will add another layer of complexity to your calculation if you want to reference more than one email address for a given author record.