This article considers how the availability of a tab in the company context maybe controlled so that it is only displayed when the customer has purchased a particular product.
The general availability of a Tab for a user is controlled by the tabs SQL Clause in the tab group definition.
You can read more about the Tab SQL Clause is these other articles.
But only if the customer has bought a license...
Imagine that the Cases tab should only be shown on the Company Screen if the customer has bought a particular license.
The information about the customer's purchase would be held in the OrderItems table.
We need to link the company to the license purchased. The way that data is normally related within Sage CRM is via the database views. Views are fundamental in building every structure within CRM.
We need a view that links the tables
-
Company
-
Opportunity
-
Order
-
OrderItems
-
NewProducts
I created a 'NewProduct' view called vCompanyProduct. The System Administrator Guide explains how to create a new view. The SQL of my view looked like this.
CREATE VIEW vCompanyProduct
AS
SELECT dbo.Company.Comp_CompanyId, dbo.Orders.Orde_OrderQuoteID, dbo.Opportunity.Oppo_OpportunityId, dbo.NewProduct.Prod_ProductID,
dbo.NewProduct.prod_name, dbo.NewProduct.prod_code
FROM dbo.Company INNER JOIN
dbo.Opportunity ON dbo.Company.Comp_CompanyId = dbo.Opportunity.Oppo_PrimaryCompanyId INNER JOIN
dbo.Orders ON dbo.Opportunity.Oppo_OpportunityId = dbo.Orders.Orde_opportunityid INNER JOIN
dbo.OrderItems ON dbo.Orders.Orde_OrderQuoteID = dbo.OrderItems.OrIt_orderquoteid INNER JOIN
dbo.NewProduct ON dbo.OrderItems.OrIt_productid = dbo.NewProduct.Prod_ProductID
Note:
This view has been designed specifically for use in an SQL Clause. If you want to use the view to retrieve data into a List, Report or Group then the view would need to be extended to include the security related columns from the main entities referenced. (Company, Opportunity). Please see the article "Sage CRM and Security Territories".
https://community.sagecrm.com/blogs/hints_tips_and_tricks/archive/2007/11/27/sage-crm-and-security-territories.aspx
The Tab Clause
Once the view was in place I was then used the SQL Tab Clause that looked like this.
exists (select * from vCompanyProduct where prod_code='EXPUSR6' and comp_companyid = #comp_companyid#)
This check that the company had placed an order for a particular product.
This example is only a proof of concept and it can be substantially changed to reference the product family and other information as necessary.