0

this is a bit of an odd one.

Say I need to find all Accounts where the textarea field "Notes__c" contains the text 04/06/2020 twice, how would I go about that? Ideally using Apex or Reports.

Basically the Notes field looks like this:

"04/06/2020 RT - rob did something

04/06/2020 BD - bobby did this

03/06/2020 JD - john did that"

I need to find all records where the Notes contain "04/06/2020" twice, like in the example above. Could anyone help me with this?

4
  • What is the data type of the field Notes__c, Text Area? – Sergio Alcocer Jun 6 '20 at 13:39
  • It's a Long Text Area, yup. – Nic Jun 6 '20 at 14:19
  • Hey @PhilW so it looks like I could use "Account.Notes__c.contains('04/06/2020')" but what I need is where "04/06/2020" is contained TWICE, not just once. Any idea how I would do that? – Nic Jun 6 '20 at 15:09
  • 1
    You could utilize countMatches() in apex – Kris Goncalves Jun 6 '20 at 16:58
2

As Phil W mentioned, you cannot filter long text fields in SOQL. You can vote for the idea to allow this.

You didn't really specify the context for this. If it's a trigger context, then you already have the records. I'd be worried about your query size if you have millions of accounts and no real way to narrow down which ones you want to check the Notes of in a SOQL query. If you have some other fields you can utilize and are sure you could narrow it down then it should work fine.

Once you have a list of accounts, it's easier. You can check how many matches you find to your substring in a given long text area field with countMatches() as below:

enter image description here

Specific to your scenario, a simple for loop like this would work:

for(Account acc : accountList){
    if(acc.Notes__c.countMatches('04/06/2020') == 2){
        //do whatever you need to do
    }
}

I'm not sure how you're building what date to find, but you'd want to make it dynamic (based on today, etc) or a label that you can change when you want to run and check. As mentioned above, depending on the context (trigger execution, scheduled job), be aware of any SOQL limits with the returned rows.

1

Taking Kris's answer a little further, if the context is outside the Account trigger, in order to get a set of candidate Accounts that may be what you are looking for - i.e. ones that include the search term at least once, somewhere (may not be in your long text field, but it may be) - tha you can then filter down, try something like:

String searchTerm = '04/06/2020';
List<List<SObject>> results = [FIND :searchTerm IN ALL FIELDS RETURNING Account (Id, Notes__c)];
List<Account> accounts = new List<Account>();

if (!results.isEmpty()) {
    for (Account account : (List<Account>) results[0]) {
        if (account.Notes__c != null && account.Notes__c.countMatches(searchTerm) == 2) {
            accounts.add(account);
        }
    }
}

The SOSL will match any account that has your search term in any field, including long text area fieds, and thus your Note__c field. The for loop then filters out false positive matches, leaving the accounts list containing just those that have the search term exactly twice in the Notes__c field.

If your search terms are likely to occur frequently in other fields, try to add extra filtering by including a WHERE clause in the SOSL against one or more additional fields that helps select instances you want to search in (just not using an unfilterable field like Notes__c itself).

1
  • +1 - this is certainly the complete answer and actually tackles the question of "how" to get the records in the first place. – Kris Goncalves Jun 7 '20 at 1:16
-2

You can try something like below, it should work. I have assumed date is in the format DD/MM/YYYY and the string always starts with a date. You can manipulate below code in the multiple ways.

string str = '04/06/2020 RT - rob did something 04/06/2020 BD - bobby did this 03/06/2020 JD - john did that'; integer k =0,m=10; String s1; set<string> dateset = new set<string> (); while( k != -1){ s1 = str.substring(k,m); if(dateset.contains(s1)){ system.debug('duplicate date'); } else{ dateset.add(s1); } k = str.indexof('0',m+1); m = k+10; }

3
  • 2
    Try to always use existing Salesforce Apex API features - writing loops in Apex will always take more of your CPU limits than calling an API method that already does the work for you. Using Kris's suggestion of countMatches is far more efficient. – Phil W Jun 6 '20 at 19:55
  • I understand that. Thanks. My answer is specific to when substring is not known (example- "04/06/2020" in this case) and find if the given string contains any duplicate dates. Countmatches may not work, when substring is not known. – Manish Anand Jun 7 '20 at 12:33
  • All fair and good, though not relevant to this specific question. – Phil W Jun 7 '20 at 14:50

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