Long text field tricky query problem seeks creative answer

  • 1
  • Question
  • Updated 3 years ago
  • Answered
We have a setup where we are determining what default records to associate with an opportunity based on values on the opportunity or related records. For example, for figuring out the set of documents needed for a particular type of loan, we find out if the borrower is an individual or an entity, if there is property associated with the loan or not, if the loan purpose is construction or purchase or refinance, etc.

We have an object Document Reference Template that has text fields that we override as multi-select picklist fields on our setup page, so that we can use a query for if a field on the Template CONTAINS the value of the related field on the opportunity record. We query for all the Templates that match the opportunity fields, then run a snippet to create a Document Reference record for each Template in the query and relate each of the new records to the Opportunity. It's working pretty great and really hums with Skuid.

However, one requirement that we can't figure out just yet is for Collateral Type. The requirement is similar, look through all the Collateral records related to the opportunity, and query for any Templates where the Collateral Type on the Template CONTAINS the Type of any of the Collateral records. Type on Collateral is a picklist, but we haven't figured out what to make the field on the Document Reference Template.

If you could do a contains query on a multi-select picklist we could do that (though we couldn't move it to production until they allow more than 40 characters per entry on a custom multi-select). Maybe I'm misunderstanding the way Includes operator works on a query, and essentially you can do Contains?

There are too many values to fit in a text area (255) field and override it like we have done for other fields. You can't query on Text Area (Long) fields.

We're thinking of splitting it into a few Text Area (255) fields, each one overriden as a multi-select picklist on our skuidified loan package setup page, but that's not ideal. 

You can search Long Text fields from a table component if you use SOSL on all text fields, is there someway to do that in Javascript? 

Maybe we could query all the records, then do something client-side to look through the records we've queried to find the ones we want or discard the ones that don't match.

We might just end up going back to square one and changing the way we set up the Templates, maybe there's a different way to achieve this entirely.

Any ideas or feedback appreciated. Here's a screenshot of our Loan Package Settings page where we define new Document Reference (DREF) Templates. Relevant Borrower Types and Purpose are both actually Text fields so that we can use them for CONTAINS queries, and on the page builder we override them as multi-select picklists

Photo of Jack Sanford

Jack Sanford, Champion

  • 8,570 Points 5k badge 2x thumb

Posted 3 years ago

  • 1
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,888 Points 10k badge 2x thumb

It looks like you need to consider a structural change to your data model.  I think your Borrower Types, Purpose, and Collateral Type need to be a separate object with a junction object between them and your DREF Templates.  Then you can use SOQL to do your querying.

The only other option I can see is one that you mentioned.  Use client-side javascript to run through the model and remove records that you don't want.