Create a List to Show Products Purchased by Company

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Create a List to Show Products Purchased by Company

  • Comments 4
  • Likes

A business had a requirement to easily see what products have been ordered by their customers.

Below shows the products that have been purchased by a company.

This was created using the List and Runblock technique discussed in the article and video "Using RunBlock and Lists for Codeless Customization".

I added a view to the NewProducts table called "vProductsOrdered".

This view had the following SQL.

CREATE VIEW vProductsOrdered 
SELECT     dbo.NewProduct.Prod_ProductID, dbo.NewProduct.prod_Active, dbo.NewProduct.prod_UOMCategory, dbo.NewProduct.prod_name, 
dbo.NewProduct.prod_code, dbo.NewProduct.prod_productfamilyid, dbo.OrderItems.*, dbo.Orders.*, dbo.Opportunity.Oppo_OpportunityId, 
dbo.Opportunity.Oppo_PrimaryCompanyId, dbo.Opportunity.Oppo_PrimaryPersonId, dbo.Company.Comp_CompanyId, 
dbo.Opportunity.Oppo_AssignedUserId, dbo.Opportunity.Oppo_ChannelId, dbo.Opportunity.Oppo_CreatedBy, dbo.Opportunity.Oppo_SecTerr, 
dbo.Company.Comp_CreatedBy, dbo.Company.Comp_UpdatedBy, dbo.Company.Comp_SecTerr, dbo.Company.Comp_ChannelID, 
dbo.Person.Pers_PersonId, dbo.Person.Pers_CompanyId, dbo.Person.Pers_CreatedBy, dbo.Person.Pers_UpdatedBy, dbo.Person.pers_SecTerr, 
dbo.Person.Pers_ChannelID, dbo.Company.Comp_PrimaryUserId, dbo.Person.Pers_PrimaryUserId 
FROM         dbo.NewProduct INNER JOIN 
dbo.OrderItems ON dbo.NewProduct.Prod_ProductID = dbo.OrderItems.OrIt_productid INNER JOIN 
dbo.Orders ON dbo.OrderItems.OrIt_orderquoteid = dbo.Orders.Orde_OrderQuoteID INNER JOIN 
dbo.Opportunity ON dbo.Orders.Orde_opportunityid = dbo.Opportunity.Oppo_OpportunityId INNER JOIN 
dbo.Company ON dbo.Opportunity.Oppo_PrimaryCompanyId = dbo.Company.Comp_CompanyId INNER JOIN 
dbo.Person ON dbo.Opportunity.Oppo_PrimaryPersonId = dbo.Person.Pers_PersonId


I had to make sure that the as I included the Opportunity, Person and Company tables the correct columns were included for security.  These are the xxxx_secterr, xxxx_createdby, xxxx_channelid, and xxxx_primaryuserid (xxxx_assigneduserid) fields.

I then built a list called "ProductsOrderedList" based on the "vProductsOrdered" view that I created.

This then allowed me to include any fields that I wanted to add.  I added hyperlinks and ordering capability to the fields.

I then added the List to the Company Tab group using the RunBlock action.



  • This is a great quick solution Jeff. Azamba has a solution that does something similar and it adds a denormalized multi-select on to the Company entity record to allow for easy filtering for reports, groups and dashboards.

    No... it's not in the product listing yet.

  • Thank you Peter! - I am looking forward to meeting your colleagues when they are over in Dublin.

  • Hi Jeff Great blog.

    What about if the customer wants to be able to see the reverse;  A list of all customers that have purchased a particular product. Perhaps create a group from the results and to target with an upgrade when an old version is sunsetted?

  • This is useful, although I have a requirement to list (or search for) Companies who have NOT purchased any given Product so that they may be targeted for cross-selling. I wonder whether someone has figured out a way to do this?