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.

A READER ASKS: FileMaker List Function Example (and more)

From Dwayne Wright PMP, PMI-ACP
Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

A READER ASKS
I read your post on the LIST function, do you have any example files?

-------
DWAYNE RESPONDS
I found a large function example file I used to use when I was doing FileMaker 9 training. This does contain the LIST function in the aggregate family tab. It also has a number of interactive examples of how to use the Text / Number / Date / Time / Functions. If or when I leave my in house developer position, I'll probably dust this off and add the new functions to it.

Here is the link for the download (click here)

© 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.

The Little Known FileMaker 12 File Access Tab

From Dwayne Wright PMP, PMI-ACP
Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Did you know that you have the capability to limit connections to your FileMaker 12 file? Say that you have developed a solution for one workgroup, another workgroup uses the solution but you have concerns the second workgroup might try to blend one of their FileMaker files to yours. There is a tab in the Manage Security dialog box called "File Access" which has a check box "ON" control and then a mechanism to load authorized access to specific external files.

From what I've read in the FileMaker 12 Security Guide,
each authorized file is assigned a unique identifier, which the protected file keeps track of, ensuring that the protected file remains protected even if it is renamed or duplicated. Any efforts to bypass authorization, such as by replacing an authorized file with a different one, will be unsuccessful.

If you want read into this further, here is a link to check out ...
http://fmhelp.filemaker.com/fmphelp_12/en/html/passwords.14.33.html#1043162

© 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.

A READER ASKS: Users Prompted For FileMaker Passwords Again And Again

From Dwayne Wright PMP, PMI-ACP
Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

A READER ASKS
I created some FileMaker databases to help me with my job and they work great! About a year ago, we purchased FileMaker Server and my databases are being used by the entire workgroup. I'm getting reports from just a couple users that they are having to enter their passwords multiple times to access FMP. It is just those two people and the problem comes and goes. Any ideas?

-------
DWAYNE RESPONDS
I've seen this happen with users that really embrace the FileMaker snapshot link feature. I have some users in which their primary daily experience with FileMaker is completely snapshot link driven. What they do is double click a link sent to them or they made, do their work and then close the window. Since it is the only window open, it closes the entire file. So when they click the next snapshot link, they are prompted for credentials again.

Most of this pattern appears to be the Macintosh users, oddly enough and use of the Keychain can help here. In my case, I used the LastWindowClose script trigger and a script to detect if it is the only window open for the file. If so, I minimize the window instead of closing it. Seems to fix the problem for my users and you might want to give that a try.

© 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.

A READER ASKS: FileMaker 12 Images Look Awful On Windows 7

From Dwayne Wright PMP, PMI-ACP
Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

A READER ASKS
I created a FileMaker database that allows users in my office to view images and/or PDFs to facilitate an approval process for content we produce. We have a mixed network of Macs and Windows 7 machines. I develop on a Macintosh. I thought everything was working great until I got reports that come of the images look awful on Windows 7 user machines.

Thoughts?

-------
DWAYNE RESPONDS
If the file in the container field is a PDF, the user is on a Windows 7 machine and the container field setting is for image optimization, the resulting image may look bad. On the very same setup on a Macintosh, it will look just fine. The key resolution I have found is in the "Optimize For" setting. This can be found by clicking on the container field in layout mode and accessing the "Data" tab in the Inspector.

Since you have a mixed bag of image or pdf data in that field, you will need to resolve the optimization setting for the data type at hand. I'm sure there are many ways to go about this but the most direct would probably be the use of the OnRecordLoad script trigger. The idea is that using the GetAsText function on the container and a tiny bit of parsing, you can detect what file type has been loaded into the field. I would put the container field into a tab object with the container field in both tab areas with the optimization setting different for each. Give each tab in the object a name that refers to that optimization type for that field (Image, PDF). Wrapping it all up, you can have a script go to the correct tab based upon the detected container data field type and have it automatically trigger when a record loads.

© 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.