Category Archives: Conga

Conga Composer Error Messages

Leave a reply

Running list of Conga error messages and their resolution

Insufficient privileges to access the Conga Composer Query Repository. Please contact your administrator.

Either:

  • The ID field on &QVarxId where x = 0-1-2 is invalid. Most likely caused by a copy-paste error

MALFORMED_QUERY: … no viable alternative …

Either:

  • Your Conga Composer URL is too long. Too many reports and/or queries. Too many long strings for pvX params. You can verify this by rearranging your reports and queries in the Composer URL. When you look at the View Data workbook, reports/queries that failed with the MALFORMED error now work and other that used to work now get the MALFORMED error

    The solution is to leverage QVarx and ReportVar or to replace soome queries and reports with Excel pivot tables rather than letting Salesforce do the aggregation and filtering

Problem: Invalid query record id. Required: 15- or 18-character Salesforce Record Id.

Either:

  • The obvious – you have the wrong Conga queryId

  • The not so obvious – you are improperly constructing the value of QVarx when used as a query pvX param.

    One cause of this is explained here – not using pipe-delimited QVarxFormat value

    Another and more subtle cause is misspelling the QVarxFormat param as QVarxFmt. Conga will use the default QVar0Format and not pipe-delimit the results.

*** No data found in this data set ***

Either:

  • Running report type Users with Territory management enabled

    If the reportId param does not include pv0=, then the first filter in the report will be smashed by Composer to be the master object Id (in my case, the QuickMerge_Link Sobject with Launch button. Setting pv0= with no params tells Composer to honor the existing first filter in the underlying report

    Example (works): [AmerTerr]00O800000060WXj?pv0=~pv2=foo

    Example (fails): [AmerTerr]00O800000060WXj?pv2=foo

Conga Composer – INVALID_FIELD: No such relation ‘Product2’ on entity ‘OpportunityLineItem’

Leave a reply

I had built a Conga Query that exploited the Product2 relationship from the OpportunityLineItem. Yet, when I used this in a Conga Composer URL, I got this error:

INVALID_FIELD: No such relation 'Product2' on entity 'OpportunityLineItem'

I know that the Product2 relationship from OpportunityLineItem was added by SFDC in V30

yet my Conga Composer URL was V33:

&serverUrl=https://mydomain.my.salesforce.com/services/Soap/u/33.0/someId

So, what gives?

Per Conga Support, the serverUrl is used by Conga solely for determining the instance name and they smash the rest of the URL with V29 (!%!).

Of course, this could change in the future so, hopefully, this post will become obsolete.

Workaround
Instead of using the OpportunityLineItem.Product2 relationship, you can fallback to the OpportunityLineItem.PricebookEntry.Product2 relationship. I don’t know what I would do were I needed a Conga Query on Order – an SObject introduced in V30

Conga Composer QVar used within QueryId pv

5 Replies

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

Conga Composer – Excel – Array Formulas – aargh

Leave a reply

We’re a steady user of Conga Composer to generate Excel reports for our users where we can’t get SFDC to format a report the way the users want. This week, an interesting problem arose.

Business problem
Display a count of distinct values in a set of rows matching some condition; some rows may have null values

Background
Without getting into too much detail, we needed to count unique accounts for various conditions in the Conga-generated, merged Excel template. I couldn’t coerce the well-known ‘Power of One’ solution to work for us in the actual SFDC report that serves as the Conga source data so the decision was made to do the counting in Excel using new cell ranges below the generated Conga output.

How do you count distinct values in Excel?
If you go to the Microsoft Excel Help, you get this page.

For example, one method is to use this excel formula: =SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))

Now, if you coded up this formula in the Conga template, you couldn’t directly use the cell value B10 because Conga will generate a variable number of rows based on your source data. So, you would code this in Excel as:
=SUM(IF(FREQUENCY(MATCH(B2:B2,B2:B2,0),MATCH(B2:B2,B2:B2,0))>0,1)) and rely on Conga+Excel to expand the outer range of each cell range expression to be B13 or B24 or B65 or whatever, depending on how many rows of generated data (where column B represents Excel value to be counted and rows 2…n are what Conga inserts into the merged template. I’m presuming you understand Conga merge templates.

Unfortunately, this does not work as the formula =SUM(IF(FREQUENCY(MATCH(B2:B2,B2:B2,0),MATCH(B2:B2,B2:B2,0))>0,1)) has to be entered into Excel as an array formula and Conga won’t expand/Excel array formulas by adjusting the outer range and B2:B2 expression

So, now the problem is how to count distinct values without using array formulas.

The solution that worked for us (with limitations)
Some Google searching yielded this count distinct formula that does not rely on Excel array formulas:
=SUMPRODUCT(1/COUNTIF(List1, List1))

Geez, how does this work? I’m used to the second argument of COUNTIF being some expression that is tested against the elements of the first argument but here we are comparing the list to itself. The MSFT documentation of COUNTIF doesn’t have such an example.

Well, I turned to the handy Excel formula expression evaluator in my Windows Excel 2010. Take a look at the picture below (click to see full size so you can read it):
Count distinct formula evaluation

As you can see from the picture and successive evaluations, the COUNTIF(list1,list1) looks at each element in the list and sees how many times it appears in itself, returning an array of the same size as List1. Then the reciprocal is taken of each resulting array element.

So, if a value X appears twice in the range, the resulting array will be {2,..,2,…} and thus the reciprocal array is {.5,..,.5,…}. If also a value Y appears in the range three times, the resulting array will be {2,3,3,…2,…}. Positions are of course arbitrary as there is no assumption made about sorting. And the reciprocal array would be {.5,.333,.333,…5,…}

Finally, SUMPRODUCT, because it has only one array, doesn’t multiply anything but does add up the values in the reciprocal array. Adding .5 twice is 1; Adding .33 three times is 1. Summing 1 and 1 is 2 – two unique elements

As you can see from the picture, the result ‘4’ is the number of unique Account Names. pretty cool and no array formula required.

But what happens if you have nulls in the range of Account Names?
The expression will return #DIV/0. Sigh.

The solution is to recognize that if the comparison array in COUNTIF is guaranteed to have a non-null value, then the counts will work. Look at this picture:

Count distinct with null formula evaluation

Note the concatenation operator ‘&’. This appends a zero length string to the second list in the COUNTIF. Thus your final result will include a count of distinct Account names plus 1 for the null cells. If you are guaranteed there will be null cells (and we were due to Conga subtotal lines inserted), you just subtract one from the final result of the =SUMPRODUCT(…) formula.