I have a DB of a few thousand contractor and trade licenses, all identified by type with a two or three letter code.  I am trying to build an email system so we can mass mail various groups of these license holders. I have SMTP emailing working well on other solutions in office, so I think I'm fine on that end.

 

I have been brainstorming for a while, and have come up with a couple of options, but not sure which is the best way to create this solution.

 

1. Script a multiple criteria find using a variable from a check box set of the license type abbreviations, then send to this found set.

 

2. Create a field that can be set to either 1 or 0, 1 representing "yes - email" and using the check box set, run through the records setting the field to the appropriate value.  Then find all "1s" and email that found set.

 

 

With either solution, I'm running up against my own limited understanding of list functions and variables.  Not sure how to roll through a field with multiple text data (GC, MC, MPG, etc) and pick the first for use, then back to use the second, then third, and stop when no more values.  This field could contain as few as one selection, up to 18 at this point.

 

Any suggestions would be GREATLY appreciated!

 

 

 

 

I believe the best way to find the relevant records would be through a relationship, using the Go to Related Record [] script step. If you plan on creating a record for each mail sent, then that's where your SendToTypes checkbox field would reside; otherwise just use a global text field in any table.

 

Performing a find with multiple requests (not multiple criteria) is certainly a viable alternative. Note, however, that you must be careful in designing the criteria, because normally searching for "MC" will also find "MCA".  Here you need to search for "=MC" to match a whole word only.

I’m not sure if I understand the problem correctly, but if so, hopefully I can be of assistance.

 

It sounds like you have a single text field formatted as checkboxes, and you want to send an email to everyone in the database with certain boxes checked. If so, you can create a global field using the same value list (also formatted as checkboxes), then create a script that sets a variable to all checked values in that global field and creates a separate Find Request for each value in that checkbox. Then you can use the Send Mail step with the “Multiple emails (one for each record in found set)” option. For example:

#Create a dialog window for the user to specify options.
Set Variable [$DialogName; Value:"Select Categories to Email"]
New Window [Name: $DialogName; Style: Dialog]
Go to Layout ["Options" (Globals)]
Pause/Resume Script [Indefinitely]

#Store the selected options in a variable and close the dialog.
Set Variable [$Categories; Value:Globals:Categories_g]
Close Window [$DialogName; Current file]

#Create a find request for each selected category
Enter Find Mode
Loop
 #
 #Set a counter that augments on each loop.
 Set Variable [$Counter; Value:$Counter + 1]
 #
 #Create a find request for value #[$Counter] in the $Categories list.
 Set Field [Contractors::category; "==" & GetValue ( $Categories ; $Counter )]
 #
 #If we’ve processed every value in $Categories, exit the loop.
 Exit Loop If [$Counter ≥ ValueCount ( Categories )]
 #
 #Otherwise, create a new request and repeat.
 New Record/Request
End Loop
#
#Perform the find.
Perform Find []
#
#Send the mail using Multiple emails (one for each record in found set)
Send Mail [Send via SMTP Server; No dialog]

Does that solve your problem?

Comment:  thanks for your response.  I can't say that I quite understand how I would go about utilizing a relationship to solve this issue.  I'll give that more thought tomorrow morning, when my brain is still functioning fairly well!

 

Otter: you summarized the problem much more succinctly than I did.  I'm working through your response and making some progress.  I'll report back when I see how this works out for me. 

 

Thanks to both - part of what makes FileMaker a GREAT product to work with is willing and helpful end users like you two!

 

Darren

the Otter - you nailed it.  Works great - thank you!