Email Template Creation in SLX Using SQL Tables Other Than Contact

[dennissekulic]

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

[MikeSpragg]

Right click the table id and choose "create local join" - you can then join from pretty much wherever to wherever.

[dennissekulic]

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.

[ToddHardin]

I believe you can use the SQL Field feature to retrieve a specific field.  Repeat for each field you need.

[RJLedger]

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 = pportunityID

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

That's basically all there is to it! 

[dennissekulic]

Thanks guys!

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

Thanks again!

-Dennis

[RJLedger]

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.