0

Currently I am trying to do something like

SELECT Id FROM DatedConversionRate WHERE IsoCode NOT IN (select IsoCode FROM CurrencyType WHERE isActive = true)

which Salesforce will not allow me to do as these two tables are not related.

So what is my best alternative other than code, to pick the rows in DatedConversionRate table for inactive currencies? Though this use case will not occur if I only use GUI to insert Dated Rates, I happen to have incorrectly inserted some rows for inactive currencies in DatedConversionRate table, using REST API. So asking this question out of curiosity.

2
  • As a sidenote, I can't even delete these rows (from DatedCurrencyRate GUI they will never appear as they are for inactive currencies, and from Workbench/GUI they will never get deleted as they are the last (and only) rows for those currencies (CANNOT_DELETE_LAST_DATED_CONVERSION_RATE). Dec 29, 2017 at 13:56
  • Your options other than code, using SOQL? None if the objects are un-related. Your best bet is to use maps, but again that involves coding and you intend to avoid it.
    – Mahmood
    Dec 29, 2017 at 14:11

1 Answer 1

1

I know you said you wanted to avoid code, but this is fairly easy to do with code.

Run this snippet from the developer console, using the Debug > Open Execute Anonymous Window. Make sure the "Open Log" option is checked when you execute your code.

This code pulls the CurrencyType records into a list, then assembles another list of Iso Codes. The query you were trying to write would have been checking against the Id field of the currency type, and not the field values of the currency type- we need to separate that data into their own list to filter with them. Once we have a list of Iso Codes, we print a query using those values as a filter to the system log.

Set<String> isoCodes = new Set<String>(); // To store our ISO codes to check against the query 
    // Using Set instead of List to ensure a unique list of values 

// Query our list of currency types, put them into a working list 
List<CurrencyType> currencyTypes = [
    SELECT IsoCode FROM CurrencyType WHERE isActive = true 
];

// Loop over our currency list, and build our Iso Code List 
for (CurrencyType currencyType:currencyTypes) {
    isoCodes.add(currencyType.IsoCode); 
}

// Print a human friendly version of the query results to the system log 
System.debug(JSON.serializePretty([
    SELECT Id FROM DatedConversionRate WHERE IsoCode NOT IN :isoCodes
]));

Someone might be able to provide a better answer, either without code or by using the objects structure to simplify the query, but this is a basic implementation if no one else comes up with anything.

1
  • Thanks a lot battery.cord for your reply. As of now I was going the same way (through code and loop) but then I thought there might be something I am missing, since these two tables are functionally so closely related.. Dec 29, 2017 at 15:03

You must log in to answer this question.

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