Automator/Applescript Help with FileMaker Pro and Mail

[hkinzler]

On another note, I just realized one more kink in this wheel of a problem I've been trying to solve. I forgot to mention that this database holds around approximately 19,000 contacts. I then perform a simple search query to filter out contacts that have been in the database before 5/1/2006, and have e-mail contacts. When I get results, around 6400 contacts are found. (this is different than the previous 9000 I mentioned, I refined the search to be more detailed)

Now, it would be great if somehow the script that is in the process of being tweaked (the one barhar has graciously been writing and editing) could somehow filter this, but I feel that this might be somewhat of a hard task. I guess if worst comes to worst, all 19,000 contacts could be e-mailed; but the sheer fact that the script would take their first name and e-mail, and be able to automatically do this would be worth it.

Good Luck and Thanks to anyone helping in the past and future!

[hkinzler]

Well, I was able to manage scripting a filter for the problem I stated above. The code:

show (every record whose cell "GEN.LASTACCESS" is less than "5/1/2006" and cell "Gen.EMAIL" is "*")


This now cuts the 19,000 records to a mere 6,400. All thats left is fixing the cell values, getting them into mail, and start sending those e-mails! I hope someone out there can offer any solution. Forums used to be something I paid no mind to, but now I might become an avid poster!

Note-Because of the security of the database I'm working with, I don't have some privledges to modify cell values/data. I hope that this won't be a problem (probably will be, what isn't¿) If it is, I'm sure I can get the rights, and everything then will work smoothly. Modifying those values, the "set":

set {firstNames, eMailAddresses} to {(every cell of document 1 whose name is firstNameCell), (every cell of document 1 whose name is eMailAddressCell)}

This is what the scripts hung up on now. Thanks and Good Luck for past and future help!

[barhar]

With reference to ...

set {firstNames, eMailAddresses} to {(every cell of document 1 whose name is firstNameCell), (every cell of document 1 whose name is eMailAddressCell)}

..., at the beginning of my second reply is ...

property firstNameCell : "First_Name" -- Name of 'First Name' assigned cell.
property eMailAddressCell : "eMail_Address" -- Name of 'e-mail Address' assigned cell.

... You must replace 'First_name' and 'eMail_Address' (which are names I used when creating a test database file, to check the code written and posted) with the actual respective cell names of your FileMaker database file.

[hkinzler]

One of the first things I realized barhar was the difference in the code, and how I would have to change it to fit my file/misc. locations. The first name and e-mail fields are defined as "GEN.FNAME" and "GEN.EMAIL", respectively. They have been in the property line since you first coded it, however, now I don't know why its not finding those cells, or field values. Below is what I have:

property firstNameCell : "GEN.FNAME" -- Name of 'First Name' assigned cell.
property eMailAddressCell : "GEN.EMAIL" -- Name of 'e-mail Address' assigned cell.

property eM_Salutation : "Dear"
property eM_Subject : "Updating Contact Database"
property eM_Contents : "Message...I took it away for text purposes..."

tell application "FileMaker Pro"
activate
getURL "fmp7://[username]:[password]@192.168.0.103/Applicants"
tell table "Applicants"
show (every record whose cell "GEN.LASTACCESS" is less than "5/1/2006" and cell "Gen.EMAIL" is "*")
set {firstNames, eMailAddresses} to {(every cell of document 1 whose name is firstNameCell), (every cell of document 1 whose name is eMailAddressCell)}

-- quit -- Optional.
end tell

set eMailAddressesCount to count eMailAddresses -- Obtain number of items of 'eMailAddresses' list.
if ((eMailAddressesCount > 0) and ((count firstNames) = eMailAddressesCount)) then -- Lists must be equal in items, and greater than 0.
tell application "Mail"
activate

repeat with i from 1 to (count eMailAddresses)
set nMessage to make new outgoing message with properties {visible:false, subject:eM_Subject, contenteM_Salutation & " " & (item i of firstNames) & "," & return & return & eM_Contents)}
tell nMessage
make new to recipient at end of to recipients with properties {addressitem i of eMailAddresses)}
--send
say "Sent"
end tell
end repeat

-- quit -- Optional.
end tell
else -- This line, and the next four (4) lines, are optional.
set errorMessage to "An error occurred"
activate
say errorMessage
display dialog errorMessage buttons {"OK"} default button 1 giving up after 5
end if
end tell


On another note, would you mind briefly describing certain commands? I'm a bit confused what these commands mean, and hope you could clear up any confusion that I have.

1) The "set {firstnames,emailaddresses}" command line, what exactly does that do? is it renaming or modifying the cell values? My access privledges might not allow this...

2) Is the counting command neccessary? I understnad what it does (@ least I think I do...lol) I did get the whole script to run only once (it only needs to work once, natch) but it didn't do anything; other than open up and log into the database file, filter the records, then pull up mail, and then the mac says "Sent" <---nice touch!

3)Lastly, what exactly do the last 5 lines do exactly? Is it to combat any errors that arise?

Other than that, I'm feverishly searching for solutions, it seems the only one able to script well (not to mention, help) is barhar. Many thanks again, I hope we can get this to work!

[barhar]

Reply to '1)':

set {firstNames, eMailAddresses} to {(every cell of document 1 whose name is firstNameCell), (every cell of document 1 whose name is eMailAddressCell)}

... is no different than ...

set firstNames to (every cell of document 1 whose name is firstNameCell)
set eMailAddresses to (every cell of document 1 whose name is eMailAddressCell)

... The line of code ...

set firstNames to (every cell of document 1 whose name is firstNameCell)

... creates a list, titled 'firstNames', which contains all of the 'firstNameCell's of the database. Where 'firstNameCell' is 'GEN.LASTACCESS', in your database.

The line of code ...

set eMailAddresses to (every cell of document 1 whose name is eMailAddressCell)

... creates a list, titled 'eMailAddresses', which contains all of the 'eMailAddressCell's of the database. Where 'eMailAddressCell' is 'Gen.EMAI', in your database.

Reply to '2)':

Yes. The comment '-- Lists must be equal in items, and greater than 0.' as the end of the line of code is self explanatory.

If you look at the line above the 'say "Sent"' ... it is '--send'. You will have to remove the two '--'es (minus signs). The duo minuses 'comments out' the line. I did not realize I did not remove the '--'s when pasting the code into the reply. [I had no intention of sending countless e-Mail messages while testing the code, thus the '--'es ]

Remove the '--'es, and the script will actually send each person an e-Mail message.

Reply to '3)':

One has to look as the code ...

if ((eMailAddressesCount > 0) and ((count firstNames) = eMailAddressesCount)) then -- Lists must be equal in items, and greater than 0.
.
.
.
else -- This line, and the next four (4) lines, are optional.
set errorMessage to "An error occurred"
activate
say errorMessage
display dialog errorMessage buttons {"OK"} default button 1 giving up after 5
end if

..., Thus, if there are not an equal number of 'First_Name' and 'eMail_Address' items in the respective 'firstName' and 'eMailAddresses' lists - the code beneath 'else' is executed. You would hear 'An error occurred' and will see a dialog box, for up to five (5) seconds, displaying 'An error occurred'.

--

In conclusion:

Locate ...

--send

..., rename it as ...

send

..., and execute the code.

[hkinzler]

Very through explanation barhar, however, I still am having trouble with the script. I am getting (which has been what I've always been getting) an error that says that FileMaker Pro could not find object. Then this is highlighted: every cell of document 1 whose name is firstNameCell

Now, I have a question to ask. Is "document 1" the default file? or should I leave it like that? I'm a bit confused as to where to find it; seeing as I have only a database file located on a remote server.

On a side note, I'm pretty sure these are the correct cell names, I got them by accessing the template/form editor in FileMaker Pro and logically determined that "GEN.FNAME" is first name and "GEN.EMAIL" is e-mail. "GEN.LASTACCESS" is only data pertaining to the date. That 'filter' that I made, does reference "GEN.LASTACCESS" and "GEN.EMAIL", allowing me to just have only records with contacts who have e-mail addresses and were last accessed anytime before 5/1/06. I hope this helps with any confusion that anyone might be having, I'm sure I am still a little confused, but I can feel that the solution is there!

Thanks again barhar!