Create a single Lookup, for Both Company, and Lead.

Question

Hi All. 
 I have a client on CRM 2017, that does not like that, if they want to lookup a prospective customer in CRM, that they have to do the company lookup, to see if the prospect exists as a company; and if not, then has to do the lookup in Leads, to see if the prospective customer exists in Leads. They are asking if we could create a single lookup, that when they type in a name, that the lookup screen will show the matching records that exist, in Leads AND Company records; within that same lookup screen. Then if they click a hyperlink, to have the link take them to the appropriate Company or Lead record. 
 I have been supporting CRM for about 10 years now; so I would like to think I am descently versed in CRM and SQL. But I am not sure if this is possible with CRM to do; and if so, how to go about creating this. I am looking to see if anyone has done something like this before; or if anyone has an suggestions, on how to go about doing this. Or if someone has any idea, if doing something like this, is even possible. 
 Thanks for any direction on this. 
 Dan.

bmalik_1 · Answer

Hi Dan, This isn't an uncommon problem to have. I've used the below code to create a view within SQL: 
 
 CREATE VIEW vLeadCompanyUnion AS Select Lead_LeadId, Lead_CompanyName, Lead_AssignedUserID from Lead where lead_deleted is null 
 UNION 
 Select Comp_CompanyId, comp_name, Comp_PrimaryUserId from Company where comp_deleted is null 
 You can add more meat to the view (I did, but deleted to keep the example simple), and even apply additional conditions. The tricky part is in adding additional columns. The column or a corresponding column must exist in both tables. You can't write a SQL UNION like the below: 
 CREATE VIEW vLeadCompanyUnion AS Select Lead_LeadId, Lead_CompanyName, Lead_AssignedUserID, lead_stuff, lead_morestuff from Lead where lead_deleted is null 
 UNION 
 Select Comp_CompanyId, comp_name, Comp_PrimaryUserId from Company where comp_deleted is null 
 If you add columns to one part of the unioned view, but not the other you'll get a SQL error. I don't claim to be a programmer by any means, but the first SQL query/view allows me to do a search within a report, or create a List gadget to do a search within the filter criteria to identify if a record exists in either table. Hope that helped, or at the very least didn't create any confusion. Best Regards, Basil Malik

Sage CRM

Create a single Lookup, for Both Company, and Lead.

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING