x Want to participate? Join the group to interact
  • State Suggested Answer
  • +1 person also asked this people also asked this
  • Replies 2 replies
  • Answers 1 answer
  • Subscribers 77 subscribers
  • Views 588 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
32
replies
General Discussion
6k
topics
18k
replies
DHANSEN

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

SUGGESTED
Posted By

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. 

  • 0
    SUGGESTED

    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

  • 0

    Another method is to use the standard Keyword Word search find screen on the find menu. 

    Here you can tick just Company & Lead and perform your search. 

*Community Hub is the new name for Sage City