I am trying to run an automated server script that spans across two files. Here is the setup:

In File1 there is a Staff table that lists all demographic information for employees. In File2 there is a table for time sheets. The two tables/files are linked by staff_id. I want to run a script that automatically emails staff who have overdue time sheets. This works when the email address is stored in the time sheet table, but the real location is in the staff table in File1.

Doing some Googling I have seen that when a server runs a script it cannot go between files even though there are relationships between the two. Is there a way to directly reference a related field so that the server can grab data from another file?

First, welcome to FM Forums.

I have seen that when a server runs a script it cannot go between files even though there are relationships between the two. Is there a way to directly reference a related field so that the server can grab data from another file?

I know this not to be the case. The script can easily reference data in related files just as it does if it ran on the client side.

Steven

When I run the script and hard code my email address and put the referenced staff email in the body, the body is blank.

Placing the referenced email field in the To: line of the send mail script step results in an error "100" on the server which means "File is missing".

Running the script on the client sends mail as intended. I have tested this with both Filemaker Server 10 and 11.

You've found "overdue" time sheets, and are looping thru them and sending emails to the related staff. So, if you are in the context of timesheets, you are using a relationship from timesheets to staff by staffID to get the email address? Have you checked for a context issue?

I am not sure what you mean by a context issue. The script runs fine on the client side. It is the server side that it fails to pull in related data.

Have you seen this? http://www.emptyofwhat.com/node/30

Also, apparently, clearing the "Name" field in the SMTP options setup helps.

In a calculated field, how exactly would I call a field in another file?

From that posting it shows file:Folder/FileB.fp7 but I don't know how that would translate to point to a specific field in a table.

You reference a field in another file thru a relationship. You said that you have the relationship. What Todd points out in his post, is that the file path to the external source is different when run from Server. So, you need to update your external source file paths (Manage External Data Sources.