Opportunities without order

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?

  • 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

  • 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