2 Replies Latest reply on Mar 27, 2009 4:18 AM by IanStone

    Newbie Help - Generate mailing letters



      Newbie Help - Generate mailing letters


      I have 2 tables.  Table 1 is called Projects and contains the following 2 text fields (Simplified):





      Table 2 is called vendors and contains detailed information of all bills paid to each vendor by project.  Field definition as follows (again simplified) 


      Project_Name  (Text field)

      Vendor_Name (Text field)

      Bill_Amount (Numeric field)

      Total_Billed  (Summarizes total of bill_amount)


      Sample of records in table 2 may be (formatted Project_Name, Vendor_Name, Bill_Amount)


      Project1, ABC Company, 200

      Project1, DFE Company, 850

      Project1, ABC Company, 340...


      I have defined a relationship between the 2 tables linking the Project_Name field and generated a layout that summarizes the activity by project.  A typical result is



      ABC Company $540

      DFE Company $850

      Project1    $1390


      Project 2


      and so on


      I would like to use this result set to generate a form letter that goes something like this


      Congratulations <<Vendor_Name>> you spent <<Total_Billed> on <<Project_Name at <<Project Address>>


      I can generate both the result set (in a layout) and the form letter but I can't figure out how to relate the result set to the form letter without exporting the data and creating a new table which seems superfluous.  



      Any help would be greatly appreciated. 



        • 1. Re: Newbie Help - Generate mailing letters

          I think that for your letter you need to summarize by vendor first, then by project. So your layout could be:


          [sub-summary by Vendor] Congratulations <<Vendor_Name>> you spent* <<Total_Billed>> on the following projects:

          [sub-summary by Project] • <<Project_Name at <<Project Address>>



          Not related to your question, but you should base your relationships on IDs (auto-entered serial numbers), not on names. Names can change, and you do not want your relationships to break when that happens. Also, it would be useful to have a Vendors table with one unique record for each vendor - so that your "Table 2" would become a table of Billings with fields for:

          • ProjectID

          • VendorID

          • Amount

          • sTotal





          (*) Spent? Weren't they PAID these amounts?

          • 2. Re: Newbie Help - Generate mailing letters

            Ahh! paradigm shift.  Thank you.


            You are correct in your assertions.  The sample sentence I provided would be better structured as you stated.  


            In this case I am not actually creating a database application  that will be long lived.  Rather I am cleaning up large amounts of data (that is in pretty bad shape).  The end goal is to send out lien waivers for a subset of projects.  This is a one time exercise and then the database has served its purpose.  I normally do this for clients on SQL Server or Access, but in this case the client is on Mac, has file maker pro and I thought I would experiment with it.  Nice app.  Once you move away from the query mindset the app is really easy to use and learn.  In fact I think I am going to add it to my arsenal.


            Thanks for your help