2

Based on salesforce Documentation currency based where clauses are of the form: WHERE Object_name Operator ISO_CODEvalue

Sample query would look like:

SELECT Id, Name FROM Opportunity WHERE Amount > USD5000

See the USD before 5000 to specify the currency. But the documentation doesn't describe how to deal with negative values.

I have tried followings and all of them fail with parsing error from salesforce.

SELECT Id, Name FROM Opportunity WHERE Amount > USD-5000

and

SELECT Id, Name FROM Opportunity WHERE Amount > -USD5000

which throws:

{
    "message": "\nName FROM Opportunity WHERE Amount > -USD5000\n                                      ^\nERROR at Row:1:Column:49\nunexpected token: 'USD5000'",
    "errorCode": "MALFORMED_QUERY"
}

What is the proper way of using ISO_CODEvalue for currency fields when the query requires negative values?

Based on comment below, I have also tried following query:

 SELECT Id, Name FROM Opportunity WHERE Amount >-1*USD5000

Salesforce throws following exception:

{
    "message": "\nFROM Opportunity WHERE Amount >-1*USD5000\n                                ^\nERROR at Row:1:Column:49\nunexpected token: '*'",
    "errorCode": "MALFORMED_QUERY"
}

Note: I have previously asked this question in stackoverflow.

  • Thanks @AdrianLarson. Updated the question with appropriate links. Yes, I did try -1*USD5000, it fails with same query parsing error. – Mahesh Aug 30 '16 at 17:24
  • Updated the question to include -1* approach and salesforce responses. – Mahesh Aug 30 '16 at 17:40
  • 1
    I don't have an org with multi-currency at the moment. Have you tried using () around the value? For example: -(USD5000) or USD(-5000). – Stephen Aug 30 '16 at 17:41
  • @Stephen both result in same "MALFORMED_QUERY" response from salesforce. – Mahesh Aug 30 '16 at 17:51
  • using the () results in an error. You can query < USD0.00 but you do not get specific records – Eric Aug 30 '16 at 18:07
1

not sure if this helps or not but this appears to work although it does not seem to take into account conversions, at least I do not think so:

> -10.00 and CurrencyISOCode = 'CAD'

You can also query

< USD0.00

but obviously the records returned are not specific to an amount you may be looking for

All other attempts I tried failed with query errors.

A slight workaround maybe:

  1. Create two fields
    • Currency
    • Checkbox
  2. WFR to populate the currency field with the ABS value of the main currency field
  3. WFR to set the checkbox to true if the amount in the new currency field should be negative

then query the new currency field [>, <, =] CAD10.00 and [CheckBox] = true`

Maybe as a workaround?

  • If you had to go down this route, you could do the conversion yourself, couldn't you? – Adrian Larson Aug 30 '16 at 18:00
  • @AdrianLarson - I would assume so, but I am no expert on the multi-currency so do not have the knowledge to offer a solid answer on that. – Eric Aug 30 '16 at 18:02
  • To implement expected behavior of field > -USD5000 using this technique will involve finding out all the supported currencies, use the exchange rate to convert and construct OR clauses of the from (amount > -10.00 and CurrencyISOCode = 'CAD') OR (> -7.60 and CurrencyISOCode = 'USD') OR.... I believe this would create massive where clause, combining with other constraints probably will exceed the query size limit for REST APIS. – Mahesh Aug 30 '16 at 18:40
  • 1
    @Mahesh - You could get creative, create a currency field that will always be positive, use a WFR to populate it. Create a checkbox that indicates negative, then use that field to query – Eric Aug 30 '16 at 19:57
  • 2
    this calls for use of the Doc feedback feature and request an example be added on how to do this – cropredy Aug 31 '16 at 1:12
0

After opening a support ticket and talking with salesforce support, the conclusion is that they don't support this functionality.

Create a currency field, in a workflow field update set it to -amount. Use this in the query.

To get the negative values, i.e Amount > -USD500

Could be written like this...

NegativeAmount__c < USD500 OR Amount >= USD0

It's similar to workaround we knew also suggested by @Eric. Although it works fine, it's not a scalable solution if you are running query against arbitrary field/object. Below is the response we got when we stated the solution is not feasible for us.

I understand that the solution provided is not feasible for you. Currently, Salesforce does not have an option of adding "-ve" signs in the query for Currency values. I highly recommend you to post this request on our Idea Exchange board to get this functionality.

0

One way to query negative currency field is to create a formula field of type currency to populate positive value of the currency field. It works for all currencies.

So in this case you can create a formula field Amount_formula and use soql

SELECT Id, Name FROM Opportunity WHERE Amount_formula > USD5000

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.