I was working on making my Conga Composer button more compact and easier to maintain by leveraging the &QVar0Id and &QVar0Fmt value to return a list of strings to be substituted into a Conga Query ...IN (...) ... expression but I couldn’t get it to work. With assistance from Conga Tech Support, here are the details of the problem and solution:
Conga Query 1
select id, opportunity.owner.name, account.name
from Opportunity where closedate = {pv2} and owner.userrole.name IN({pv1})
Note the use of {pv1} within the IN expression. Since owner.userrole.name is a string, you would expect the value passed into {pv1} to look like 'Sales','Marketing' – that is, a comma-delimited list of strings.
So, I set up Conga Query 2 that would always return the Sales and Marketing roles as follows:
select name from userRole where name in ({pv1})
And finally, my Composer button looked like this:
https://www.appextremes.com/apps/Conga/Composer.aspx
?SessionId={!API.Session_ID}
&ServerUrl={!API.Partner_Server_URL_210}
&Id={!$User.ID__c}
&QVar0Id=secondQueryId?pv1='Sales'|'Marketing'
&QVar0Fmt=11000
&QueryId=
firstQueryId?pv1={QVar0}~pv2=THIS_QUARTER,
firstQueryId?pv1={QVar0}~pv2=NEXT_QUARTER,
firstQueryId?pv1={QVar0}~pv2=LAST_QUARTER,
...
Note that the value returned from executing the second Conga Query that is, QVar0Id, is returned formatted per QVar0Fmt, in this case, 11000 means string-delimited, comma-separated, or, 'Sales','Marketing'
So, I expected that when Conga resolved the first query, it would take 'Sales','Marketing' and inject it into the value of the first query’s {pv1}, generating this:
select id, opportunity.owner.name, account.name
from Opportunity where closedate = THIS_QUARTER and owner.userrole.name IN('Sales','Marketing')
Valid SOQL.
But this doesn’t work.
You get this error in Conga (on the View Data workbook)
MALFORMED_QUERY:
Marketing') and closeDate = {pv2}
^
ERROR at Row:1:Column:109
Bind variables only allowed in Apex code
The Solution
You can’t use QVar0Fmt to return the result as comma-separated. It needs to be pipe-separated. In retrospect, this is not surprising as passing comma-separated values to the the pv1, pv2, … parameters of a report filter need to be pipe-separated (as described in the Conga doc). I naively thought that anything injected into a Conga (hence SOQL) query should be valid SOQL syntax but it is clear that Conga post-processes the pipes into commas before executing the SOQL.
Hence, QVar0Fmt needs to be 21000
By changing the Composer button to:
https://www.appextremes.com/apps/Conga/Composer.aspx
?SessionId={!API.Session_ID}
&ServerUrl={!API.Partner_Server_URL_210}
&Id={!$User.ID__c}
&QVar0Id=secondQueryId?pv1='Sales'|'Marketing'
&QVar0Fmt=21000
&QueryId=
firstQueryId?pv1={QVar0}~pv2=THIS_QUARTER,
firstQueryId?pv1={QVar0}~pv2=NEXT_QUARTER,
firstQueryId?pv1={QVar0}~pv2=LAST_QUARTER,
...
The results of the second query come back as 'Sales'|'Marketing', and are injected into the first query yielding this:
select id, opportunity.owner.name, account.name
from Opportunity where closedate = THIS_QUARTER and owner.userrole.name IN('Sales'|'Marketing')
but Conga clears this up before executing the SOQL and the Conga template is properly populated with data from the Opportunity
