Salesforce SOQL querying negative currency fields in multi currency orgs

Question

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.

Answer 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?

Answer 2

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.

Answer 3

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