11 Replies Latest reply on May 4, 2012 1:06 PM by philmodjunk

    Three table join?

    ArnorKristjansson

      Title

      Three table join?

      Post

      I wasn't sure what I should have in the title :-P

       

      I'm looking for a way to send an email to all customers that want an email for certain products.

       

      I have three tables

      Customers:

      ID (number)

      Name (text)

      email Address (text)

      SendEmail (number/boolean)

      etc

       

      Products:

      CustID -> Customers(ID)

      Product name (text) <-values from another table

       

      Emails:

      EmailID (number)

      Subject (text)

      Body (text)

       

      The story is this: I have customers that I want to send an email to. Some customers have some products that I want to contact them about from time to time. F.x. if an HP 8500 toner comes on sale I want to be able to email the customers that have bought that printer _if_ the email checkbox is checked.

       

      The data would look like this:

      Customers:

      ID "1"

      Name "Splurge Inc."

      email Address "splurge@example.com"

      SendEmail "1"

      etc

       

      Products:

      CustID "1"

      Product name "HP Color LaserJet 8500"

       

      Emails:

      EmailID "1"

      Subject "HP Color LaserJet 8500 toner sale"

      Body "Foo bar"

       

      What I don't understand is how I would perform a find from the Email table/database so that an email would only be sent for certain Customers (the one where SendEmail=1) and certain products

        • 1. Re: Three table join?
          philmodjunk

          You appear to have these relationships:

          Emails>----Customers------<Products

          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:

          Emails>-----Customers-----<Customer_Product>------Products

          Customers::ID = Customer_Product::CustID
          Products::ProductID = Customer_Product::ProductID

          Otherwise, only one customer can be linked to a given product record.

          • 2. Re: Three table join?
            ArnorKristjansson

            OK.

             

            But how would I perform the search for the records? The Find command only seems to work on one table at a time...

            • 3. Re: Three table join?
              philmodjunk

              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.

              • 4. Re: Three table join?
                ArnorKristjansson

                The structure is

                Customers------<Products--------<Emails

                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

                 

                • 5. Re: Three table join?
                  philmodjunk

                  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.

                  • 6. Re: Three table join?
                    ArnorKristjansson

                    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.

                    • 7. Re: Three table join?
                      philmodjunk

                      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?

                      • 8. Re: Three table join?
                        ArnorKristjansson

                        Each record in Emails contains the text that I want to send to all customers that match the criteria

                        • 9. Re: Three table join?
                          philmodjunk

                          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.

                          • 10. Re: Three table join?
                            ArnorKristjansson

                            Thanks. That's the thing

                            Filtering on PatternCount ( ProductsPerCustomer::EffectiveEmail ; "@" ) did the trick!

                            • 11. Re: Three table join?
                              philmodjunk

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