3 Replies Latest reply on Jul 28, 2013 6:26 AM by philmodjunk

    email help needed

    LeslieCarr

      Title

      email help needed

      Post

           I have a simple directory database.  Four tables, Company info, Sales Offices, Products & Services.  I want to send an email to all the each company giving them the information we currently have on file and asking them to make changes.  I can send them a pdf of a layout that pulls everything together, I would rather send them something they can edit and return to me.  Any way of pulling information from the four tables into one email? 
           Thanks for any help.

        • 1. Re: email help needed
          philmodjunk

               Yes, but with just fileMaker, not an email plug in, your options will be limited. It may be possible to put that data into the body of your email as plain text if you don't need to get too fancy. The exact details of a calculation that can do that depend on the structure of your database and what you need to include in your report.

          • 2. Re: email help needed
            LeslieCarr

                 this is a very simple db.  Company and sales offices are linked through the company id.  There is a a join table that links company ids to product ids, and another join table that links company ids to service ids.  I tried to pull the sales offices into the company file, but I can't seem to make that work.  I thought I could then dump them into the email.  I tried exporting information, but it seems to only export the first instance of the related db. 

                 I could live with plain text for the email as long as I could list each sales office that is linked to the company, and the list of their products and services.

                  

            • 3. Re: email help needed
              philmodjunk

                   The body can be a calculation field that combines data from multiple fields, to include a return separted list of Sales offices in your email, use the list function: List ( SalesOffices::OfficeName ).

                   To include a list of products, you'll likely need a calculation that pulls multiple fields into a "mini table" of several columns. That can be done 1 of two ways:

                   1) Define a field in Products, cEmailProductLine as
                   ProductID & Char ( 9 ) & ProductDescription

                   Then List ( Products::cEmailProductLine ) can include this as a list of values in your email. Char (9 ), BTW, is the tab character.

                   2) If you have FileMaker 12, use ExecuteSQL to extract this data from Products using the Tab character as a field separator and the Return (¶) as the record separator.

                   Both options will produce a list of values to form a kind of plain text table, but you may find that differences in the amount of characters returned for each column of data keep the results from being acceptable and thus you still may want to investigate either using an email plug in or attaching a PDF of one of your layouts even though the result cannot be edited.

                   Note: If this has to be done on a fairly regular basis, you might consider using FileMaker Advanced to create a run time database to use for the updates. The client downloads the update once, then you email them a single database file containing just their data. They drop the file into the solutions folder they downloaded, open the file and update their information. Then a button click exports and attaches a file to an email that they send back to you where you import the updated data back into your system.