Question:
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.
Answer:
You can use the SQL Field feature.
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 the elipsis (button) for the 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 = OpportunityID
Choose format type of Currency.
