3 Replies Latest reply on Dec 9, 2016 8:56 AM by philmodjunk

    Using List function in body of Email


      I was having difficulty getting the contents of a List function on a related table's calculated field to send in the body of an Email correctly.  Only the first related record's value were coming thru in the email body.  When I put the email body into a custom dialog to view it, it would display correctly, it was just not passing thru to the send email step correctly.  Using mac and windows clients and fmp 15 on a windows server.


      What didn't work:

      Using both the List function or a Set Variable to the List function in the email body.


      What DID WORK:

      using Set Field (to a global field) to the List function and then using that global field in the email body did work.


      Is there any reason the first 2 methods wouldn't work properly?

      Any explanations? I'm just trying to understand for future usage and post here in case someone else had the same problem.

      more detail below:


      I have an Orders table from which I'm sending an email.

      In the Related Line Items table I have a text calculation field "Text_for_Email" building properly with several text fields appended to each other, e.g. where <p> is paragraph return

      "Item # Qty Description <p>

      Cost <p>

      Total Amount"


      Then in the Orders table email script I wanted to use the following in the body of the email.
      List (Text_for_Email), e.g.:


      "Item #relatedline1 Qty1 Description1 <p>

      Cost1 <p>

      Total Amount1<p>

      Item #relatedline2 Qty2 Description2 <p>

      Cost2 <p>

      Total Amount2<p>

      Item #relatedline3 Qty3 Description3 <p>

      Cost3 <p>

      Total Amount3"


      But only the contents of the first Text for Email field were being sent to the email until I used the List function in a global field.


      Thank you in advance, kathy

        • 1. Re: Using List function in body of Email

          What you describe should work and equally well whether you use a variable, a field or just make it a calculation inside the "Body" box of the Send Mail dialog. Have done similar of all three frequently.


          Note that the correct syntax in all three cases would be: List ( RelatedTable::Field ) not List ( Field ).


          And while that should work, you can also eliminate the need for the calculation field in the related table these days as you can use ExecuteSQL to build your list of text values from multiple fields and thus remove one field from what is defined in your related table.

          • 2. Re: Using List function in body of Email

            glad to know they should work, at least my understanding of these 'tools' seems correct, but they didn't produce the desired result.


            and yes, was using List (related TO:: field) I just simply typed in the post List (field)


            using ExecuteSQL would be a tool for me, haven't ventured there yet.


            I appreciate your time in replying

            good news is, we're getting the desired result, it just took a lot longer getting there.


            • 3. Re: Using List function in body of Email

              And the point that I was trying to make is that since all of the approaches should have produced the correct result, you should revisit each method to see what was different that may have kept it from working.


              Two ways that I can think of that List might fail for you for any of these approaches:


              Wrong Context. List ( RelatedTable::Field ) evaluates from either the context of the current record in the current layout's table occurrence (Script step) or from a record in a table where the calculation is defined via the "context" table occurrence specified for that calculation (calculation field or auto-entered calculation). If you get that "starting point" table occurrence wrong, you might not be matching the related records that you expect and thus list returns a different list of values than you expected.


              Failure to update:

              If changes have been made by another user or in a different window and have not yet been committed, these changes will not be returned by the function as they aren't yet "there" to reference. Also, if you are using an auto-enter calculation instead of a field of type calculation, changes to the records in the related table will not trigger an update of the auto-enter calculation.


              Edit Note: just added a bit more info to the "wrong context' paragraph...