6 Replies Latest reply on Oct 29, 2014 1:23 PM by philmodjunk

    Send Email with totals

    jakes

      Title

      Send Email with totals

      Post

      I have a listview that is sorted by sub summary fields. that display totals.

      I want to be able to send an email every hour to users to notify them of the totals with what product.

      The problem is the product and totals change all the time so I can't hardcode it using sql.  I want to just go into the layout do a find on current date and time and whatever products appear, email those totals.

      How can I accomplish this?

        • 1. Re: Send Email with totals
          philmodjunk

          Don't see why you can't use ExecuteSQL if you set it up correctly.

          But if you perform a find, you can then either:

          a) from the context of your found set, include the summary field reference in the body of your email to report current totals

          or

          b) use set variable to copy this total to a variable so that you can change layouts to get a different context before setting up your email and sending it. The variable can be referred to in the body of the email.

          • 2. Re: Send Email with totals
            jakes

            Thanks Phil for your reply.

            Im still not able to accomplish what im trying to. Here is abit more info.

            I have 2 relevant fields.

            Date and Product in table Products

            I do a search on date and want to display the totals for the products that appear in the find.

            so if 2 products appear

            results must look like this:

            Product 1: Total: 2

            Product 2: Total 3

            Im happy to use ExecuteSQL, I guess I just haven't managed to configure it correctly.

            This will give me the total of products for todays date but it won't break it down for me to look like this

            Product 7: Total: 8

            Product 9: Total 1

            Heres the best I could do with sql

            ExecuteSQL ( 

            "SELECT COUNT(*)
            FROM Products
            WHERE Date  = ?

            "

             ; "" ; "" ;Get ( currentdate ))

            • 3. Re: Send Email with totals
              philmodjunk

              See this thread for an SQL method to get a "summary Recap": FMP 12 Tip: Summary Recaps (Portal Subtotals)

              But if you set up a summary report like you describe in your email, you can save it as a PDF and attach it to the email.

              • 4. Re: Send Email with totals
                jakes

                Thanks Phil,

                Thats exactly what I was looking for. Your work is amazing. Is there anywhere I can go to see other tips you have posted?

                • 5. Re: Send Email with totals
                  jakes

                  Hi Phil,

                  One last thing. The display looks really good. "I've used the Inspector to add a right justified tab with . as a leading character to get the recap data into columns."

                  Because I am pasting these totals in the body of an email is there anyway to get the same effect using filemakers functions without using the inspectors tab stops.

                  • 6. Re: Send Email with totals
                    philmodjunk

                    Unfortunately, your formatting options in FileMaker's Send Mail are very limited. To get that result you might have to use HTML tags and an email plug in that supports HTML formatting.

                    Is there anywhere I can go to see other tips you have posted?

                    You might check out the Adventures in FileMaking series. Adventures 1 and 2. They are free to download.

                    Adventures in FileMaking #1 - Conditional Value Lists (includes details on how to set up a basic field based value list)
                    Adventures in FileMaking #2 - Enhanced Value Selection (what to do when a simple value list won't cut it.)

                    Caulkins Consulting, Home of Adventures In FileMaking