SOSL: How to programmatically work out what field a search string is found in

Question

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!

Answer 1

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.

Answer 2

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 {[email protected]} IN EMAIL FIELDS
• NAME FIELDS | FIND {MyProspect} IN NAME FIELDS
• PHONE FIELDS | FIND {MyProspect} IN PHONE FIELDS
• Invalid search | FIND {MyProspect} IN Accounts