Wrangling Data with QVars: 3 Helpful Methods

Subscribe to Our Blog

Get weekly updates
straight to your inbox

Submit your email address to subscribe.

By submitting this form you confirm that you agree to the storing and processing of your personal data by Conga as described in our Privacy Statement.

Happy Thursday everyone!  Today, we return to our regularly scheduled programming of Tips & Tricks with a topic that’s one of my absolute favorites in the realm of Conga features and functionality: the QVar.

Though it sounds a lot like a sci-fi nickname, QVar stands for Querystring Variable.  The basic concept is that you can write a SOQL query to retrieve certain data values, and then use the result of that query to provide a value for a Conga Composer parameter.

A quick word of caution: QVars are arguably one of the more technically complex features of Composer.  They require a solid understanding of SOQL and a conceptual grasp of programmatic variables.  If algebra wasn’t your thing, QVars definitely won’t be.  That being said, the QVar feature opens up a world of possibilities for dynamic data retrieval and customization of a Conga Composer solution, so my goal today is to introduce the feature in an approachable way.

To steer away from the abstract concept of a QVar and show how this feature is actually useful for merging documents with Composer, let’s jump right into three examples of how QVars are commonly used to make a solution dynamic as hell:

 

1) Using a Contact ID as an Email Recipient

The &EmailToID parameter is a great way to specify an email recipient – it just requires a Contact or Lead ID as a value.  Salesforce custom buttons allow you to reference Contacts via lookup or as a Primary Contact Role, but we see many scenarios where you need to traverse a few related records to find the right email recipient.  You could always do this manually in the email interface before sending out a merged file, but I’m definitely not here to coach manual processes.  QVars can make email recipient selection totally automatic.

For example, let’s say I want to send a proposal from an Opportunity using Composer but my desired email recipient is always going to be the related Account’s Primary Contact.  I can’t reference that contact’s record ID directly from my Composer button on the Opportunity.  To solve this problem, we can use a QVar to reach up to the Account, find its Primary Contact and retrieve their Contact ID.

Here’s a look at the object schema involved here:

 

Schema
And here’s how the SOQL query looks:
ContactRoleQVar

As you can see, the Account ID is our ‘pv0’ variable and we’re filtering for only Primary Contact Roles.  We then take the ID of this query and define the QVar in our Composer button on the Opportunity.

The parameter to define your QVar is &QVar0Id.  This makes the result of the query accessible as a variable.  Once defined, you can plunk the variable in as a parameter value by referencing {QVar0}.  Let’s break this down in the following screenshot:

QVar0Id

1) The Conga Query ID is referenced with &QVar0Id

2) The Opportunity’s related Account ID is passed to the first filter in the query

3) The {QVar0} result is used as the value for the &EmailToID parameter

When this button is used and the query executes, the actual 15-character Salesforce ID of my desired Contact replaces {QVar0} and, voila, I have an email recipient.

 

2) Using a Salesforce Attachment as a Conga Template

Conga Composer templates are typically stored and referenced by uploading the file to the Conga Template Manager.  The TemplateID parameter can reference the record IDs of these Conga Templates to predefine template selection.  That’s all well and good for basic template purposes, but many times it makes sense to include a file that’s attached to a Salesforce record as a “template”.  This isn’t usually a template with merge fields included – more often, it’s something like:

– Custom terms and conditions that need to be added to a proposal- A candidate’s résumé to be included in a job application- A contract addendum
A QVar can be used to scoop up the record ID of a Salesforce Attachment, and then feed that record ID to the &TemplateID parameter.  All that’s required is a little custom filtering to give the QVar some direction around what type of attachment it’s going to select.

For example, I want to select the file attached to an Opportunity that represents custom terms and conditions for a proposal.  Here’s how the SOQL looks:

AttachmentQVar

Similar to the previous example, I’m using two filters in my query.  The first is looking for a dynamic parent ID and the second is filtering for attachments with the word ‘Terms’ in the file name.  The second filter is (and should be) totally customized to the type of file you need to select.  You might filter for a specific file extension, a particular keyword or really any unique identifier you want to apply.  Establishing a naming convention is definitely best practice when going this route – setting up QVars to retrieve attachments is only worthwhile when they can be used dynamically across records for the same purpose.
Let’s break this down in the following screenshot:

QVar1Id

1) The Conga Query ID is referenced with &QVar1Id

2) The proposal template is hardcoded as the first template to be merged using &TemplateID

3) The {QVar1} result is used as the second value for the &TemplateID parameter, separated by a comma

 

When this button is used, it will replace {QVar1} with an Attachment record ID and Composer will pick up both templates.  They can be combined into a single file or merged separately in a .zip file.

3) Using Multiple Email Addresses as CC Recipients

The previous examples both used QVars to retrieve single data values – a Contact ID and an Attachment ID.  However, QVars are also great at returning multiple values, like a handful of email addresses that need to be included as additional recipients on an outbound email generated by Composer.

When working with multiple values in a QVar result, you’ll almost always want to format that result to separate each value with commas, surround each with quotations, etc.  The &QVar0Format parameter allows you to do exactly that, and we’ll use it in the next example.
Sticking with the Opportunity-based proposal scenario, let’s say I want to email the Account’s Primary Contact (handled by the first QVar) but also CC every individual who is a ‘Decision Maker’ in the Opportunity’s Contact Roles.  The SOQL looks like this:

EmailCCQVar

I’m working from an Opportunity with multiple Decision Makers:

ContactRoles

This query will return all three of their email addresses.  To separate these with commas, we use our QVar0Format parameter.  Let’s see how this works:

QVar2ID

1) The Conga Query ID is referenced with &QVar2Id

2) The results of the query are formatted with &QVar2Format.  10000 is the 5-digit value that will separate each data value with a comma – perfect for email addresses

3) The {QVar2} result is used as the value for &EmailCC

When this button is used, email addresses are gathered and separated by commas before replacing {QVar2}, giving us a list of email recipients to be CC’d.

Bring It All Together

Using the three QVars that I just described, the end result from Composer is downright elegant:

Results

1) The first QVar found the Account’s Primary Contact (which happens to be me) and defined it as the main email recipient
2) Another QVar found the Decision Maker Contact Roles and included them as CC recipients

 3) The last QVar found the attached terms and conditions file and combined it with a proposal template as a single PDF attachment

There you have it – three common but powerful ways to wrangle data with QVars.  Lastly, I want to extend a big thank you to everyone who stopped by to see us at Dreamforce ‘13.  It was an awesome event and we were thrilled to have been a part of it.
If you liked this post, be sure to subscribe to the Conga Blog so that you never miss a new one.  Happy Holidays!

0 Shares

Leave a Reply

Your email address will not be published. Required fields are marked *