7 Replies Latest reply on Apr 29, 2016 10:26 PM by fredrick

    Create text for an Email using multiple records from related table

    fredrick

      Hi,

       

      I'm in a bit of a situation here...

       

      Not new to Filemaker but no expert either I am having an issue to create the text for my email in the correct way. Perhaps the solution is staring me in the face, I'm grateful for any help.

       

      The issue is as follows.

      I'm building a solution to help  sales.

      One of the functions that I would like to include is to send quotes to customers.

      Each quote can have multiple line items, and I have used a simple setup where there is a Quotes table and a Quote Line items table linked through the Quote ID (and further linked to the customer data, product data in their respective table, etc)

       

      I would like to make an Email that says something like this:

       

      ***

      Dear Mr XYZ,

       

      Please find below our offer.

       

      01. Chocolate Cookies

      Amount: 3000 Boxes

      Price per Box: USD 3,0

      Shipment: August

      Incoterm: CIF New York

       

      02. Blueberry Muffins

      Amount: 6000 Pcs

      Price per piece: USD 0,5

      Shipment: August

      Incoterm: FCA Washington

       

      We hope to have made you a good offer, (now buy our product. )

       

      Kind regards,

       

      Cookie Company.

       

       

       

       

      ***

       

      I have thought about making a PDF file, but most people would find that a hassle, and difficult to use, answer to, etc.

       

      Therefore I wanted to put this into the body of the email, but the way that I can do this is giving me issues.

      I think that I basically need to loop through the related Quote Line items output the text and end loop after the last one insert the salutation and done.

      BUT, I cannot execute a loop in a calculation, and the email body in the email script is a calculation.

       

      Then I thought I can create a Global Field where I input the text from a script when I loop, but in a way I seem to be unable to get my head round a way to insert the text output into a global field...

       

      Perhaps there is a completely different way to do this?

       

      Is there anyone who would have an idea to help me?

       

      Fred

        • 1. Re: Create text for an Email using multiple records from related table
          beverly

          print as pdf and attach? export as Excel and attach?

          otherwise, you must loop the items and push into a variable (or field) that can be called in the body of the email. keep in mind that PLAIN TEXT email (such as from FileMaker is not always the way you thought in the various email clients. The attachment helps

          beverly

          • 2. Re: Create text for an Email using multiple records from related table
            rgordon

            create a calc field in your line items table that contains all of the data you want for the email.  Then use the List function to gather all of the calc fields.  Your calc would be:

            Whatever you want at the top &¶& List(Your relationship::lineitemscalc) &¶& whatever you want at the bottom.

            • 3. Re: Create text for an Email using multiple records from related table
              erolst

              fredrick wrote:

              BUT, I cannot execute a loop in a calculation, and the email body in the email script is a calculation.

              The mail body is a calculation, but what calculation is up to you; e.g. use a loop and write to a $var …

               

              Loop

                Set Variable [ $thisRecord ; /* see below */ ]

                Set Variable ( $mailBody ; List ( $mailBody; $thisRecord & ¶ )

                Go to Record [ next ; exit after last ]

              End Loop

              Set Variable [ $mailBody ; $boilerPlateHeader & ¶ & mailBody & ¶ & $boilerPlateFooter )

               

              In your mail field, reference $mailBody

               

              ** e.g. calculate this format:

               

              01. Chocolate Cookies

              Amount: 3000 Boxes

              Price per Box: USD 3,0

              Shipment: August

              Incoterm: CIF New York

               

              like so:

               

              List (

                 SerialIncrement ( "00" ; Get ( RecordNumber ) ) & ": " & Product::name ; // assumes you constrain the found set to line items of this order

                "Amount: " & DisplayAsUSD ( LineItem::amount ) & " boxes"

                "Price per box: " & NumberAsUSD ( LineItem::price ) ; // assumes you have a Custom Function NumberAsUSD() that does just that

                "Shipment: " & MonthName ( Quote::dateShipment ) ;

                "Icoterm: " & Order::terminal

              )

              • 4. Re: Create text for an Email using multiple records from related table
                fredrick

                Wow you guys are quick! Thank you for the good ideas.

                I wanted to avoid an attachment so the pdf and excel I cannot use.

                 

                I will sound like a complete newbie, but I'm worried about the text format in calculation field, or even the $var.

                I will add information both as text and from the fields:

                 

                "Dear Mr " & Name of Person & "¶¶"

                 

                # the above can be done in the normal email body calculation, but the following section would come from the calculated field of the Variable:

                 

                & Email Body Text calculation ......

                 

                 

                Which would have to built up something like this as a combination of fields and inserted text and carriage returns:

                 

                & Product line number & Product & "¶"

                & "Amount" & amount of product field & "¶"

                 

                etc....

                 

                Will I be able to get such a lay out in a Filed that receives bits of information from a loop in a script?

                 

                Perhaps I'm understanding something wrong here...?

                 

                Fred

                • 5. Re: Create text for an Email using multiple records from related table
                  rgordon

                  You don't need to worry about the format.  If it doesn't look right the first time, you can always tweak it until it looks right.  One thing that might be a little confusing for you is erolst use the List function to combine all of the fields in one line item.  This allowed him to do it without any returns.  Pretty cool way of doing this.  The List function will add the return after every segment.

                  • 6. Re: Create text for an Email using multiple records from related table
                    erolst

                    fredrick wrote:

                    # the above can be done in the normal email body calculation, but the following section would come from the calculated field of the Variable:

                     

                    & Email Body Text calculation ......

                    Which would have to built up something like this as a combination of fields and inserted text and carriage returns:

                    & Product line number & Product & "¶"

                    & "Amount" & amount of product field & "¶"

                    etc....

                    Will I be able to get such a lay out in a Filed that receives bits of information from a loop in a script?

                     

                    You're doing this from the LineItem context, so you have access to all related records – from Client, Quote and Product.

                     

                    And yes, I prefer List() – you don't have to check if the expression is empty on the first run, and avoid a bunch of '& ¶ &'. (But you still need to sometimes fiddle a bit to get the correct number of line breaks.)

                     

                    See the attached file for a practical example.

                    • 7. Re: Create text for an Email using multiple records from related table
                      fredrick

                      YEAH! It worked. Thanks Erolst!

                       

                      Ok looking back at what I missed, is the proper use of the combination of the Variable and the List function.

                      I did not understand two things:

                      One can add from the loop to the Variable and it will "paste" the value of each occurrence in the loop at the end of the variable (I hope that's what it does...) and that one can use "formatting", such as combining text and field contents in a list function. (I still don't understand HOW the last thing works, I am just happy it does!)

                       

                      Well I am really happy you showed it with your script, because I would not have understood it otherwise.

                       

                      There is even an added bonus to it in your example, since you showed me to use the SerialIncrement to assign a number to the 'item' of the quote.

                       

                      Your very grateful Fred.