What is Search and Create?
The Search and Create tool is the primary search tool used when searching your database for an existing customer, or entering new prospects into your improveit 360 system.
What data fields can we search by, when using Search and Create?
- Prospect: Last Name
- Prospect: First Name
- Prospect: Street Address
- Prospect: Postal Code
- Prospect: Phone #
- Prospect: Email Address
Note: The Search and Create tool searches only Prospect Fields. It does not search Fields on other objects like Appointments, Quotes, Sales, or Projects. If you would like to search for a specific record using another field, like the Sale ID, to apply a payment, or the Project ID, you can use the Advanced Search bar at the top of the page to search Fields on other Objects:
How does Search and Create Match Prospect data?
Based on the data points entered by the User, there are different Search Paths the system might take. It is important to understand how the data entered by the User in Search and Create dictates which Search Path is used when querying Prospect Matches. It is also critical to understand how the number of search results may trigger a second, additional Search Path to be used.
Note below each of the Search Paths as they apply to the data entered by the User and the number of Search Results received by the query:
Search Path 1
Executes if the user provides Name, Email, or Phone
First Search
o The query is built to use AND logic between all types of searches (e.g. Phone AND Email AND Address)
o Email must exactly match Primary Email or Secondary Email
o Phone must exactly match Phone 1, Phone 2, or Phone 3
o Correspondence Name has to contain the Name search term (e.g. "John Doe")
o Address is not used in the first search if Email, Phone, or Name are specified
o Postal Code is not used in the first search if Email, Phone, or Name are specified
Second Search
o Executed only if the first search returns 5 or fewer results
o The query string is identical to the first search except all instances of AND logic are replaced with OR logic (Phone OR Email OR Address)
Third search
o Executes only if the first and second searches yield 0 results.
o The query uses all fields entered, including Name, Phone, Email, Address, and Postal Code
o The individual WHERE statements are combined with an OR
Search Path 2
Executes if the user doesn't provide Name, Email, or Phone, but does provide an Address or Postal Code
First Search
o Searches using House Number, Address Parts, and Postal Code.
o Criteria is joined with AND
Second Search
o Executed only if the first search returns 0 results.
o Runs the same query as the first search except that all instances of AND are replaced with OR.
Additional Logic
If Path 1 or Path 2 executes and no results are found, the process moves onto the logic below.
o If Address and Name were provided, but Phone, Email, and Postal code were not provided, we search by House Number and Name.
o If Postal Code and Name were provided, but Address, Phone, and Email were not provided, we search by Postal Code and Name.
o If Address and Postal Code were provided, but First Name, Last Name, Phone, and Email were not provided, we search by House Number and Postal Code.
- At this point, the code splits off into two paths, and executes more queries even if the previous Additional Logic returns results.
Additional Search Path 1
Executes if the user provided a First Name or Last Name. If Email was provided, two different searches can occur:
First Email Search
o If Phone was provided in addition to Email and the Phone is at least 10 characters, a query is run matching Phone to Phone 1, Phone 2, or Phone 3, Email is matched against Primary Email or Secondary Email, and Name is like Correspondence Name. There is an OR statement between all of these, so either Email, Phone, or Name has to match, but not necessarily more than one of those.
Second Email Search
o If Phone wasn't provided or wasn't at least 10 characters, a query is run against just Email and Name where the provided Email must match either Primary Email or Secondary Email and the name must be like Correspondence Name.
- If Email wasn't provided, the following two different searches can occur:
First Search without Email
o If Phone was provided and is at least 10 characters, a query is run that checks if Phone matches Phone 1, Phone 2, or Phone 3 or the Name matches Correspondence Name.
Second Search without Email
o If Phone wasn't provided (and neither was Email), a query is run where only the provided Name must be like Correspondence Name.
Additional Search Path 2
Executes if the user did not provide First Name or Last Name
If Phone and Email were provided, a query is run against Phone and Email that looks for any match (OR statements)
If only Phone was provided, a search is done where Phone matches Phone 1, Phone 2, or Phone 3.
If only Email was provided, a search is done where Email matches Primary Email or Secondary Email.
If All Else Fails
- If at this point no Prospects have been found, there is another section of code that attempts to find matching prospects.
- If Last Name, Address, and Postal Code were provided, a query is run where Last Name + House Number + Postal Code equals the field Key_NameZipAddress or the Correspondence Name is like last Name or the First Name (if provided)
- If the previous criteria wasn't provided but First Name or Last Name is provided, one of three query will run against the provided names.
- At this point, if no Prospects have been found, a query is run using only the House Number (Home_Address__c like House Number)
- If no Prospects have been found and Postal Code was provided, a search is done for all Prospects where Postal Code matches