0

I am doubting this is possible in SOQL given the requirements and limitations of the language, but wanted to sanity check to make sure I am not overlooking any possible solutions before I throw in the towel.

We are currently building an azure application for which we need to migrate a large subset of our Salesforce users into. I have been tasked with building a query that will return all of the user records that have a certain field filled in, but more importantly need to eliminate all of the users who share an email with any other user in the SF org. I cannot use apex to go through the user records and eliminate the duplicate values.

In SQL, I believe this could be accomplished with using a where clause like this-

WHERE NOT EXISTS (SELECT 1 FROM User U2 WHERE U1.Email = U2.Email AND U1.Id <> U2.ID)

But so far, the closest I have been able to come to accomplishing this in SOQL is the following query-

SELECT Email, FirstName, LastName, Id, IsActive, Username FROM User WHERE Custom_Field_A__c != null AND isActive = true GROUP BY FirstName, LastName, Username, Id, IsActive, Email HAVING COUNT(Id) = 1 Limit 200

However, there are several problems with this query as is. The first is the only way it works is to limit to 200 records, when the number of users that needs to be returned is a much larger number, and there does not seem to be a way to run multiple batches of the query when aggregating fields. It also needs to return the created and modified dates, which seem to not be able to be included in a query that is aggregating fields. Lastly, the way this is group makes it so that all of the values collectively have to be not shared by any other records, but I really just want to filter out users with a duplicate email, not really caring if they have a duplicate first or last name.

Would it be possible to reform this query in some fashion that it will return all of the users in which the Custom_Field_A__c is not null, and the email is not shared between any other users, and all of the fields mentioned can still be returned, without using apex to assist in some fashion?

Improve this question

1 Answer 1

Reset to default
2

You can do it with just SOQL, though you may still need to add further filtering to reduce record count, since it will use aggregate queries, which do not support queryMore. But the HAVING clause may give you exactly what you need. The basic idea would be:

SELECT Email
FROM User
WHERE My_Field__c != null
GROUP BY Email
HAVING count(Id) = 1

If you group by more fields, the above will only deduplicate for the comprehensive set of filters. So if you wanted to deduplicate for only Email, then you might need two separate queries.

Improve this answer
3
  • This is very similar to what I was using before the query in the question, but unfortunately I do need to return other fields than just the one that I am deduplicating, so it breaks down when I try to add name and all the other fields that I need to see but not deduplicate. This lead me to adding them to the group by section as well, but as you say say that runs into the problem of deduplicating them all which is also not what we want to do (though if we could figure out a way to solve the rest of the problems, we can probably make it work).
    – Frank Evers
    Dec 3, 2020 at 16:56
  • You'll need two queries. One to dedupe and dump emails for a filter value, then a second to pull field values.
    – Adrian Larson ♦
    Dec 3, 2020 at 17:00
  • I was thinking that would be the case, just had to make sure I was not overlooking anything. Thank you for the confirmation. Working with SQL really makes me wish SOQL supported some of this stuff better!
    – Frank Evers
    Dec 3, 2020 at 17:04

You must log in to answer this question.

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