In the screen shot below we can see the demo user "Susan Maye" is limited to assigned the new opportunity to only people in the "Direct Sales" team.

This has been accomplished by using the property of the field (a.k.a entryblock) called SearchSQL. The SearchSQL property is discussed briefly in the System Administration guide.

How Not To Do It!

In the image below we can see the screen that would allow us to add an SQL clause into the SearchSQL property of the entryblock.

We want to make sure that only fellow team members are available to the user in the opportunity screens.

We could easily hardcode the SearchSQL to only display users in the "Direct Sales" team by entering the clause:

user_primarychannelid=1

But we need to make this dynamically reference the current user's team. We may know from other work with Sage CRM that we can reference contextual information using system variables.

We maybe tempted to use the #C symbol and construct an SQL clause that looks like:

user_primarychannelid= #C

But this creates an "Unexpected" event.

In the log we would see that the #C symbol has not been resolved.

Sep 17 2007
8:00:35.218 1232 3256 1 fselectsql,time,sql,errormsg 16 SELECT
User_UserId, User_DisplayName FROM vSearchListUser WHERE
COALESCE(user_disabled, N'') = N'' AND COALESCE(user_istemplate, N'')
<> N'Y' AND (user_primarychannelid = #C) ORDER BY User_Firstname,
User_LastName Invalid column name '#C'

We may also be tempted to use the type of 'merge' field reference that is used in email templates etc and create an SQL clause that looks like:

user_primarychannelid= #user_primarychannelid#

Again an unexpected event would occur creating the error in the log:

Sep 17 2007
9:16:07.375 1232 3340 1 fselectsql,time,sql,errormsg 0 SELECT
User_UserId, User_DisplayName FROM vSearchListUser WHERE
COALESCE(user_disabled, N'') = N'' AND COALESCE(user_istemplate, N'')
<> N'Y' AND (user_primarychannelid = #user_primarychannelid#) ORDER BY
User_Firstname, User_LastName Invalid column name
'#user_primarychannelid#'

The Correct Way To Do It

The way that we can access contextual variables into the SearchSQL clause is by using script. We are going to use the onCreate script. In the case of the opportunity entity we will have to change the onCreate script within each workflow rule that references the oppo_assigneduserid field.

For example in the primary rule we can see:

The SQL clause used is:

SearchSQL = "user_primarychannelid = "+CurrentUser.user_primarychannelid;