0

Problem Statement: We created a custom metadata which has a field called programs. Since we cannot create multi-select field with custom metadata we have created a string field and stored all values semi-colon separated like below enter image description here

User would select programs in UI like Healthcare Coverage, Food Assistance Program and we need to only show questions related to the selected programs from the metadata we created

Includes and Excludes will only work with multi-select field and i looked at this solution from adrian SOQL: Bulk Fuzzy Searching and it doesnt help for my case as its doing a exact search. How should the SOQL be so that if user selects 'Food Assistance Program','Cash Assistance' it should go query the metadata record as shown in the screenshot.

enter image description here

1

You could actually do this in Apex using a set/list. Here's a barebones example:

Set<String> test = new Set<String>{'%search%', '%term%'};

SObject[] records = [SELECT Id FROM OBJECT_NAME__c WHERE NAME LIKE :test];

This will work in apex like my example above. This will NOT work in the developer console. For example

SELECT Id, Name FROM OBJECT_NAME__c WHERE Name LIKE ('%search%', '%term%') // Unknown error parsing query

Essentially the same as the other answer

  • It works if i have only one search keyword in the set. the moment i add 2 strings the results sobject list is blank – RedDevil May 16 '17 at 16:53
0

I think you would need to use OR expressions in your where clause.

Select [...] from [...] where [insert built expression here]

and the built expression would be something like this:

Programs__c LIKE '%partial1%' OR Programs__c LIKE '%partial2%' [etc.]

I do not know how good the performance of this would be.

  • select MasterLabel,Field_API_Name__c,Programs__c,Section__c,Sub_Section__c from Holistic_Application_Questions__mdt where Programs__c like '%State Emergency Relief%' OR Programs__c like '%Healthcare Coverage%'. tried this and it doesn't return any record – RedDevil May 16 '17 at 15:55
  • You can't generally use OR in searches with custom metadata types. – Avrom Roy-Faderman May 16 '17 at 23:46
  • Is there a way to get around it? – RedDevil May 17 '17 at 5:38

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.