1

I set up SOAP integration with my Salesforce instance, to maintain a copy of certain tables in our data warehouse. This works great as long as I'm using fields enumerated in their SOAP API:

SforceService  service = InitializeApi();
foreach (sObject rawRecord in service.query("SELECT X, Y, Z FROM Opportunity").records)
    {
    var  opp = (Opportunity)rawRecord;
    SaveData(opp.Id, opp.AccountId, opp.RecordTypeId, opp.Amount);  // Etc.
    }

However, I'd like to get the converted values of currency fields. I'd like to call convertCurrency() and start capturing those values. What I don't know is how to actually retrieve the values:

string  queryText = "SELECT Id, Amount, convertCurrency(Amount) AmountConverted FROM Opportunity";
foreach (sObject rawRecord in service.query(queryText).records)
    {
    var  opp = (Opportunity)rawRecord;
    SaveData(opp.AmountConverted);  // Does not exist
    }

Interestingly, if I run such a query in the Developer Console, the query executes without error, but I do not get any values for that function. With this query:

SELECT SystemModStamp, TotalPrice, convertCurrency(TotalPrice) TotalPriceConverted
FROM OpportunityLineItem WHERE OpportunityID = 'xxx'

...I get just two columns, SystemModStamp and TotalPrice. Assuming Developer Console is using the SOAP API just like I am, I fear that there may be no way to retrieve this column.

Can anyone suggest a workaround? Is this just not possible with the SOAP API?

1 Answer 1

1

You can't retrieve both the raw and calculated value at once. Aliases only work in Aggregate Result queries. When using a function, the value itself is folded back into the field (e.g. TotalPrice), which is why you can't retrieve it twice.

Examples

No Translation

SELECT Name, StageName, Amount FROM Opportunity

Results

Name, StageName, Amount
Opportunity 1, Prospecting, USD1000

Translation

SELECT Name, toLabel(StageName), convertCurrency(Amount) FROM Opportunity

Results

Name, StageName, Amount
Opportunity 1, Prospezione, EUR924.07

The reason why is because the translated values are meant for UI consumption, while the raw values are meant for API-only consumption (e.g. replication). Also, the translated value is of somewhat limited use, because it will only be converted to your user's currency, as opposed to any arbitrary currency.

3
  • "You can't retrieve both the raw and calculated value at once..." - so I can retrieve the calculated amount, as long as I don't also retrieve the local amount? That's odd, but I don't mind running two queries. Querying SELECT Id, convertCurrency(Amount) Amount FROM Opportunity, I do get values in the Amount column! Would you like to expand upon that just a bit in your answer? May 6, 2020 at 13:34
  • @JonofAllTrades Added an example, does that help?
    – sfdcfox
    May 6, 2020 at 13:49
  • That's perfect! I got the answer that I needed, and it should be crystal clear for the next person who comes along too. Thank you for sharing your experience. May 6, 2020 at 14:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .