Adding a query to an email

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
Mark HHP
Posts: 387
Joined: Mon May 06, 2013 6:59 am
Location: Cape Town, South Africa

Adding a query to an email

Post by Mark HHP »

Hey guys.

So I need to automate an email to go out daily with the Consultants Calls and Interactions. So basically just need to pull a Query showing the following details (see attachment)

So either it runs query at a certain time then attaches the csv to email somehow or embeds the query in the email. I don't know, I've hardly touched the email features. Has anyone done anything similar to this? And for some reason, I've never had success scheduling things. But that's probably just me
Attachments
Interactions.jpg
Interactions.jpg (24.55 KiB) Viewed 14799 times
Mark
Running V5.7 (Build 1714) Linux Server. MySQL
weblike
Posts: 1165
Joined: Sun Dec 02, 2012 12:00 pm
Location: Europe

Re: Adding a query to an email

Post by weblike »

Hi Mark,

PM me, and I will help gladly. I'm using a lot email features...

Cheers,
Thx,
George
________________________________
Developer Edition
AwareIM: v8.5, build 2824
OS: Windows Server 2012
DB: MySql 5.6.42
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: Adding a query to an email

Post by tford »

Mark,

You can accomplish this with LIST_TABLE. Following is the initial value of the Message attribute in an Outgoing Email notification:
email.jpg
email.jpg (122.35 KiB) Viewed 14784 times
For example, "Re-enrollment Stats" is a query used in the first table displayed in this email. There are 5 columns shown in this first table in the email. See the User Guide for the syntax of LIST_TABLE.

I suggest a 3 step approach:
1) Get the Query to work that you would like to display in the email, but don't associate with an email yet
2) Build and test the email using the query from #1, but don't try to schedule it yet.
3) Once you've done #1 and #2 --> schedule it.
Tom - V8.8 build 3137 - MySql / PostGres
Mark HHP
Posts: 387
Joined: Mon May 06, 2013 6:59 am
Location: Cape Town, South Africa

Re: Adding a query to an email

Post by Mark HHP »

Ok cool. I now have two different ways to tackle this. Thanks so much.
Mark
Running V5.7 (Build 1714) Linux Server. MySQL
Mark HHP
Posts: 387
Joined: Mon May 06, 2013 6:59 am
Location: Cape Town, South Africa

Re: Adding a query to an email

Post by Mark HHP »

So Tom, you seem to do a lot of work regarding schools? I'm always interested to see different and varying applications for Aware, not just CRMs and business applications.
Mark
Running V5.7 (Build 1714) Linux Server. MySQL
poshaccy
Posts: 11
Joined: Wed May 11, 2022 11:55 pm

Re: Adding a query to an email

Post by poshaccy »

Hi, although this is an old post I have found it useful in sending a summary of "daily sales invoices" via an email.

I have set up an email that sents the data which is listed in the body of the text, it uses a query `Sales AnalysisDate`, see below

<<LIST_TABLE (`Sales AnalysisDate`,`CalculatedName`,`Description`,`Amount`,`TotalPurchases`,`TotalTyrePurchases`)>>

However, when the query is run as a normal query within AIM it also sorts the data by an attribute and then displays subtotals - can the table within the email be sorted by an attribute and can subtotals also be included within the table?

Many thanks
Paul
Jaymer
Posts: 2528
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Adding a query to an email

Post by Jaymer »

search this forum for "LIST_TABLE"

i'd say NO on Subtotals

But just as easy (kinda) to run a report to a PDF and email that.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
hpl123
Posts: 2633
Joined: Fri Feb 01, 2013 1:13 pm
Location: Scandinavia

Re: Adding a query to an email

Post by hpl123 »

Use LIST_TABLE_START instead of LIST_TABLE. LIST_TABLE_START and LIST_TABLE_END just print the data and not the actual query/grid i.e you provide that yourself (HTML) and if I am not mistaken, you can sort the data any way you want and to do that, use a query in the function (e.g LIST_TABLE_START('MyQuery')) and set the sorting in the query itself. This function doesn´t display subtotals but you can add that yourself in most cases (add the subtotal attributes below the actual query/grid in the HTML).
Henrik (V8 Developer Ed. - Windows)
Post Reply