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
AS
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
Note:
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.
