3

As part of our continuing GDPR improvement, I've been tasked to easily find all records which reference an email address, telephone number or name, entered by a user as a text string. Then we want to programmatically replace the personal data with random text.

Our org is elderly and rather bloated, and there are potentially references to a customer's email address in dozens of objects, going back to the beginning of the org.

I've not really had a use for SOSL before, but I think it might be the solution. However, I'm wondering if there's a way to perform a SOSL query and be able to return WHICH fields it found the text string in? This will future-proof the solution, in case some future dev or admin comes along and decides to create a new object which hard codes personal info rather than referencing a contact record.

So my question: is there a way, either in Apex or in the SOSL search itself, to return the fields that the search found the text in?

I know how to return specific fields in the search, but if I don't know in advance which field the search term might come up in, returning specified fields is no good to me.

Thanks in advance!

2

SOSL contains no mechanism to identify the actual field in which the search pattern was found, much less to dynamically construct the result set fields based on where the term was found. It makes me wonder if the underlying search infrastructure even does this in a way that could be surfaced to the returned result set.

Given the importance of solving the GDPR problem, and in the absence of the above, I think your best solution is going to be use IN to return all fields that could contain it. Then once you have that result set, batch it up and use a secondary batch Apex job to chase down the search pattern and report back which field.

So which fields should you then return? Well...any text-based fields. I know...right? Not great news. As to what are "all the text based fields", well, a good start is this help topic on searchable Objects and Fields.

One SOSL feature that may make this ever so slightly better is the "with hightlights" feature. This will return the results with the search term highlighted in the results. This way you could potentially use a constant regex and look for the <mark>.*</mark> markup that it decorates search results with. I've not actually used it...it just seems like a potentially helpful feature in your use case.

So not great news, I reckon. But on the other hand, this could be a useful app in the era of GDPR...maybe you could submit it as an app exchange partner app and start a new career as an independent software vendor.

  • 1
    Thanks @pchittum - yeah, it does seem like there's a gap in the market - the best I've seen so far involves mirroring your entire org in a local DB like Oracle or MySQL. Fortunately at this point, our Subject Access Request volumes are so low that someone can manually input the SOSL search strings into the Dev Console (where you don't need to specify the object, unlike if you run it in Apex, grr!) and find all the data that way. Not ideal, but a workable band-aid for now. I'll get on that AppExchange idea. Wanna join me? ;-) – Charlotte G Sep 20 '18 at 11:28
  • 1
    Hah! Well...seeing as I work for Salesforce, it'd have to be a Salesforce Labs app if I made something. But it would be an interesting problem to crack. – pchittum Sep 20 '18 at 19:56
  • If you developed something like that, I bet SF would snap it up and sell it at a premium, rather than giving it away on Labs. And a nice big promotion for you! Just remember us little people when you're Benioff's right-hand-man. ;-) – Charlotte G Sep 20 '18 at 22:06
0

According to the Salesforce Documentation you can filter your queries using the IN SearchGroup optional clause. For example, you can search for name, email, phone, sidebar, or all fields.

  • No search group | FIND {MyProspect}
  • ALL FIELDS | FIND {MyProspect} IN ALL FIELDS EMAIL FIELDS
  • EMAIL FIELDS | FIND {mylogin@mycompany.com} IN EMAIL FIELDS
  • NAME FIELDS | FIND {MyProspect} IN NAME FIELDS
  • PHONE FIELDS | FIND {MyProspect} IN PHONE FIELDS
  • Invalid search | FIND {MyProspect} IN Accounts

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.