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

    Newbie Help - Generate mailing letters

    IanStone

      Title

      Newbie Help - Generate mailing letters

      Post

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

       

      Project_Name

      Project_Address

       

      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

       

      Project1

      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
          comment_1
            

          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
            IanStone
              

            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

             

            Ian