• Search Select Advanced Fields
• Security and Data Access
• Dynamically Changing Field Properties using an Internal (Create) Script 

Imagine a business requirement to include in the Company Field a lookup to a list of Supplier Companies.

At first sight this is easy to do as we can use a Search Select Advanced Field

We can set the Meta Data properties of the new supplier field:

• FieldName: comp_supplierid
• EntryType: Search Select Advanced
• Search Entity: Company
• Search SQL: comp_type= 'Supplier';
• View Fields: Territory, Company Name

 
We could resolve the problem by creating a special security territory for companies that are of type supplier and we could give absolute rights to that new territory to the users. But this may be inconvenient to the users and we may not want to add complexity to the security model.

So how can we by pass security for a lookup field? Certainly we are not be able to force the Search Select Advanced field to ignore security as the security feature is fundamental to its behaviour. What we can do is hijack some of its properties and then directly control the retrieval of the data using the eWareQuery Object. The eWareQuery object ignores the security profiles and we can decide exactly the SQL that gets passed to the Sage CRM database.

I can hijack the field by using the CreateScript within the screen (CompanyBoxLong).

I have listed the code below but in general the tasks I need to are

1. Check if a SupplierID exists
2. Check if screen is in Edit Mode (I have discussed this type of check before)
• Make the field ReadOnly
• Build a query statement to get the Supplier companies
• Start assembling the HTML for a selection list
• Process the return rows from the query to create the options in the HTML selection
• Make sure that any existing value is marked as selected
• Reset the field's Caption property to include the original caption plus the HTML selection field


3. Check if we are in View/Save Mode and include check to if user normally has rights to view Supplier
• If user has rights then normal Search Select Advanced behaviour used for display



• If user doesn't have rights then the output of the Supplier company name is rebuilt.

---

var intSupplierId = CRM.GetContextInfo("company","comp_supplierid");
var strSQL ="";
var CompanyQRY = CRM.CreateQueryObj(strSQL,"");
if(Values("Act")!=200 && !Values("_actionid"))
{
ReadOnly=true;

var CustomField = "<SELECT class=EDIT size=1 name='comp_supplierid'>";
CustomField+="<OPTION Value='>"+CRM.GetTrans("GenCaptions","none")+"</OPTION>";

strSQL +=" SELECT dbo.vSummaryCompany.*, dbo.Territories.Terr_TerritoryID, dbo.Territories.Terr_Caption";
strSQL +=" FROM dbo.vSummaryCompany INNER JOIN";
strSQL +=" dbo.Territories ON dbo.vSummaryCompany.Comp_SecTerr = dbo.Territories.Terr_TerritoryID";
strSQL +=" WHERE (dbo.vSummaryCompany.Comp_Type = N'supplier')";

CompanyQRY.SQL = strSQL;
CompanyQRY.SelectSQL();
while (!CompanyQRY.eof)
{
CustomField+="<OPTION Value='"+CompanyQRY.FieldValue("comp_companyid")+"'";
if (CompanyQRY.FieldValue("comp_companyid")==intSupplierId)
{
CustomField+=" SELECTED >";
}
else
{
CustomField+=">";
}
CustomField+=CompanyQRY.FieldValue("comp_name")+", "+CRM.GetTrans("Tags",CompanyQRY.FieldValue("Terr_Caption"))+"</OPTION>";
CompanyQRY.NextRecord();
}
CustomField +="</SELECT>";
Caption = CRM.GetTrans("ColNames","comp_supplierid")+":<BR>"+CustomField;
}
else
{
var checkRecord = CRM.FindRecord("company","comp_companyid="+intSupplierId);
if (Values("comp_supplierid") && intSupplierId && checkRecord.eof)
{
strSQL +="select comp_name, ";
strSQL +="comp_phonecountrycode+comp_phoneareacode+comp_phonenumber as comp_fullnumber";
strSQL +=" from company where comp_companyid ="+intSupplierId;
CompanyQRY.SQL = strSQL;
CompanyQRY.SelectSQL();
if (CompanyQRY.eof)
{
Caption = CRM.GetTrans("ColNames","comp_supplierid")+":<BR>"+CRM.GetTrans("WebPicker","-noneselected-");
}
else
{
Caption = CRM.GetTrans("ColNames","comp_supplierid");
Caption +=":</SPAN><BR><SPAN class=viewbox>"
Caption +=CompanyQRY.FieldValue("comp_name");
Caption +="<img src='/CRM/img/Icons/SmallPhone.gif' HSPACE=0 BORDER=0 ALIGN=TOP HEIGHT=16>";
Caption += CompanyQRY.FieldValue("comp_fullnumber");
Caption +="</SPAN>";
}
}
}