More about using U:, C: and T: in the Tab SQL Clause

1 minute read time.
In the "Hiding the Global Library (Shared Documents) by teams" article we saw that...

"The SQL to restrict access to this tab to only people in certain teams can be expressed as

user_primarychannelid in (1,5)

or special clauses that use a form specific for Sage CRM can be used

C: 1,5 (this restricts access to members of Teams with IDs 1 or 5
U: 1,5 (this restricts access to users with user_userids of 1 or 5"

Something that was missed out in that article was the T: clause. T: can be used to restrict the tab to territories, using the Territory ID (terr_territoryid).
For example: T: -2097151993

The SQL actually used can be examined to gain some further insight into the way CRM works.

A Tab SQL clause like:

user_primarychannelid in (4,5)

will generate the SQL

select * from vusers where user_userid=4 and (user_primarychannelid in (4,5))

But the clause

C: 4,5

creates the SQL

SELECT * FROM UserContacts WHERE COALESCE(Ucnt_UserId, 0) = 4 AND COALESCE(Ucnt_PersonId, 0) = 30 AND UCnt_Deleted is NULL

We can see it is using a different target table in its SQL clause.

These special clauses (C:, T:, U:) may not be used in conjunction with anything else.

For example the Tab SQL Clause

C: 1 and comp_type = 'customer'

would not error, but would only generate

SELECT * FROM UserContacts WHERE COALESCE(Ucnt_UserId, 0) = 4 AND COALESCE(Ucnt_PersonId, 0) = 30 AND UCnt_Deleted is NULL

The additional predicate is ignored.
  • After some trial and error I found the only way to combine criteria from user and company table here is

    user_userid = #comp_primaryuserid#

    this is working and TAB will only show for the account manager.

    comp_primaryuserid = #user_userid#

    does generate an SQL error because it is always queried against vusers if "user_" is found in your input