One way to accomplish this could be to set the Requested by email field to be a calculation field using the Case function:
Requestedby = "John";
Requestedby = "Jane";
A downside to this is anytime you need to add or remove from the RequestedBy value list, you would need to edit the calculation to include, remove the person.
If the person making the request is a staff member then I would suggest you use a Staff table - you really should have one anyway. Here is a file showing how you might resolve your issue using a Staff table. In relational design, the goal is to eliminate redundancy.
You do not really need the email address in your Purchase Orders table since you have it in your Staff table, so you can just place the email address from your Staff table directly on your layout (see the blue email field on the Purchase Order layout).
If it is important to capture the Staff's email at the time the Purchase Order was created then you can add a field into your Purchase Order (like you had started) and then use a Lookup (or auto-enter calculation) to plant that staff's email into the PO. Choice is yours.
Message was edited by: LaRetta - you need to sign into TechNet to download (and see) the file I've attached.
SelectEmails.fp7.zip 5.9 K
Assuming you have a relationship between the name and the e-mails address and both the Requested by fields are in the same table/record you can create a dependancy between the field by using a let statement.
Formula for Requested by e-mail
nm = Reqestedby; // This creates the dependancy between fields. FileMaker will do the rest.
Anytime the Requested by field is updated, the Requestedbye-mail field will get the new e-mail address.
Thank you Bruce, Matt and LaRetta.