Combining 2 queries in Exact Event Manager Event

[lrousey]

I have 2 queries created...one that has detail payment information for a vendor (invoice, date, amount, etc). The second query only shows the total amount paid to the vendor. I need to include both these queries in an event on the email tab so that the vendor will receive an email that shows the invoices that were paid on a certain day, and the total for those invoices.

Has anyone ever done this before or have any idea how too? I can add the 2 queries, but I don't know how to link them together so that the correct total per vendor shows on the email.

Thanks in advance for any help you can give me!

Laura

 

[NEmacGuy]

I just took a hack at this for brain tester and gave up for now. I have done something very similar but started off in a different direction which worked for me. Instead of 2 querys, I use a crystal report for the notification which then I can basically can compose almost anything on want on the report. Then have a query that determines when the report should be triggered.
Only thing I have ever had to think about was weather to have the crystal report rendered as a pdf file (which forces it to be an attachment), or send as HTML which usually ends up in the body, but for some people their email system may treat it as an attachment.
I have, so far, always leaned towards PDF.

My guess is from you message is you want it all in the body of the email.

 

[lrousey]

Thanks for the info.

I did find a way to link them in EEM. In the first query I created, in the columns tab, I added the field that I wanted to link in both queries. I named it something unique like (creditornumberlink). Then on the second query, in the filters tab, I added that same field to link on and in the compare value column typed in {creditornumberlink}.

That worked for me. Maybe it will help you if you ever have the need.

Thanks again

Laura

 

[JayTheGeek]

It looks like there is a solution to this, but it seems complex. Is there a reason why a UNION of the two queries will not work?

 

[AXes]

Just confirming I've been able to use any of the 3 solutions proposed, depending on the specifics of the requirement. 1) Use a Crystal Report to do the heavy lifting like NEMacGuy. 2) Link the queries like Laura has done. Not very intuitive if you're trying to figure it out on your own, but the manual describes how to do it. 3) Edit the query manually in the query editor to union the selects like Jay.