2

I'm trying to write a SOQL query for a Conga Composer button but I encounter errors. Below is the query - what's wrong with it? I was getting an error on the Product Code but seem to have that sorted, now the FISCAL_MONTHS bit is throwing this error:

"value of filter criterion for expression FISCAL_MONTH(ScheduleDate) must be of type integer and should not be enclosed in quotes"

But it is an integer and isn't in quotes, isn't it?!

SELECT 
SUM(Revenue) January 
FROM 
OpportunityLineItemSchedule 

WHERE

OpportunityLineItem.Account_Record_Type_Id__c = '0120000000097XD' 
AND 
OpportunityLineItem.Sales_Person__c = '{pv0}' 
AND 
OpportunityLineItem.PriceBookEntry.Product2.ProductCode = 'o' 
AND 
OpportunityLineItem.PriceBookEntry.Product2.ProductCode = 'dsc-ot' 
AND 
OpportunityLineItem.PriceBookEntry.Product2.ProductCode = 'd-o' 
AND 
OpportunityLineItem.PriceBookEntry.Product2.ProductCode = 'aw' 
AND 
OpportunityLineItem.PriceBookEntry.Product2.ProductCode = 'dsc-aw' 
AND 
OpportunityLineItem.PriceBookEntry.Product2.ProductCode = 'd-aw' 
AND 
OpportunityLineItem.PriceBookEntry.Product2.ProductCode = 'AD' 
AND 
OpportunityLineItem.PriceBookEntry.Product2.ProductCode = 'dsc-ad' 
AND 
FISCAL_MONTH(ScheduleDate) = 1 

AND

FISCAL_YEAR(ScheduleDate) = THIS_FISCAL_YEAR
1
  • James, I know this is not the question, but you know that "where clause" won't work properly since you're "AND-ing" many mutually exclusive clauses, right? Jun 1 '16 at 19:12
0

Give the following query a try:

SELECT SUM(Revenue) January 
FROM OpportunityLineItemSchedule 
WHERE OpportunityLineItem.Sales_Person__c = '{pv0}' 
AND OpportunityLineItem.Account_Record_Type_Id__c = '0120000000097XD' 
AND OpportunityLineItem.PriceBookEntry.Product2.ProductCode IN ( 'o', 'dsc-ot', 'd-o', 'aw', 'dsc-aw', 'd-aw', 'AD', 'dsc-ad' ) 
AND ScheduleDate = THIS_FISCAL_YEAR 
AND FISCAL_MONTH(ScheduleDate) = 1

In my testing in the Developer Console, it seems that you had an illegal character somewhere in the final filter that wasn't visible on the surface. I have also cleaned up your ProductCode filter so you don't have the on going evaluation.

If you want the results simply grouped by the month, you could also use the following which would give you all months, summed by month

SELECT FISCAL_MONTH(SCHEDULEDATE), SUM(Revenue) Amt 
FROM OpportunityLineItemSchedule 
WHERE OpportunityLineItem.Sales_Person__c = '{pv0}' 
AND OpportunityLineItem.Account_Record_Type_Id__c = '0120000000097XD' 
AND OpportunityLineItem.PriceBookEntry.Product2.ProductCode IN ( 'o', 'dsc-ot', 'd-o', 'aw', 'dsc-aw', 'd-aw', 'AD', 'dsc-ad' )
AND ScheduleDate = THIS_FISCAL_YEAR 
GROUP BY FISCAL_MONTH(SCHEDULEDATE)
ORDER BY FISCAL_MONTH(SCHEDULEDATE)
7
  • Thank you CongaPete! And thank you also Sebastian and sfdcfox. Jun 2 '16 at 13:02
  • There don't now appear to be errors in my Conga values workbook, but now I want to be sure that '{pv0}' is referencing the right thing. If the query is included in a Conga button (like the below), does '{pv0}' reference the &ID= in the button code? Jun 2 '16 at 13:04
  • appextremes.com/apps/Conga/Composer.aspx ?sessionId={!API.Session_ID} &serverUrl={!API.Partner_Server_URL_80} &ID={!ShipServ_People__c.Id} &TemplateId=a0A58000001q39i &CurrencyCulture=en-US &Reportid=[SSPerson]00O58000002pcJx &Queryid=[Jan]a0B58000001NzdE, Jun 2 '16 at 13:05
  • ...also please let me know if I've rated the answers correctly - both very useful and CongPete's the most useful. Jun 2 '16 at 13:07
  • The error could be related to the spaces in the button URL. Make sure you don't have spaces on the end of each row in the button. I would encourage you to raise a case with Conga support: support@congamerge.com and they can likely dive in to troubleshoot via GoToMeeting. Without testing in Composer, you could grab a sample Sales_Person__c Id value and test the query in Dev Console or Workbench to surface query syntax errors.
    – CongaPete
    Jun 2 '16 at 15:50
1

Date Literals do not want (or need) a formula surrounding the field. The filter should simply be:

ScheduleDate = THIS_FISCAL_YEAR

By using FISCAL_YEAR, you're changing the data type to a number, so you'd then have to do:

FISCAL_YEAR(ScheduleDate) = {pv1}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.