SIMPLE FILEMAKER ExecuteSQL - Getting An Unlinked Email Address

From Dwayne Wright PMP, PMI-ACP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

There is a lot of great information out there on the FileMaker 12 ExecuteSQL function but I haven't come across anything in a recipe book format (like the old Visual QuickStart Guides of yesteryear). I'm not up for anything that ambitious on my own but I thought I'd try to got down little example snippets when I can.

SITUATION
Say that you are creating an APPROVALS database, something that has a document in a container field and multiple fields that hold the names of team members based on their approval role for the document.

Next to each name field, they want a small field that can be used to directly email that person. None of the fields are actually linked via a relationship to the STAFF table that holds their email addresses. You can actually do this without any relationships or any scripts. Here is a quick little ExecuteSQL snippet that will allow you to pull the email address from the staff table.

ExecuteSQL (
 
"SELECT Email_Address
FROM Staff
WHERE Full_LastFirst = ?"
;
 
""; "" ; APPROVALSHEET::roundOwner )


WRAPUP
Only the last string needs to be changed for each button to match the team member field to which it is associated. A very small amount of coding for a fairly advanced feature and the ExecuteSQL is the key enabler.  


EXAMPLE REALITY CHECK 1

Most developers would try to accommodate this requirement in a portal that can hold an almost unlimited amount of team members and have them associated with a STAFF table. For the sake of this example, lets say that the requirements for the solution and/or the project stakeholders do not want to go the portal route. They want ten name fields that are available from pull down menus for the various roles of an approval table.

EXAMPLE REALITY CHECK 2
You probably would want to script the send email function to accommodate customized messages, error conditions such as empty name or perhaps where an email address is not available for a person in the staff table.

© 2013 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.