Using the Template Builder to create your Microsoft Excel templates.

The Template Builder

The Template Builder is an essential tool in the development of Conga Composer solutions. The Template Builder exposes the data retrieved by Conga Composer from the Master Object (and from any Salesforce Reports or Conga Queries referenced in the Conga Composer URL). You can then use these fields to build templates.

To learn more on how to use the Conga Template Builder, see About Data in the Template Builder.

Configuring for Excel

The Template Builder supports multiple template types. By default, it is set to create text-based merge fields for Microsoft Word.

  1. Select the Excel radio button to format the merge fields for Microsoft Excel.
  2. Select the field and copy it to the clipboard.
  3. Paste that field into your template.

The format of an Excel merge field is:

  • &=Datasetname.Fieldname
  • &=Master.ACCOUNT_NAME

Tips and Tricks

  • The name of the dataset is case-sensitive. It must match the case as indicated on the Template Builder window. The Salesforce fieldname portion is not case-sensitive. In the image below the dataset name is Master with an upper case “M”.
  • You may only use one merge field per cell. Merge fields can be used as part of a more complex formula but require special adaptations in order to work with Conga Composer. To use a merge field in a formula, put the merge field in a non-printing region (or a different sheet) and then refer to that cell in the formula.
  • Since you can retrieve multiple rows of data for each master object (e.g. opportunity line items for each opportunity), your Excel template will automatically expand to accommodate an unlimited number of detail rows.

    Example

Add the following fields from the Master - Account dataset:

  • ACCOUNT_NAME
  • ACCOUNT_BILLINGADDRESS
  • ACCOUNT_ACCOUNTNUMBER,
  • ACCOUNT_PHONE

Then add the following field from the Master - Contact dataset:

  • CONTACT_FULLNAME

Format the fields to your liking in Excel, and save your template.

Now we’ll add line items related to an opportunity from the OppLineItems dataset.

Copy and paste the following fields as shown below:

  • Opportunity_Lineitem_ProductName
  • Opportunity_LineItem_Qty(Numeric)
  • Opportunity_Lineitem_UnitPrice(Numeric)
  • Opportunity_LineItem_Total(Numeric)

When you copy the field from the Template Canvas, the Template Builder will automatically add the dataset name (in this case OppLineItems) to the field name. Conga Composer will automatically insert rows to the spreadsheet to accommodate the number of rows in the dataset. If, for example, the first LineItem will be inserted into cell A23, then a new row will be inserted into A24, and so on.

Now save the template and run the solution.

The final output would look like this:

Rows Inserted Dynamically.

To learn more on how to use Excel templates with Conga Composer, including how to merge detailed data and use functions and formulas, refer to this Excel Templates course in the Conga Learning Center.

All Conga customers have FREE access to getting started content and release training in the Conga Learning Center. To take your training further, ensure your organization has access to the Conga Learning Pass, which is a training subscription service. Click here to learn more.