-1

I need to find the name of the common records which have similar fields in them. For Example - I will create a vf page to dynamically take 3 fields for an object.

And I need an Soql Query to search for duplicate records in all the 3 fields together and must retrn the names of records which have the common (Same) values for all those fields. Note - I need to get only the name of the records. Please help. My query is - " This is a Dynamic query "

 string SoqlStr=' SELECT ' + Field1 + ' , ' + Field2 + ' , ' + Field3 + ' , ' + ' COUNT(ID) ' +  
          ' FROM Account GROUP BY ' + Field1 + ' , ' + Field2 + ' , ' + Field3 + 
          ' HAVING COUNT(Field1) >= 1 AND COUNT(Field2) >= 1 AND COUNT(Field3) >= 1 ';

This is my basic built query . Please help me to transform it. The object is a standard object.

closed as off-topic by Pranay Jaiswal, glls, battery.cord, Raul, Mohith Shrivastava Nov 28 '18 at 3:07

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions on problems in code you've written must describe the specific problem and include valid code to reproduce it. For help writing short, self-contained syntactically-valid examples, see: SSCCE.org" – glls, battery.cord, Raul
If this question can be reworded to fit the rules in the help center, please edit the question.

1

Your query isn't valid because it includes Field1, Field2, and Field3 literally, instead of doing string concatenation. That's one reason I encourage using String.format() to template dynamic queries like this one:

String query = String.format(
    'SELECT {0}, {1}, {2}, count(Id) FROM Account GROUP BY {0}, {1}, {2} HAVING count({0}) >= 1 AND count({1}) >= 1 AND count({2}) >= 1',
    new List<String> { 'Name', 'Industry', 'AccountSite' } // or whatever fields you pick
);

Your HAVING() clauses are, I think, misplaced here. What you really want is duplicate groups, so you need HAVING count(Id) > 1. Your current HAVING() clauses are tautological because any group will have a count of values in the grouped fields above or equal to one.

One of the limitations of aggregate queries in Salesforce is that you cannot extract the field values for individual grouped rows. This query will return AggregateResult objects that include the Id count and the values of the three fields that define the group. You would then need to perform an additional query for each group of duplicate records to identify the names of the records in that group, unless Name is one of the grouped fields. Then and only then can you include Name in your SELECT clause.

That said, I think this is a risky route to handle duplicate records. In a large database, this query will require careful tuning to avoid timing out, and it may well time out anyway. I'd encourage using Salesforce's native duplicate management rules.

Not the answer you're looking for? Browse other questions tagged or ask your own question.