x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 2 replies
  • Subscribers 75 subscribers
  • Views 481 views
  • Users 0 members are here
Browse Forums
Announcements
102
topics
32
replies
General Discussion
6k
topics
18k
replies
Mikey J.

Opportunities without order

Posted By

Was wondering if there was any way to view all of the quotes/opportunities that we have created that do NOT have an associated order attached to them. We want to follow up on all of our unfulfilled quotes in one batch without missing any due to human error. Is this possible for a layperson/regular user without any SQL know-how or should I contact an administrator for help?

  • 0

    Hi MIkey

    Unfortunately within CRM without Admin help you won't be able to do it, but your Administrator could use the below to create a Source View to be used in a report

    You view all Opportunities with a quote, but without an order you could do 

    SELECT *
    FROM Opportunity (NOLOCK)
    JOIN Company (NOLOCK) ON oppo_primarycompanyid = comp_companyid
    WHERE oppo_opportunityid IN (SELECT ISNULL(quot_opportunityid,0) FROM Quotes (NOLOCK) WHERE quot_deleted IS NULL)
    AND oppo_opportunity NOT IN (SELECT ISNULL(orde_opportunityid,0) FROM Orders (NOLOCK) WHERE orde_deleted IS NULL)
    AND oppo_deleted IS NULL

  • 0 in reply to Matthew Shaw

    If you want to view it from a Quote perspective and so see a line for every quote (the above would give you one line per opportunity) you could do 

    SELECT *
    FROM Quotes (NOLOCK)
    JOIN Opportunity (NOLOCK) ON quot_opportunityid = oppo_opportinityid
    JOIN Company (NOLOCK) ON oppo_primarycompanyid = comp_companyid
    WHERE oppo_opportunityid NOT IN (SELECT ISNULL(orde_opportunityid,0) FROM Orders (NOLOCK) WHERE orde_deleted IS NULL)
    AND quot_deleted IS NULL

*Community Hub is the new name for Sage City