1

I want to check for records that have the same data as other records in case a mistake was made during data upload.

I want something like a self-join in SQL:

SELECT Id FROM My_Object__c a, My_Object__c WHERE a.Start_Date = b.Start_Date AND a.Student_Number = b.Student_Number AND a.Id != b.Id

Or

SELECT Id FROM My_Object__c WHERE Start_Date IN (SELECT Start_Date FROM Enrolment_c)

However, these give errors.

SELECT COUNT(Id), Student_Number__c, Study_start__c
FROM Enrolment__c
GROUP BY Study_Start__c, Student_Number__c
ORDER BY COUNT(Id) DESC
LIMIT 10

Gives Line: 1, Column: 1 System.UnexpectedException: field 'Student_Number__c' can not be grouped in a query call

Note that I cannot use GROUP BY as some of my fields such as Student_Number are not groupable.

5
  • 3
    Try this: SELECT Start_Date__c, COUNT(Id) FROM My_Object__c GROUP BY Start_Date__c HAVING COUNT(Id) > 1
    – o-lexi
    Feb 14, 2017 at 21:01
  • Can you please edit your post to include all the fields you want to group by and their configuration details? A date field should be groupable, and if it is Datetime you can still group you just need to use DAY_ONLY(MyDatetimeField__c).
    – Adrian Larson
    Feb 14, 2017 at 21:50
  • @AdrianLarson Done. Feb 14, 2017 at 21:57
  • You still haven't clarified the configuration details of the Student_Number field. Also, please always include error messages verbatim when you encounter them. Thanks!
    – Adrian Larson
    Feb 14, 2017 at 22:00
  • @AdrianLarson Student Number is a Number(18,0) field Feb 14, 2017 at 22:58

2 Answers 2

3

This isn't possible with SOQL alone +edit: after reading the possible duplicate question, I stand corrected on this statement.

Probably the most performant way (and also one of the shortest ways) to do this (in Apex) is to do something that people will generally tell you not to do, use an sObject as the key of a map.

Sets and Maps (well, the keyset of a map at least) in Apex work by computing a 'hash' of whatever you give it, and use that as an 'index' of sorts.

The reason why putting sObjects into sets and using sObjects as the key of a map is discouraged is because if you make any change to an sObject in memory, you change its hash (meaning, for example, you can no longer access the value stored in the map).

In this situation, however, this behavior is beneficial.

Some example code looks like this

List<MyObject__c> myRecs = [SELECT <collection of fields you want to test for equality in bulk> 
    FROM MyObject__c 
    WHERE <some filter>];

Map<MyObject__c, List<MyObject__c>> duplicates = new Map<MyObject__c, List<MyObject__c>>();

MyObject__c temp;

for(MyObject__c rec :myRecs){
    temp = rec.clone();

    if(!duplicates.containsKey(temp)){
        duplicates.put(temp, new List<MyObject__c>());
    }

    dupliates.get(temp).add(rec);

}

The key to making this whole thing work is that we clone the current record in the loop before we try to check to see if it matches any other record (up to this point).

You see, even if you don't explicitly query for it, you will always get the Id field in the result from a query. As Ids are unique, including this information would cause the sObject hash to be different 100% of the time, guaranteed.

By calling clone(), and setting the preserveId parameter to false (which is the default), we get an sObject record that doesn't include the Id.

Note that if your object has recordtypes, the recordtypeId is also automatically included in the result of a query. If you want to compare records across record types, you just need to null out the recordTypeId field of the temp variable (again, before testing to see if the temp variable already exists in the map).

2
  • 1
    Very clever. I'd run another loop on the map, removing all members having only 1 element in the List<MyObject__c>.
    – o-lexi
    Feb 14, 2017 at 23:06
  • @Oleksiy good point, it's not exactly a duplicate if there's only one of 'em.
    – Derek F
    Feb 14, 2017 at 23:21
1

So just a thought here in a different direction... a very common Salesforce tactic is to use "shadow fields" to enforce uniqueness rules and such. So for example you could have a workflow rule that sets Shadow__c = TEXT(Start_Date__c) & TEXT(Student_Number__c) whenever My_Object__c is created or updated and Shadow__c != TEXT(Start_Date__c) & TEXT(Student_Number__c). Then to populate historical data you'll just want to make a mass edit to every My_Object__c to trigger the rule.

Now if you rigorously want to enforce uniqueness, you can then go and set Shadow__c to be a unique text field. If there are any dupes Salesforce will tell you about it and prevent you from setting it unique. Once this setting is on, your data loads will throw errors on every row that hits the uniqueness constraint. You can then take the error report and act on it accordingly.

Alternately you can let the dupes in and then check on it with SOQL, e.g. SELECT Shadow__c, COUNT(Id) FROM My_Object__c GROUP BY Shadow__c HAVING COUNT(ID) > 1.

You must log in to answer this question.

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