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

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.
• To my knowledge, there are governance limits on Apex code which would prevent processing this information in memory.
• 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 the ideas or sample coding that is outlined the algorithm or solution. Any help providing a solution or algorithm for this use case is greatly appreciated.

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

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.