We just had a client ask or support team how to display one to many data (or a list) in merge forms. This is very achievable, the process is pretty straight forward and can really expand the use of your merge forms.
As is the Customer FX way we want to share with the SLX user community so I though I could take a break from Crystal Functions and write up the how to for you. The scenario I will use today is you want to create a Contact Merge form that list all of the Opportunities a contact has been involved in. I am assuming that you already have the Contact side of things developed in your merge form and are ready to add the list of opportunities.
Open your merge form for editing by using the SalesLogix Write/Template menu item.
Find the merge form in the list and select the Edit button at the top of the Manage Templates dialog. Note, if the merge form is in the public folder the system will make save a copy for editing. Find that merge form down in the Private Templates section of the tree control.
With the Merge Form open find the location in the document where you want to add the table for your Opportunity information and place your cursor.
Select the “Insert Special Field” button, highlight the SQL menu item and then select table from the list that appears.
You should see a “New SalesLogix SQL Field” added to your merge form and the SalesLogix Mail Merge Editor should appear. If it does not you can select the Edit field located at the top of the Letter Editor.
In the list you will find an icon that looks like a little table, the type will equal SQL. Highlight the value in the list on the left and the properties will appear in the dialog on the right.
The Field Identifier – allows you to name this control on your form.
The SQL field is where you add your SQL Statement that will collect the one to many opportunity data for your Contact. You select the button on the right side of the SQL field to open a dialog that allows you to enter your SQL Statement. You can use SQL to help develop your statement. If you use SQL, log in as sysdba, then you should be able to copy your statement directly into this dialog. My SQL Statement looks like this:
SELECT OPPORTUNITY.DESCRIPTION
, OPPORTUNITY.ACTUALAMOUNT
, OPPORTUNITY.ACTUALCLOSE
FROM
CONTACT left join OPPORTUNITY_CONTACT on CONTACT.CONTACTID = OPPORTUNITY_CONTACT.CONTACTID
inner join OPPORTUNITY on OPPORTUNITY_CONTACT.OPPORTUNITYID = OPPORTUNITY.OPPORTUNITYID
Where CONTACT.CONTACTID = :ContactID
Order By OPPORTUNITY.DESCRIPTION
Notice the where statement contains the = :ContactId. This sets your SQL up to run in the merge form. The ContactID is passed through to the form when you run the merge process.
When Your SQL statement is entered select the OK button.
Options: This sets up the look of your list. From top to bottom I usually keep the 1st item checked, I unchecked the 2nd and 3rd, the 4th and 5th stay checked, the rest are all unchecked. Play around to format the table the way you like.
Now for each column in your SQL Statement you need to format the column in the merged table. This is done by selecting the Edit table Columns button.
If you look at my select statement I used Description (which is a Tex field), ActualAmount (a Currency field), and ActualClose (a DateTime). So when the Edit Columns dialog opens I added the formatting for each in the same order as they are displaying through my SQL Statement.
For Description – I added word “Description” in as the Caption, I set the alignment to the left, and I had none for a type.
For the ActualAmount – I added Actual Amount for the caption, right for the alignment, and currency for the type.
For the ActualClose – I added Close date for the caption, Center for Alignment, and DateTime for Type.
Now exit out of the Mail Merge Editor using the OK buttons and test your merge form.
