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
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.
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
WHERE Date = ?
; "" ; "" ;Get ( currentdate ))
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.
Thats exactly what I was looking for. Your work is amazing. Is there anywhere I can go to see other tips you have posted?
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.
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.)