You appear to have these relationships:
Emails::EmailID = Customers::ID
CustomersID = Products::CustID
That makes what you want very simple, but I question whether those are really the correct relationships. Might you really have:
Customers::ID = Customer_Product::CustID
Products::ProductID = Customer_Product::ProductID
Otherwise, only one customer can be linked to a given product record.
But how would I perform the search for the records? The Find command only seems to work on one table at a time...
How you structure your relationships and tables can affect how you perform the find--that's why I asked for confirmation on the details of your design here.
Finds do pull up a found set of records from a specific table, but the criteria specified can be specified in the fields of a related table.
Using either structure that I posted in my previous post: On the customers layout you can specify "HP Color LaserJet 8500" in the Product Description field of Products and you will find all customers records that are linked to at least one Products record where the description field contains that text. This can be done manually if you put that field on your layout. It can be done via a global field and script even if the product description field is not present on the customers layout.
The structure is
Emails::Product = Products::ProductName
Customers::CustomersID = Products::CustID
Where Products holds the portal for the Customers table allowing me to add Products for customers from a static value list called ProductListStatic; I go into the Customers table and add records via the Products portal and that gives me a record with the CustID from Products:: and another field with the Product value. That part is great.
Then there is another value list called ProductListDynamic which contains the values from Products::ProductName. I'm hoping to be able to filter that list down to unique records later on though I probably should create a new table where I have a single record for each product. To keep things sane I should probably rename Products:: to CustProducts:: and create a new table called Products:: like I described.
The reason I'm relating on product name is that I'm confused about how I should realate the tables and keep everything "Normal"; ::EmailID makes just about as much sense to me as ::ProductName since I will not be creating one email per product. No relation actually makes more sense to me; the idea is to be able to use a Emails::ProductName to search the Products table and filter the Customers table to get the Customers::EmailAddress value I want to use in the email
Assuming that each email refers only to a single product, never multiple products, I'd link emails by ProductID, but then I'd also have a table of Products where there is one record for each product in order to generate those ProductID's.
Using your current setup, you can perform the find on the Products table and send out your emails from there, creating the Emails records as needed via a looping script that loops through the found set of records. The relationship to customers will enable you to access the email address for each customer that purchased that product.
If SendEmail is intended to prevent sending emails to customers that do not want these notifications, you can include criteria in this field when setting up your find on the Products table. Essentially, you'll be telling FileMaker: "find all products with this data in the description (or productID) field that also have a related customers record with this data in the SendEmail field".
You'll need to figure out if you can use the "email found set" option or if you'll need to loop through the found set of products (which you'd do anyway to create the records in the emails table) to send out the emails one send at a time.
What I have done is create two calculation fields in ProductsPerCustomer; EffectiveEmail and EffectiveProductID. EffectiveEmail displays the data in the Customers::Email if Customers::SendEmail="1". ProductsPercustomer::EffectiveProductID displays the value in ProductsPerCustomer::ProductID if Customers::SendEmail="1".
This works pretty well, I can see that ProductsPerCustomer is updated correctly if I check the Customers::SendEmail box.
However, when I create a relationship from Email::ProductID to ProductsPerCustomer::EffectiveProductID the portal in Email:: that displays records from ProductsPerCustomer doesn't display anything...well it did but I don't know what I did to break it.
I have doublechecked that all calculations are of type Number and I have tried every possible mutation of creating the relationship.
Hmmm, you don't actually need the EffectiveEmail field. You can specify search criteria directly in the Customers::SendEmail field on your ProductsPerCustomer layout. (Is ProductsPerCustomer what you previously called "Products"?)
I'm no longer sure I understand the purpose of the Emails table. What does one record in this table represent?
Each record in Emails contains the text that I want to send to all customers that match the criteria
Ok, then why do you need anything but the original ProductID field to use in the relationship? What are you trying to set up with this change?
If I'm right as to why, you can get the same results more easily with a filtered portal if you have Filemaker 11 or 12.
Thanks. That's the thing
Filtering on PatternCount ( ProductsPerCustomer::EffectiveEmail ; "@" ) did the trick!
If Customers::SendEmail is field of type number and 1 indicates that they should be emailed...
Customers::SendEmail should also work as your filter expression...