2

Good day everyone I have a question in regards with queries having a many filters.Below is a sample picture of my data. Let's assume this data came from the contact object. enter image description here

If I try to query the contact record to check if this 3 email exist, I can do like this.

Select Email from Contact where email IN ('[email protected]','[email protected]','[email protected]')

My question is how will I do a query to check if the email,firstName,LastName exist without using loop. Fields to be filter is dynamic. Is it possible ? What are the possible way to do this ? If I will do a query like this, I believe that this will not work as I want.

Select Email from Contact where email IN ('[email protected]','[email protected]','[email protected]') AND firstName IN('john','mike','test') AND lastName in ('doe','wew','sample')

Please help.

Improve this question

2 Answers 2

Reset to default
1

Have you considered SOSL? It sounds like it may be a better fit for this type of search.

Use the Salesforce Object Search Language (SOSL) to construct text
searches. SOSL queries can be used for text searches in the following
environments.

 - the search() call Apex statements Visualforce
 - controllers and getter methods
 - the Schema Explorer of the Eclipse Toolkit

Unlike SOQL, which can only query one object at a time, SOSL
enables you to search text, email, and phone fields for multiple
objects simultaneously.

SOSL documentation

There is a great Trailhead module covering SOSL here which should give you enough knowledge to use it to meet your requirements.

Improve this answer
6
  • Thank you ... I did not usually use SOSL. Can you give me a sample SOSL code using the data in my question ? Commented Feb 10, 2016 at 8:54
  • Complete the linked Trailhead module, it will give you a good foundation on how to use SOSL. Commented Feb 10, 2016 at 9:35
  • How does SOSL avoid the problem of when the aim is to match "John Doe" and "Jane Smith" that "John Smith" and "Jane Doe" also get matched using the and/in construct in the question? Commented Feb 10, 2016 at 9:53
  • @DavinCasey Yeah keith C question is one thing in my mind. I think there no big difference from SOQL and SOSL except that SOSL can be used in multiple objects. Commented Feb 10, 2016 at 9:56
  • You're right, I missed the AND part of your requirement. The simplest option might be a Contact trigger to populate a checkbox if the Contact matches the criteria, then search for this checkbox value when querying for Contacts. Use a fieldset / custom setting to allow the criteria to be dynamic. Commented Feb 10, 2016 at 11:21
0

One approach is to add a formula field that appends the 3 fields you want to check (with a delimiter value to separate the fields if you want):

where Forumla__c in ('[email protected]:john:doe', '[email protected]:mike:wew', ...)
Improve this answer
2
  • This would be a good idea. The problem is that I believe this only applicable if you know the exact fields to append. But in my case there the fields to check is dynamic. Commented Feb 10, 2016 at 8:26
  • @Hope Fair enough. Suggest you edit your question to make this clear. Commented Feb 10, 2016 at 9:47

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.