Adding field level security to a SalesLogix table

If you ever find yourself in a bind and needing to switch a table from
not being able to be controlled by field level security to being able to (normally this has to be set at time of table creation), here is a handy
way to do it without deleting the table and re-adding it.

Keep in mind that field level security only works on one-to-one tables so one-to-many tables can not have it enabled.

  1. Add a field to the table called SECCODEID as a SLXStandardID data type.
  2. Go to the resynctabledefs table, find the table in question and set the securefield column to T.
  3. Go to the sectabledefs table and switch the seqcode=0 for all rows where tablename=yourtable.
  4. Determine the max field offset value for the entire sectabledefs table (select max(fieldoffset) from sectabledefs).
  5. For each of the rows you updated in step 3, add the
    max(fieldoffset)+1 number to the field offset column.  For instance if
    you have three rows in step 3 and the max(fieldoffset) value=4 then you
    would set the three rows field offset values to 5, 6, and 7.
  6. Once this is done you will need to populate the SECCODEID column
    with the right owner value corresponding to the parent table.  For
    instance, if this is a one-to-one extension off Account then you would
    need to set the SECCODEID of your table to equal the SECCODEID of the
    account it belongs to.

Now that you have done this you should be able to access these fields in the manage field security area of SalesLogix.


Update:  Instead of manually updating each row in step 5 listed above, you can use a SQL statement like this to update all of the rows at once.  You just need to change the {TABLE} in the SQL to the name of the actual table.

update SECTABLEDEFS set FIELDOFFSET = t.fieldoffset
   (select MAX + ROW_NUMBER() over(order by fieldname) fieldoffset,
   fieldname from SECTABLEDEFS where tablename=’{TABLE}’ group
   by fieldname) t
   where SECTABLEDEFS.TABLENAME=’{TABLE}’ and sectabledefs.FIELDNAME=t.fieldname



Want content like this delivered to your inbox? Sign up for our newsletter!

Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

1 Comment

  1. Took me a while to figure out what to do with that SQL statement, since “MAX” isn’t a column in that table, but then I realized it was really supposed to be this:

    update [sysdba].[SECTABLEDEFS] set FIELDOFFSET = t.fieldoffset
    (select (select max(fieldoffset) from sectabledefs) + ROW_NUMBER() over(order by fieldname) fieldoffset,
    fieldname from [sysdba].[SECTABLEDEFS] where tablename=’TABLE’ group
    by fieldname) t
    where SECTABLEDEFS.TABLENAME=’TABLE’ and sectabledefs.FIELDNAME=t.fieldname;


Submit a Comment

Your email address will not be published. Required fields are marked *