1

I am new to Salesforce and wanted to get some guidance on building a solution for the following use case. I am currently working on a Salesforce project building a hotel reservation system for service reps. The hotel/property information is pushed from an external system into Salesforce. We receive the Property Feautre, Room Feature and Nearby Activties in a comma separated format and is stored in a custom object called RoomInfo__c. The data type for these fields is Long Text (32768).

There are close to 180,000 records in the RoomInfo__c object and the schema with representative data is as follows RoomInfo__c

Search Screen Filters

  • Location : Dropdown
  • Exact Match : Checkbox
  • Property Feature : Checkbox Group
  • Room Feature : Checkbox Group
  • Near By Activities : Checkbox Group

The distinct values for the PF, RF and NBA Checkbox Group are stored in custom object with Type__c (PF, RF, NBA) and Value__c as two fields. The service rep executes search with following parameters.

Scenario 1 :

  • Exact Match : true
  • PF : Pool and Spa
  • RF : King Bed, TV
  • NBA : Theme Park, Kayaking

Expected Result : 0 Records. There is no record that matches all 2 criterias.

Scenario 2 :

  • Exact Match : true
  • PF : Pool and Spa
  • RF : Safe, TV
  • NBA : Fishing, Kayaking

Expected Result : 2 Records. Property1-Standard and Property1-Deluxe

Scenario 3 :

  • Exact Match : false
  • PF : Pool and Spa
  • RF : Safe, TV
  • NBA : Fishing, Kayaking, Theme Park

Expected Result : 4 Records. Property1-Standard, Property1-Deluxe, Property2-Standard and Property2-Deluxe

Problem Statement : The permutation and combinations of PF,RF,NBA can run into millions. The constraints are,

  • The RoomInfo__c schema cannot change.
  • There are governance limits on Apex code preventing in memory processing.
  • SOQL does not support LIKE operator while querying on Long Text field.

My Request : Guide me with an algorithm or solution. I am happy to work on your ideas or code the algorithm or solution provided by you. Any help providing a solution or algorithm for this use case is greatly appreciated.

2
  • Clarification: is each RoomInfo record a single CSV line for one room, or does each RoomInfo record contain CSV data for multiple rooms?
    – David Reed
    Apr 16, 2021 at 18:33
  • Great question David. It is the former. Each RoomInfo records represents a single CSV line for one room. Apr 16, 2021 at 18:50

1 Answer 1

1

First of all, I have to call this out: this is a very poor data model. By storing data in CSV in a text field, you are sacrificing much of the innate capability of the Salesforce database or indeed any database to effectively relate, index, query, and search structured data. That's going to make all further implementation you do more expensive, less performant, and higher maintenance.

I'm also going to ignore for the purpose of discussion the constraint

The RoomInfo__c schema cannot change.

because in my opinion most of the viable solutions involve changing the schema.

As far as I can see, the single best decision you could make right now is...

Use a middleware platform and parse the data

Put some kind of middleware platform between your external source of truth and Salesforce. Parse that grimy CSV data and turn it into actual related records: each Property is a record, each Room is child record. (See below for discussion about modeling Nearby Activities and Features).


If you can't use a middleware platform, proposal #2 is...

Postprocess the data in Apex

CSV is not hard to parse, although fair warning, you will find a lot of quite bad CSV parsing code in Apex if you search. (Read all of RFC 4180 and be aware of specific quirks, if any, in how your source system interprets the CSV format).

You'd probably want a batch process or perhaps a Queueable that crawls inbound data in CSV format after it is split into RoomInfo records and parses it out into actual database fields.

Note that this will create a time gap where records enter your database in CSV format and then are picked up by the asynchronous job and converted to real data. You might be able to do it in a trigger depending on what your data volume looks like and how performant you can make your Apex. The details of the best solution will be dependent on how frequently the data is loaded or updated and at what volume.

Modeling Nearby Activities and Features

There are several different ways the Activities and Features elements could be modeled in real database objects and fields.

  1. Relationships. Each Activity or Feature could be a record, and you could define junction objects connecting Room Info records to them. At the data volume you specify, this is likely to result in data skew, so this solution is probably contraindicated.

  2. Multi-select picklists. Nobody really likes these; they're annoying to work with and report on. The advantage over #3, checkboxes, is that adding new Features or Activities would involve adding picklist entries but not adding a new field to the schema.

  3. Checkboxes - one checkbox on the Room Info record per Activity or Feature that is supported. This can of course lead to a profusion of checkboxes, but it is easy to query and report on.

  4. Structured data in text fields. JSON is much more common here than CSV. However, this solution is best for situations where you need to display complex data for one record in a UI, not where you need to perform complex searches or queries against it.

I would suggest that, assuming the set of Activity and Feature values is relatively small (and it sounds like it is, since you're presenting them in the UI via Checkbox Groups and are defining what is available via Custom Settings), modeling these elements as checkboxes is probably the right solution.

I'm not touching on the complexities of updating existing records at all - exercise for those who know more about the totality of this integrated architecture.


If you can't do either of those things, the only other option I see is...

Use SOSL

Unlike SOQL, SOSL can search long text fields (although it still can't filter them in a WHERE clause). You can dynamically construct a SOSL search for the features that you want to find in the long text field.

There's a lot of caveats and potential gotchas here.

  • You need to understand how SOSL constructs a result set. Your searches are not guaranteed to return all potentially responsive records.
  • If a property name overlaps with one of the features, your search for that feature will pick up all the rooms at that property (!)
  • If feature names are shared across fields, you can't distinguish between matches in different locations.
  • Your search query must be less than 4,000 characters long or you'll lose the ability to use filter logic.
  • You'll never be able to return more than 2,000 results.
  • The performance may or may not meet your expectations - worth experimenting. I don't have a lot of experience benchmarking SOQL vs. SOSL.

The Exact Match = false Case

All of this preceding is around the Exact Match = true case. The Exact Match = false case is likely to require you to write additional code depending on what data model solution you adopt, potentially running multiple queries with partial sets of your parameters and writing Apex or more likely JavaScript code to merge those result sets.

3
  • David, Thank you for the detailed explanation. This is exactly what I was looking for and clearly I have few things to ponder over. My thoughts for next step are : 1. Check feasibility of change to data model. 2. Assume we do change RoomInfo__c data model, options are a. Junction object (Room Info Id and Feature Id). b. Consider using Multiselect Picklist for Property Feature, Room Feature and Near By Activities 3. Assume we cannot change RoomInfo__c data model, options are a. Explore SOSL as an option. b. Leverage asynchronous features to convert csv to structured format. Apr 16, 2021 at 23:31
  • My opinion is that checkboxes are probably the right solution for modeling features, but you'll have to weigh all the pros and cons for how your system works.
    – David Reed
    Apr 16, 2021 at 23:54
  • Thanks David, depending upon the internal discussion I'll update the comments to share where we landed. Apr 20, 2021 at 19:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .