2

I am looking to export only case comments and notes related to particular standard and custom objects. Ex: All CaseComments for cases of a particular record type. All notes for a custom object with a particular field value.

I know there are currently multiple AppExchange and external products out there that currently do this, but I am looking to complete this task myself.

What I am thinking is use the Bulk Api/Data Loader to query and download CaseComments and Case with a WHERE clause to that identifies that cases by the criteria needed. Now I have an export of Case Ids and now I need a way to use this list of Cases Ids as the filter criteria on CaseComments to generate a list of only CaseComments related to those particular cases.

My current thought process is to utilize Node or Python to do this parsing and file generation, but I wanted to get some other ideas on how this might best be accomplished.

Code Based on Suggestions Made in Comments:

List<Case> caseList = new List<Case>();
List<CaseComment> caseCommList = new List<CaseComment>();
List<Note> noteList = new List<Note>();
caseList = [select Id from Case LIMIT 600];
caseCommList = [select id, parentId, commentbody from casecomment where parentId in (select id from Case where Id =: caseList)];
noteList = [select Id, parentId, Title, Body from Note where parentId in (select Id from Case where Id =: caseList)];

system.debug(caseCommList);
system.debug(noteList);

For the note query I was able to make the following work, but I am curious why the above does not function for notes:

List<Contact> contactList = new List<Contact>();
List<Note> noteList = new List<Note>();
List<String> contactIdStringList = new List<String>();
contactList = [select Id from Contact LIMIT 25000];

for (Contact c : contactList) {
    contactIdStringList.add(c.Id);
}

system.debug(contactIdStringList);
system.debug(contactIdStringList.size());

noteList = [select id, parentId, Title, Body from Note where parentId =: contactIdStringList];
system.debug(noteList.size());
system.debug(noteList);
  • 1
    I've written previously about doing something very similar with ContentDocumentLink in Python. – David Reed Nov 20 '18 at 16:56
  • 1
    for casecomment, why not: select id, parentId, commentbody from casecomment where parentId in (select id from Case where recordtype.developername = 'Foo_Bar') – cropredy Nov 20 '18 at 21:47
  • 2
    @S.B. The ParentId field on Note is polymorphic - it doesn't just point to Case. That apparently is enough to break that particular subquery formulation. – David Reed Nov 21 '18 at 17:27
  • 1
    @S.B. I have an alpha version of a more developed tool for doing tree exports with Python. Feel free to contact me (email in profile) if you'd be interested in trying it out; I think it'd handle your use case well. – David Reed Nov 21 '18 at 17:43
  • 1
    @S.B. every table/object in Salesforce has a specific prefix, so if you only wanted to pull down notes relate to accounts you would then filter the csv you exported with the data loader to just those rows where parentId starts with "001". This wouldn't cover the use case where you only wanted to return notes specific to accounts matching a certain criteria. Not ideal, but worth considering. – Ralph Callaway Nov 21 '18 at 23:11
0

From the support of the comments provided by David Reed and Cropredy, the following two solutions meet my requirement:

CaseComments:

List<Case> caseList = new List<Case>();
List<CaseComment> caseCommList = new List<CaseComment>();
caseList = [select Id from Case LIMIT 600];
caseCommList = [select id, parentId, commentbody from casecomment where parentId in (select id from Case where Id =: caseList)];

Note:

//Replace Contact with whatever parent object you need to query the notes for
List<Contact> contactList = new List<Contact>();
List<Note> noteList = new List<Note>();
List<String> contactIdStringList = new List<String>();
contactList = [select Id from Contact LIMIT 25000];

for (Contact c : contactList) {
    contactIdStringList.add(c.Id);
}

noteList = [select id, parentId, Title, Body from Note where parentId =: contactIdStringList];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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