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 ) )
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.
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.
(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.
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.