Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Nickel Super Contributor
Posts: 105
Registered: ‎11-11-2009

Email Template Creation in SLX Using SQL Tables Other Than Contact

I've been trying to figure out how to create email template that pulls data from the Account and Opportunity tables as well as some fields from our custom functionality in SLX but I cannot seem to figure out how I can add tables into the "Insert SalesLogix Mail Merge Field" window in the Email Editor.

 

If anyone has any suggestions that will allow me to choose fields in the other tables mentioned above, it would be greatly appreciated!

 

-Dennis

Silver Super Contributor
Posts: 801
Registered: ‎03-24-2009

Re: Email Template Creation in SLX Using SQL Tables Other Than Contact

Right click the table id and choose "create local join" - you can then join from pretty much wherever to wherever.
Nickel Super Contributor
Posts: 105
Registered: ‎11-11-2009

Re: Email Template Creation in SLX Using SQL Tables Other Than Contact

I tried that before and did not allow me to access the information I was after as the main table for mail merge is the Contacts table.

 

What I am looking for is a way to add the Accounts and Opportunities tables into the main table column along with Contacts.  This will allow me to access the fields I need that the Contacts main table does not have a relationship with.

Employee
Posts: 535
Registered: ‎03-30-2009

Re: Email Template Creation in SLX Using SQL Tables Other Than Contact

I believe you can use the SQL Field feature to retrieve a specific field.  Repeat for each field you need.
Todd Hardin
SalesLogix Professional Services Group
Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: Email Template Creation in SLX Using SQL Tables Other Than Contact

Correct! 

Here's some info on creating a "Total SQL Field".

 

In a merge template:

 

  A - Click in the template where you want the merge field

 

  B - In the menu: Insert Special Field -> SQL -> Text

 

  C - Give it a unique name (Field Identifier) - ex: Total SQL Field 

 

  D - Click on teh elipsis (button) for teh SQL (an editor pops up)

 

  E - Put the following SQL IN the editor (and save - ok) :

 

       SELECT Sum(COALESCE(A1.EXTENDEDPRICE,0)) 

       FROM sysdba.OPPORTUNITY_PRODUCT A1, PRODUCT A2 

       WHERE A1.PRODUCTID = A2.PRODUCTID 

            AND A1.OPPORTUNITYID = Smiley SurprisedpportunityID

 

F -  Choose format type (for this one use type Currency)

 

That's basically all there is to it! 

 

--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html
Nickel Super Contributor
Posts: 105
Registered: ‎11-11-2009

Re: Email Template Creation in SLX Using SQL Tables Other Than Contact

Thanks guys!

 

I will give it a shot and let you know how it works out for me.

 

Thanks again!

 

-Dennis

Highlighted
Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: Email Template Creation in SLX Using SQL Tables Other Than Contact

You can also define a custom SQL "table" using the same basic model as a field. Of course you want to return multiple fields ;-)

 

Be careful w/formatting.. it's a bit of trial and error. 

--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html