Demystifying TIER Permissions

2 Comments

TIER has a complex set of permissions. The amount of control possible through the user interface can be staggering. The way that TIER permissions are stored in the database is fairly simple but can be cryptic without the magic decoder ring. Magic decoder ring follows.

The following tables and columns are directly involved in permissions:

T4W_CLASSES
  OP__ID
  OP__CLASSCLASSNAME

T4W_GROUPS
  OP__ID
  OP__TIERNAME

T4W_GROUPS.OP__ID is a negative number

T4W_KEYS
  OP__ID
  OP__OWNERCLASS
  OP__OWNERID
  OP__OBJECTCLASSID
  OP__OBJECTID
  OP__DENIALSORD
  OP__GRANTSORD

Note that deletes to T4W_GROUPS and T4W_USERS do not delete records in T4W_KEYS.

T4W_KEYS.OP__OWNERCLASS is not used and is always null.

In T4W_KEYS.OP__OWNERID positive values map to T4W_USERS.OP__ID. Negative values map to T4W_GROUPS.OP__ID.

T4W_KEYS.OP__OBJECTCLASSID maps to T4W_CLASSES.OP__ID.

T4W_CLASSES.OP__OBJECTID maps to the object classes T4W_[TABLE] dependent on OP__OBJECTCLASSID. For example if T4W_KEYS.OP__OBJECTCLASSID is 0 then T4W_KEYS.OP__OBJECTID maps to T4W_CLASSES.OP__ID. IF T4W_KEYS.OP__OBJECTCLASSID is 9 then T4W_KEYS.OP__OBJECTID maps to T4W_FORMS.OP__ID.

T4W_KEYS.OP__DENIALSORD and T4W_KEYS.OP__GRANTSORD are bit-product columns.

T4W_MEMBERS
  LSID
  RSID

T4W_USERS
  OP__ID
  OP__TIERNAME

Some queries that show the relationships:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

--groups members
SELECT g.OP__TIERNAME
, u.OP__TIERNAME
FROM T4W_GROUPS g
JOIN T4W_MEMBERS m ON g.OP__ID = m.RSID
JOIN T4W_USERS u ON m.LSID = u.OP__ID
ORDER BY g.OP__TIERNAME
, u.OP__TIERNAME

--groups keys
SELECT g.op__tiername
, c.op__classclassname
, k.op__objectid
, k.op__denialsord
, k.op__grantsord
FROM t4w_keys k
JOIN t4w_groups g ON k.op__ownerid = g.op__id
JOIN t4w_classes c ON k.OP__OBJECTCLASSID = c.op__id
order by g.op__tiername
, c.op__classclassname
, op__denialsord

--users keys
SELECT u.op__tiername
, c.op__classclassname
, k.op__objectid
, k.op__denialsord
, k.op__grantsord
FROM t4w_keys k
JOIN t4w_users u ON k.op__ownerid = u.op__id
JOIN t4w_classes c ON k.OP__OBJECTCLASSID = c.op__id
order by u.op__tiername
, c.op__classclassname
, op__denialsord

T4W_KEYS.OP__DENIALSORD and T4W_KEYS.OP__GRANTSORD is where it gets interesting. These two columns are bit-product columns. What that means is that each permission maps to a single bit value. That value is summed up and stored in these columns. I’m new to this design pattern myself. Converting the decimal values to binary helped to clarify the situation.

Parameter	Decimal	Binary
Parameter 1	1	1
Parameter 2	2	10
Parameter 3	4	100
Parameter 4	8	1000
Parameter 5	16	10000

This chart should help to show that a decimal value of 12 equals binary value 1100 which is equal to parameters 3 (100) and 4 (1000). T-SQL Bitwise Operations was instrumental in helping me to understand what was going on and how to put it to use.

Mappings

Unfortunately TIERs mapping of parameters to values is in the executable. We’ll have to create our own mapping tables and look up each key by hand. Here is a good start for RM_PermissionKeyGroup and RM_PermissionKeys. I’ve only tracked down permissions for Cabinets, Folders, Forms and Form Sections. You’ll have to do the rest on your own for now.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

/*this table is used to properly map permissions to groups

SELECT *
FROM RM_PermissionKeyGroup
ORDER BY SortOrder

*/

DROP TABLE RM_PermissionKeyGroup

CREATE TABLE RM_PermissionKeyGroup(
ID int NULL,
Name varchar(50) NULL,
OP__ObjectClassID int NULL,
OP__ObjectID int NULL,
SortOrder int NULL
)

--Form Class (all forms)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (1, 'Audit Forms - All', NULL, NULL, 1)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (2, 'Audit Forms - By Category', NULL, NULL, 2)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (3, 'Audit Forms - By Form', NULL, NULL, 3)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (4, 'Audit Reports - All', NULL, NULL, 4)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (5, 'Audit Reports - By Category', NULL, NULL, 5)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (6, 'Audit Reports - By Form', NULL, NULL, 6)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (7, 'Auto Log Off', NULL, NULL, 7)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (8, 'Cabinets - All', 0, 6, 8)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (9, 'Cabinets - By Cabinet', 6, NULL, 9)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (10, 'Configuration', NULL, NULL, 10)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (11, 'Databases - All', NULL, NULL, 11)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (12, 'Databases - By Database', NULL, NULL, 12)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (13, 'Drill Downs - All', NULL, NULL, 13)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (14, 'Drill Downs - By Category', NULL, NULL, 14)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (15, 'Drill Downs - By Drill Down', NULL, NULL, 15)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (16, 'Folder Groups - All', NULL, NULL, 16)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (17, 'Folder Groups - By Category', NULL, NULL, 17)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (18, 'Folder Groups - By Group', NULL, NULL, 18)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (19, 'Folders - All', 0, 15, 19)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (20, 'Folders - By Cabinet', 6, NULL, 20)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (21, 'Folders - By Folder', 15, NULL, 21)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (22, 'Form Packets - All', NULL, NULL, 22)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (23, 'Form Packets - By Category', NULL, NULL, 23)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (24, 'Form Packets - By Packet', NULL, NULL, 24)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (25, 'Form Sections - All', 0, 17, 25)
--INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (26, 'Form Sections - By Form', NULL, NULL, 26) --actually done with forms
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (27, 'Form Sections - By Section', 17, NULL, 27)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (28, 'Forms - All', 0, 9, 28)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (29, 'Forms - By Category', 8, NULL, 29)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (30, 'Forms - By Form', 9, NULL, 30)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (31, 'Groups - All', NULL, NULL, 31)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (32, 'Groups - By Group', NULL, NULL, 32)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (33, 'Important Specification - All', NULL, NULL, 33)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (34, 'Important Specification - By Category', NULL, NULL, 34)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (35, 'Important Specification - By Specification', NULL, NULL, 35)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (36, 'Locks', NULL, NULL, 36)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (37, 'Reports - All', NULL, NULL, 37)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (38, 'Reports - By Category', NULL, NULL, 38)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (39, 'Reports - By Report', NULL, NULL, 39)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (40, 'Schedule', NULL, NULL, 40)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (41, 'Snap Shot Definitions - All', NULL, NULL, 41)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (42, 'Snap Shot Definitions - By Category', NULL, NULL, 42)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (43, 'Snap Shot Definitions - By Snapshot Definition', NULL, NULL, 43)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (44, 'SQLReports - All', NULL, NULL, 44)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (45, 'SQLReports - By Category', NULL, NULL, 45)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (46, 'SQLReports - By SQL Report', NULL, NULL, 46)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (47, 'Tasks', NULL, NULL, 47)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (48, 'TierReportBuilder - All', NULL, NULL, 48)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (49, 'TierReportBuilder - By Category', NULL, NULL, 49)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (50, 'TierReportBuilder - By Reports', NULL, NULL, 50)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (51, 'Users - All', NULL, NULL, 51)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (52, 'Users - By Group', NULL, NULL, 52)
INSERT INTO RM_PermissionKeyGroup (ID, Name, OP__ObjectClassID, OP__ObjectID, SortOrder) VALUES (53, 'Users - By User', NULL, NULL, 53)

/*this table is used to properly map permissions to groups

select *
from RM_PermissionKeys
order by KeyGroupID
, sortorder

*/

DROP TABLE RM_PermissionKeys

CREATE TABLE [RM_PermissionKeys](
[KeyGroupID] [int] NULL,
[Permission] [varchar](50) NULL,
[DecimalValue] [int] NULL,
[SortOrder] [int] NULL
)

--Cabinets - All
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (8, 'Root Visible', 256, 1)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (8, 'Visible', 1, 2)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (8, 'View Properties', 2, 3)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (8, 'Edit Properties', 4, 4)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (8, 'New Cabinet', 8, 5)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (8, 'Delete Cabinet', 16, 6)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (8, 'Move Cabinet', 64, 7)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (8, 'Copy Cabinet', 128, 8)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (8, 'New Folder', 32, 9)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (8, 'Import Info', 512, 10)

--Cabinets - By Cabinet
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (9, 'Visible', 1, 2)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (9, 'View Properties', 2, 3)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (9, 'Edit Properties', 4, 4)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (9, 'New Cabinet', 8, 5)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (9, 'Delete Cabinet', 16, 6)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (9, 'Move Cabinet', 64, 7)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (9, 'Copy Cabinet', 128, 8)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (9, 'New Folder', 32, 9)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (9, 'Import Info', 512, 10)

--Folders - All
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (19, 'Visible', 65536, 1)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (19, 'See Contents List', 131072, 2)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (19, 'Send To Folder Group', 32768, 3)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (19, 'View Documents', 262144, 4)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (19, 'Print Documents', -2147483648, 5)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (19, 'Edit Documents', 524288, 6)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (19, 'Move Documents', 268435456, 7)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (19, 'Save Documents', 1048576, 8)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (19, 'Copy Documents', 8192, 9)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (19, 'Save Drafts', 2097152, 10)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (19, 'New Document', 4194304, 11)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (19, 'Delete Document', 8388608, 12)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (19, 'Rename Folder', 16777216, 13)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (19, 'Delete Folder', 33554432, 14)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (19, 'Move Folder', 536870912, 15)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (19, 'Copy Folder', 1073741824, 16)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (19, 'New Subfolder', 67108864, 17)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (19, 'Delete Subfolder', 134217728, 18)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (19, 'Import Info', 16384, 19)

--Folders - By Cabinet
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (20, 'Visible', 65536, 1)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (20, 'See Contents List', 131072, 2)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (20, 'Send To Folder Group', 32768, 3)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (20, 'View Documents', 262144, 4)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (20, 'Print Documents', -2147483648, 5)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (20, 'Edit Documents', 524288, 6)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (20, 'Move Documents', 268435456, 7)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (20, 'Save Documents', 1048576, 8)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (20, 'Copy Documents', 8192, 9)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (20, 'Save Drafts', 2097152, 10)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (20, 'New Document', 4194304, 11)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (20, 'Delete Document', 8388608, 12)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (20, 'Rename Folder', 16777216, 13)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (20, 'Delete Folder', 33554432, 14)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (20, 'Move Folder', 536870912, 15)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (20, 'Copy Folder', 1073741824, 16)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (20, 'New Subfolder', 67108864, 17)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (20, 'Delete Subfolder', 134217728, 18)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (20, 'Import Info', 16384, 19)

--Folders - By Folder
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (21, 'See Contents List', 131072, 2)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (21, 'Send To Folder Group', 32768, 3)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (21, 'View Documents', 262144, 4)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (21, 'Print Documents', -2147483648, 5)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (21, 'Edit Documents', 524288, 6)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (21, 'Move Documents', 268435456, 7)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (21, 'Save Documents', 1048576, 8)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (21, 'Copy Documents', 8192, 9)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (21, 'Save Drafts', 2097152, 10)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (21, 'New Document', 4194304, 11)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (21, 'Delete Document', 8388608, 12)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (21, 'Rename Folder', 16777216, 13)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (21, 'Delete Folder', 33554432, 14)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (21, 'Move Folder', 536870912, 15)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (21, 'Copy Folder', 1073741824, 16)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (21, 'New Subfolder', 67108864, 17)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (21, 'Delete Subfolder', 134217728, 18)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (21, 'Import Info', 16384, 19)

--Form Sections - All
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (25, 'View', 16777216, 1)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (25, 'Edit', 33554432, 2)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (25, 'Sign', 67108864, 3)

--Form Sections - By Form
--These are actually in the form permissions.

--Form Sections - By Section
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (27, 'View', 16777216, 1)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (27, 'Edit', 33554432, 2)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (27, 'Sign', 67108864, 3)

--Form Class (all forms)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'Root Visible', 131072, 1)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'Category Visible', 262144, 2)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'Visible', 1, 3)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'Add To Folder Group', 4194304, 4)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'Scan Properties', 2, 5)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'View Properties', 4, 6)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'New Form', 8, 7)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'New Category', 16, 8)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'Delete Category', 32, 9)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'Rename Category', 64, 10)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'Copy Category', 524288, 11)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'View Form', 128, 12)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'Edit Form', 256, 13)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'Delete Form', 512, 14)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'Copy Form', 1024, 15)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'Move Form', 2048, 16)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'Visible in Folder', 2097152, 17)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'View Documents', 4096, 18)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'New Documents', 8388608, 19)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'Edit Documents', 8192, 20)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'Rename Documents', 1048576, 21)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'Print Documents', 65536, 22)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'Save Documents', 16384, 23)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'Save Drafts', 32768, 24)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'View Sections', 16777216, 25)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'Edit Sections', 33554432, 26)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (28, 'Sign Sections', 67108864, 27)

--Form Categories
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'Category Visible', 262144, 2)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'Visible', 1, 3)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'Add To Folder Group', 4194304, 4)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'Scan Properties', 2, 5)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'View Properties', 4, 6)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'New Form', 8, 7)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'Delete Category', 32, 9)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'Rename Category', 64, 10)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'Copy Category', 524288, 11)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'View Form', 128, 12)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'Edit Form', 256, 13)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'Delete Form', 512, 14)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'Copy Form', 1024, 15)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'Move Form', 2048, 16)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'Visible in Folder', 2097152, 17)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'View Documents', 4096, 18)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'New Documents', 8388608, 19)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'Edit Documents', 8192, 20)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'Rename Documents', 1048576, 21)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'Print Documents', 65536, 22)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'Save Documents', 16384, 23)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'Save Drafts', 32768, 24)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'View Sections', 16777216, 25)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'Edit Sections', 33554432, 26)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (29, 'Sign Sections', 67108864, 27)

--Forms - by form
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (30, 'Visible', 1, 3)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (30, 'Add To Folder Group', 4194304, 4)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (30, 'View Form', 128, 12)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (30, 'Edit Form', 256, 13)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (30, 'Delete Form', 512, 14)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (30, 'Copy Form', 1024, 15)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (30, 'Move Form', 2048, 16)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (30, 'Visible in Folder', 2097152, 17)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (30, 'View Documents', 4096, 18)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (30, 'New Documents', 8388608, 19)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (30, 'Edit Documents', 8192, 20)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (30, 'Rename Documents', 1048576, 21)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (30, 'Print Documents', 65536, 22)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (30, 'Save Documents', 16384, 23)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (30, 'Save Drafts', 32768, 24)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (30, 'View Sections', 16777216, 25)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (30, 'Edit Sections', 33554432, 26)
INSERT INTO RM_PermissionKeys (KeyGroupID, Permission, DecimalValue, SortOrder) VALUES (30, 'Sign Sections', 67108864, 27)

Reporting Values

Here is a query showing our mappings being put to use.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276

SELECT CASE
WHEN g.OP__TIERNAME IS NOT NULL THEN 'Group'
WHEN u.OP__TIERNAME IS NOT NULL THEN 'User'
END AS GroupUser
, CASE
WHEN g.OP__TIERNAME IS NOT NULL THEN g.OP__TIERNAME
WHEN u.OP__TIERNAME IS NOT NULL THEN u.OP__TIERNAME
END AS Name
, pkg.Name AS KeyGroup
, NULL AS Item
, pk.Permission
, 'Grant' AS Setting
, pkg.SortOrder AS KeyGroupOrder
, pk.SortOrder
FROM T4W_KEYS k
JOIN T4W_CLASSES c ON k.OP__OBJECTID = c.OP__ID
JOIN RM_PermissionKeyGroup pkg ON pkg.OP__ObjectID = c.OP__ID
JOIN RM_PermissionKeys pk ON pkg.ID = pk.KeyGroupID
LEFT JOIN T4W_GROUPS g ON g.OP__ID = k.OP__OWNERID
LEFT JOIN T4W_USERS u ON u.OP__ID = k.OP__OWNERID
WHERE k.OP__OBJECTCLASSID = 0
AND k.op__grantsord & pk.decimalvalue <> 0
AND (g.OP__TIERNAME IS NOT NULL
OR u.OP__TIERNAME IS NOT NULL)

UNION ALL

SELECT CASE
WHEN g.OP__TIERNAME IS NOT NULL THEN 'Group'
WHEN u.OP__TIERNAME IS NOT NULL THEN 'User'
END AS GroupUser
, CASE
WHEN g.OP__TIERNAME IS NOT NULL THEN g.OP__TIERNAME
WHEN u.OP__TIERNAME IS NOT NULL THEN u.OP__TIERNAME
END AS Name
, pkg.Name AS KeyGroup
, NULL AS Item
, pk.Permission
, 'Denial' AS Setting
, pkg.SortOrder AS KeyGroupOrder
, pk.SortOrder
FROM T4W_KEYS k
JOIN T4W_CLASSES c ON k.OP__OBJECTID = c.OP__ID
JOIN RM_PermissionKeyGroup pkg ON pkg.OP__ObjectID = c.OP__ID
JOIN RM_PermissionKeys pk ON pkg.ID = pk.KeyGroupID
LEFT JOIN T4W_GROUPS g ON g.OP__ID = k.OP__OWNERID
LEFT JOIN T4W_USERS u ON u.OP__ID = k.OP__OWNERID
WHERE k.OP__OBJECTCLASSID = 0
AND k.op__denialsord & pk.decimalvalue <> 0
AND (g.OP__TIERNAME IS NOT NULL
OR u.OP__TIERNAME IS NOT NULL)

UNION ALL

SELECT CASE
WHEN g.OP__TIERNAME IS NOT NULL THEN 'Group'
WHEN u.OP__TIERNAME IS NOT NULL THEN 'User'
END AS GroupUser
, CASE
WHEN g.OP__TIERNAME IS NOT NULL THEN g.OP__TIERNAME
WHEN u.OP__TIERNAME IS NOT NULL THEN u.OP__TIERNAME
END AS Name
, pkg.Name AS KeyGroup
, c.op__tiername AS Item
, pk.Permission
, 'Grant' AS Setting
, pkg.SortOrder AS KeyGroupOrder
, pk.SortOrder
FROM T4W_KEYS k
JOIN T4W_CABINETS c ON k.OP__OBJECTID = c.OP__ID
JOIN RM_PermissionKeyGroup pkg ON pkg.op__ObjectClassID = k.op__ObjectClassID
JOIN RM_PermissionKeys pk ON pkg.ID = pk.KeyGroupID
LEFT JOIN T4W_GROUPS g ON g.OP__ID = k.OP__OWNERID
LEFT JOIN T4W_USERS u ON u.OP__ID = k.OP__OWNERID
WHERE k.OP__OBJECTCLASSID = 6
AND k.op__grantsord & pk.decimalvalue <> 0
AND (g.OP__TIERNAME IS NOT NULL
OR u.OP__TIERNAME IS NOT NULL)

UNION ALL

SELECT CASE
WHEN g.OP__TIERNAME IS NOT NULL THEN 'Group'
WHEN u.OP__TIERNAME IS NOT NULL THEN 'User'
END AS GroupUser
, CASE
WHEN g.OP__TIERNAME IS NOT NULL THEN g.OP__TIERNAME
WHEN u.OP__TIERNAME IS NOT NULL THEN u.OP__TIERNAME
END AS Name
, pkg.Name AS KeyGroup
, c.op__tiername AS Item
, pk.Permission
, 'Denial' AS Setting
, pkg.SortOrder AS KeyGroupOrder
, pk.SortOrder
FROM T4W_KEYS k
JOIN T4W_CABINETS c ON k.OP__OBJECTID = c.OP__ID
JOIN RM_PermissionKeyGroup pkg ON pkg.op__ObjectClassID = k.op__ObjectClassID
JOIN RM_PermissionKeys pk ON pkg.ID = pk.KeyGroupID
LEFT JOIN T4W_GROUPS g ON g.OP__ID = k.OP__OWNERID
LEFT JOIN T4W_USERS u ON u.OP__ID = k.OP__OWNERID
WHERE k.OP__OBJECTCLASSID = 6
AND k.op__denialsord & pk.decimalvalue <> 0
AND (g.OP__TIERNAME IS NOT NULL
OR u.OP__TIERNAME IS NOT NULL)

UNION ALL

SELECT CASE
WHEN g.OP__TIERNAME IS NOT NULL THEN 'Group'
WHEN u.OP__TIERNAME IS NOT NULL THEN 'User'
END AS GroupUser
, CASE
WHEN g.OP__TIERNAME IS NOT NULL THEN g.OP__TIERNAME
WHEN u.OP__TIERNAME IS NOT NULL THEN u.OP__TIERNAME
END AS Name
, pkg.Name AS KeyGroup
, ISNULL(f.OP__TIERNAME, '') + ' -- ' + ISNULL(fs.op__tiername,'') + '\"' AS Item
, pk.Permission
, 'Grant' AS Setting
, pkg.SortOrder AS KeyGroupOrder
, pk.SortOrder
FROM T4W_KEYS k
JOIN T4W_FORMSECTIONS fs ON k.OP__OBJECTID = fs.OP__ID
JOIN T4W_FORMS f ON fs.OP__PARENTID = f.OP__ID
JOIN RM_PermissionKeyGroup pkg ON pkg.op__ObjectClassID = k.op__ObjectClassID
JOIN RM_PermissionKeys pk ON pkg.ID = pk.KeyGroupID
LEFT JOIN T4W_GROUPS g ON g.OP__ID = k.OP__OWNERID
LEFT JOIN T4W_USERS u ON u.OP__ID = k.OP__OWNERID
WHERE k.OP__OBJECTCLASSID = 17
AND k.op__grantsord & pk.decimalvalue <> 0
AND (g.OP__TIERNAME IS NOT NULL
OR u.OP__TIERNAME IS NOT NULL)

UNION ALL

SELECT CASE
WHEN g.OP__TIERNAME IS NOT NULL THEN 'Group'
WHEN u.OP__TIERNAME IS NOT NULL THEN 'User'
END AS GroupUser
, CASE
WHEN g.OP__TIERNAME IS NOT NULL THEN g.OP__TIERNAME
WHEN u.OP__TIERNAME IS NOT NULL THEN u.OP__TIERNAME
END AS Name
, pkg.Name AS KeyGroup
, ISNULL(f.OP__TIERNAME, '') + ' -- ' + ISNULL(fs.op__tiername,'') AS Item
, pk.Permission
, 'Denial' AS Setting
, pkg.SortOrder AS KeyGroupOrder
, pk.SortOrder
FROM T4W_KEYS k
JOIN T4W_FORMSECTIONS fs ON k.OP__OBJECTID = fs.OP__ID
JOIN T4W_FORMS f ON fs.OP__PARENTID = f.OP__ID
JOIN RM_PermissionKeyGroup pkg ON pkg.op__ObjectClassID = k.op__ObjectClassID
JOIN RM_PermissionKeys pk ON pkg.ID = pk.KeyGroupID
LEFT JOIN T4W_GROUPS g ON g.OP__ID = k.OP__OWNERID
LEFT JOIN T4W_USERS u ON u.OP__ID = k.OP__OWNERID
WHERE k.OP__OBJECTCLASSID = 17
AND k.op__denialsord & pk.decimalvalue <> 0
AND (g.OP__TIERNAME IS NOT NULL
OR u.OP__TIERNAME IS NOT NULL)

UNION ALL

SELECT CASE
WHEN g.OP__TIERNAME IS NOT NULL THEN 'Group'
WHEN u.OP__TIERNAME IS NOT NULL THEN 'User'
END AS GroupUser
, CASE
WHEN g.OP__TIERNAME IS NOT NULL THEN g.OP__TIERNAME
WHEN u.OP__TIERNAME IS NOT NULL THEN u.OP__TIERNAME
END AS Name
, pkg.Name AS KeyGroup
, fc.op__tiername AS Item
, pk.Permission
, 'Grant' AS Setting
, pkg.SortOrder AS KeyGroupOrder
, pk.SortOrder
FROM T4W_KEYS k
JOIN T4W_FORMCATS fc ON k.OP__OBJECTID = fc.OP__ID
JOIN RM_PermissionKeyGroup pkg ON pkg.op__ObjectClassID = k.op__ObjectClassID
JOIN RM_PermissionKeys pk ON pkg.ID = pk.KeyGroupID
LEFT JOIN T4W_GROUPS g ON g.OP__ID = k.OP__OWNERID
LEFT JOIN T4W_USERS u ON u.OP__ID = k.OP__OWNERID
WHERE k.OP__OBJECTCLASSID = 8
AND k.op__grantsord & pk.decimalvalue <> 0
AND (g.OP__TIERNAME IS NOT NULL
OR u.OP__TIERNAME IS NOT NULL)

UNION ALL

SELECT CASE
WHEN g.OP__TIERNAME IS NOT NULL THEN 'Group'
WHEN u.OP__TIERNAME IS NOT NULL THEN 'User'
END AS GroupUser
, CASE
WHEN g.OP__TIERNAME IS NOT NULL THEN g.OP__TIERNAME
WHEN u.OP__TIERNAME IS NOT NULL THEN u.OP__TIERNAME
END AS Name
, pkg.Name AS KeyGroup
, fc.op__tiername AS Item
, pk.Permission
, 'Denial' AS Setting
, pkg.SortOrder AS KeyGroupOrder
, pk.SortOrder
FROM T4W_KEYS k
JOIN T4W_FORMCATS fc ON k.OP__OBJECTID = fc.OP__ID
JOIN RM_PermissionKeyGroup pkg ON pkg.op__ObjectClassID = k.op__ObjectClassID
JOIN RM_PermissionKeys pk ON pkg.ID = pk.KeyGroupID
LEFT JOIN T4W_GROUPS g ON g.OP__ID = k.OP__OWNERID
LEFT JOIN T4W_USERS u ON u.OP__ID = k.OP__OWNERID
WHERE k.OP__OBJECTCLASSID = 8
AND k.op__denialsord & pk.decimalvalue <> 0
AND (g.OP__TIERNAME IS NOT NULL
OR u.OP__TIERNAME IS NOT NULL)

UNION ALL

SELECT CASE
WHEN g.OP__TIERNAME IS NOT NULL THEN 'Group'
WHEN u.OP__TIERNAME IS NOT NULL THEN 'User'
END AS GroupUser
, CASE
WHEN g.OP__TIERNAME IS NOT NULL THEN g.OP__TIERNAME
WHEN u.OP__TIERNAME IS NOT NULL THEN u.OP__TIERNAME
END AS Name
, pkg.Name AS KeyGroup
, f.op__tiername AS Item
, pk.Permission
, 'Grant' AS Setting
, pkg.SortOrder AS KeyGroupOrder
, pk.SortOrder
FROM T4W_KEYS k
JOIN T4W_FORMS f ON k.OP__OBJECTID = f.OP__ID
JOIN RM_PermissionKeyGroup pkg ON pkg.op__ObjectClassID = k.op__ObjectClassID
JOIN RM_PermissionKeys pk ON pkg.ID = pk.KeyGroupID
LEFT JOIN T4W_GROUPS g ON g.OP__ID = k.OP__OWNERID
LEFT JOIN T4W_USERS u ON u.OP__ID = k.OP__OWNERID
WHERE k.OP__OBJECTCLASSID = 9
AND k.op__grantsord & pk.decimalvalue <> 0
AND (g.OP__TIERNAME IS NOT NULL
OR u.OP__TIERNAME IS NOT NULL)

UNION ALL

SELECT CASE
WHEN g.OP__TIERNAME IS NOT NULL THEN 'Group'
WHEN u.OP__TIERNAME IS NOT NULL THEN 'User'
END AS GroupUser
, CASE
WHEN g.OP__TIERNAME IS NOT NULL THEN g.OP__TIERNAME
WHEN u.OP__TIERNAME IS NOT NULL THEN u.OP__TIERNAME
END AS Name
, pkg.Name AS KeyGroup
, f.op__tiername AS Item
, pk.Permission
, 'Denial' AS Setting
, pkg.SortOrder AS KeyGroupOrder
, pk.SortOrder
FROM T4W_KEYS k
JOIN T4W_FORMS f ON k.OP__OBJECTID = f.OP__ID
JOIN RM_PermissionKeyGroup pkg ON pkg.op__ObjectClassID = k.op__ObjectClassID
JOIN RM_PermissionKeys pk ON pkg.ID = pk.KeyGroupID
LEFT JOIN T4W_GROUPS g ON g.OP__ID = k.OP__OWNERID
LEFT JOIN T4W_USERS u ON u.OP__ID = k.OP__OWNERID
WHERE k.OP__OBJECTCLASSID = 9
AND k.op__denialsord & pk.decimalvalue <> 0
AND (g.OP__TIERNAME IS NOT NULL
OR u.OP__TIERNAME IS NOT NULL)

ORDER BY GroupUser
, Name
, KeyGroupOrder
, Item
, pk.SortOrder
, Setting DESC

Verifying user has permission

The catalyst to all this research was that we needed to be able to verify a user had permission to create a new document in our stored procedures. It’s probably possible to make the function CheckPermissionNewDoc more generic for any permission. However, the interdependency of the different permissions makes that possibility a bit cumbersome. This function specifically checks the “New Document” permission in:

Folders – All
Folders – By Cabinet
Folders – By Folder
Forms – All
Forms – By Category
Forms – By Form

There are a couple of caveats to this function. The function only checks the folders direct cabinet. It does not check permissions on a cabinet’s cabinet. The function only checks permission of the forms direct category. The parent categories of a nested form category are not checked.

/******************************************************************************
NOTES:
Must check denials before grants

Only checks cabinet of folder not the cabinets of the cabinet.

Checks are in rough order according to probability of being set

EXAMPLE:
DECLARE @Permission INT
SET @Permission = dbo.CheckPermissionNewDoc (125, 11418, 2625)
PRINT @Permission

HISTORY: See Source Control
******************************************************************************/
CREATE FUNCTION dbo.CheckPermissionNewDoc (
@TIERUser INT
, @FormID INT
, @FolderID INT
) RETURNS INT AS

BEGIN

DECLARE @DenialsCount INT
DECLARE @FormsGrantsCount INT
DECLARE @FolderGrantsCount INT

--Denial on Forms - All
SELECT @DenialsCount = COUNT(1)
FROM T4W_KEYS k
LEFT JOIN T4W_GROUPS g ON k.OP__OWNERID = g.OP__ID
LEFT JOIN T4W_MEMBERS m ON g.OP__ID = m.RSID
WHERE (k.OP__OWNERID = @TIERUser
OR m.LSID = @TIERUser)
AND k.OP__OBJECTCLASSID = 0 --classes
AND k.OP__OBJECTID = 9 -- forms
AND k.OP__DENIALSORD & 8388608 <> 0

IF @DenialsCount > 0
BEGIN
RETURN 1
END

--Denial on Forms - By Category
SELECT @DenialsCount = COUNT(1)
FROM T4W_KEYS k
LEFT JOIN T4W_GROUPS g ON k.OP__OWNERID = g.OP__ID
LEFT JOIN T4W_MEMBERS m ON g.OP__ID = m.RSID
WHERE (k.OP__OWNERID = @TIERUser
OR m.LSID = @TIERUser)
AND k.OP__OBJECTCLASSID = 8 --forms - by category
AND k.OP__OBJECTID = (
SELECT f.OP__PARENTID
FROM t4w_forms f
WHERE f.op__id = @FormID
)
AND k.OP__DENIALSORD & 8388608 <> 0

IF @DenialsCount > 0
BEGIN
RETURN 1
END

--Denial on Forms - By Form
SELECT @DenialsCount = COUNT(1)
FROM T4W_KEYS k
LEFT JOIN T4W_GROUPS g ON k.OP__OWNERID = g.OP__ID
LEFT JOIN T4W_MEMBERS m ON g.OP__ID = m.RSID
WHERE (k.OP__OWNERID = @TIERUser
OR m.LSID = @TIERUser)
AND k.OP__OBJECTCLASSID = 9 --forms - by form
AND k.OP__OBJECTID = @FormID
AND k.OP__DENIALSORD & 8388608 <> 0

IF @DenialsCount > 0
BEGIN
RETURN 1
END

--Denial on Folders - All
SELECT @DenialsCount = COUNT(1)
FROM T4W_KEYS k
LEFT JOIN T4W_GROUPS g ON k.OP__OWNERID = g.OP__ID
LEFT JOIN T4W_MEMBERS m ON g.OP__ID = m.RSID
WHERE (k.OP__OWNERID = @TIERUser
OR m.LSID = @TIERUser)
AND k.OP__OBJECTCLASSID = 0 --classes
AND k.OP__OBJECTID = 15 -- folders
AND k.OP__DENIALSORD & 4194304 <> 0

IF @DenialsCount > 0
BEGIN
RETURN 1
END

--Denial on Folders - By Cabinet
SELECT @DenialsCount = COUNT(1)
FROM T4W_KEYS k
LEFT JOIN T4W_GROUPS g ON k.OP__OWNERID = g.OP__ID
LEFT JOIN T4W_MEMBERS m ON g.OP__ID = m.RSID
WHERE (k.OP__OWNERID = @TIERUser
OR m.LSID = @TIERUser)
AND k.OP__OBJECTCLASSID = 6 --folders - by cabinet
AND k.OP__OBJECTID = (
SELECT f.OP__PARENTID
FROM t4w_folders f
WHERE f.op__id = @FolderID
)
AND k.OP__DENIALSORD & 4194304 <> 0

IF @DenialsCount > 0
BEGIN
RETURN 1
END

--Denial on Folders - By Folder
SELECT @DenialsCount = COUNT(1)
FROM T4W_KEYS k
LEFT JOIN T4W_GROUPS g ON k.OP__OWNERID = g.OP__ID
LEFT JOIN T4W_MEMBERS m ON g.OP__ID = m.RSID
WHERE (k.OP__OWNERID = @TIERUser
OR m.LSID = @TIERUser)
AND k.OP__OBJECTCLASSID = 15 --folders - by folder
AND k.OP__OBJECTID = @FolderID
AND k.OP__DENIALSORD & 4194304 <> 0

IF @DenialsCount > 0
BEGIN
RETURN 1
END

--Grant on Forms - All
SELECT @FormsGrantsCount = COUNT(1)
FROM T4W_KEYS k
LEFT JOIN T4W_GROUPS g ON k.OP__OWNERID = g.OP__ID
LEFT JOIN T4W_MEMBERS m ON g.OP__ID = m.RSID
WHERE (k.OP__OWNERID = @TIERUser
OR m.LSID = @TIERUser)
AND k.OP__OBJECTCLASSID = 0 --classes
AND k.OP__OBJECTID = 9 -- forms
AND k.OP__GRANTSORD & 8388608 <> 0

IF @FormsGrantsCount = 0
BEGIN
--Grant on Forms - By Category
SELECT @FormsGrantsCount = COUNT(1)
FROM T4W_KEYS k
LEFT JOIN T4W_GROUPS g ON k.OP__OWNERID = g.OP__ID
LEFT JOIN T4W_MEMBERS m ON g.OP__ID = m.RSID
WHERE (k.OP__OWNERID = @TIERUser
OR m.LSID = @TIERUser)
AND k.OP__OBJECTCLASSID = 8 --forms - by category
AND k.OP__OBJECTID = (
SELECT f.OP__PARENTID
FROM t4w_forms f
WHERE f.op__id = @FormID
)
AND k.OP__GRANTSORD & 8388608 <> 0

IF @FormsGrantsCount = 0
BEGIN
--Grant on Forms - By Form
SELECT @FormsGrantsCount = COUNT(1)
FROM T4W_KEYS k
LEFT JOIN T4W_GROUPS g ON k.OP__OWNERID = g.OP__ID
LEFT JOIN T4W_MEMBERS m ON g.OP__ID = m.RSID
WHERE (k.OP__OWNERID = @TIERUser
OR m.LSID = @TIERUser)
AND k.OP__OBJECTCLASSID = 9 --forms - by form
AND k.OP__OBJECTID = @FormID
AND k.OP__GRANTSORD & 8388608 <> 0

IF @FormsGrantsCount = 0
BEGIN
RETURN 1 --forms are not granted at any level
END
END
END

--Grant on Folders - All
SELECT @FolderGrantsCount = COUNT(1)
FROM T4W_KEYS k
LEFT JOIN T4W_GROUPS g ON k.OP__OWNERID = g.OP__ID
LEFT JOIN T4W_MEMBERS m ON g.OP__ID = m.RSID
WHERE (k.OP__OWNERID = @TIERUser
OR m.LSID = @TIERUser)
AND k.OP__OBJECTCLASSID = 0 --classes
AND k.OP__OBJECTID = 15 -- folders
AND k.OP__GRANTSORD & 4194304 <> 0

IF @FolderGrantsCount = 0
BEGIN
--Grant on Folders - By Cabinet
SELECT @FolderGrantsCount = COUNT(1)
FROM T4W_KEYS k
LEFT JOIN T4W_GROUPS g ON k.OP__OWNERID = g.OP__ID
LEFT JOIN T4W_MEMBERS m ON g.OP__ID = m.RSID
WHERE (k.OP__OWNERID = @TIERUser
OR m.LSID = @TIERUser)
AND k.OP__OBJECTCLASSID = 6 --folders - by cabinet
AND k.OP__OBJECTID = (
SELECT f.OP__PARENTID
FROM t4w_folders f
WHERE f.op__id = @FolderID
)
AND k.OP__GRANTSORD & 4194304 <> 0

IF @FolderGrantsCount = 0
BEGIN

--Grant on Folders - By Folder
SELECT @FolderGrantsCount = COUNT(1)
FROM T4W_KEYS k
LEFT JOIN T4W_GROUPS g ON k.OP__OWNERID = g.OP__ID
LEFT JOIN T4W_MEMBERS m ON g.OP__ID = m.RSID
WHERE (k.OP__OWNERID = @TIERUser
OR m.LSID = @TIERUser)
AND k.OP__OBJECTCLASSID = 15 --folders - by folder
AND k.OP__OBJECTID = @FolderID
AND k.OP__GRANTSORD & 4194304 <> 0

IF @FolderGrantsCount = 0
BEGIN
RETURN 1
END
END
END

RETURN 0 --success

END

Here is an example of what usp_NewDocumentTest5 might look like from my previous post Design Patterns for Creating New Documents in TIER

/******************************************************************************
NOTES:
Demonstrates the use of checking permissions

EXAMPLE:
usp_NewDocumentTest5 @ParentID = :TIER_DocID, @FolderID = :TIER_FolderID
, @UserID = :TIER_UserID

EXEC usp_NewDocumentTest5 @ParentID = 12916698, @UserID = 125

******************************************************************************/
CREATE PROCEDURE dbo.usp_NewDocumentTest5 (
@ParentID INT
, @FolderID INT
, @UserID INT
) AS

SET NOCOUNT ON -- Otherwise TIER wont see messages.

DECLARE @FormID INT = 11421 -- New Document Test Sub
, @NewDocID INT
, @ErrorID INT = 1

SET @ErrorID = dbo.CheckPermissionNewDoc (@UserID, @FormID, @FolderID)

IF @ErrorID <> 0
BEGIN
raiserror('You do not have permission to create document.',16,1)
return @ErrorID
END

EXEC @ErrorID = usp_GenerateNewID @ClassID = 7 --Document
, @NewDocID = @NewDocID OUTPUT

IF @ErrorID <> 0
BEGIN
raiserror('Doc ID creation failed.',16,1)
return @ErrorID
END

-- Ensure that both records are created or neither are
BEGIN TRANSACTION InsertDoc
BEGIN TRY

EXEC usp_CreateT4WDocument2 @NewDocID = @NewDocID
, @ParentID = @ParentID
, @FormID = @FormID
, @UserID = @UserID

INSERT FD__NEW_DOCUMENT_TEST_SUB (OP__DOCID
, OP__PARENTID
, OP__FOLDERID
, TestField1
, TestMemo1
) VALUES (
@NewDocID
, @ParentID
, @ParentID
, 'usp_NewDocumentTest4 - error checking'
, 'A test memo'
)

END TRY
BEGIN CATCH
DECLARE @ErrorMessage VARCHAR(100)
, @ErrorSeverity INT
, @ErrorState INT

SELECT @ErrorMessage = ERROR_MESSAGE()
, @ErrorSeverity = ERROR_SEVERITY()
, @ErrorState = ERROR_STATE()

RAISERROR (@ErrorMessage
, @ErrorSeverity
, @ErrorState
)

ROLLBACK TRANSACTION InsertDoc

RETURN 1

END CATCH

COMMIT TRANSACTION InsertDoc

RETURN

TIER

Finding Document #s Used More Than Once

0 Comments

Obviously a document # being used more than once is a serious data integrity issue. Here’s a little query using v_DocStandardKeys to identify if your facility has this problem.

1
2
3
4
5
6
7
8

SELECT sk.op__DocID
, sk.TableName
, COUNT(sk.op__DocID)
FROM v_DocStandardKeys sk
GROUP BY sk.op__DocID
, sk.TableName
HAVING COUNT(sk.op__DocID) > 1
ORDER BY sk.TableName

Design Patterns for Creating New Documents in TIER

1 Comments

Update 16 May 2012 There are new notes at the end of this post.

All TIER developers are familiar with creating a new document using a buttons baNewSubdocument action. Most are aware of being able to create new documents using a stored procedure. There are many reasons for wanting to creating documents using stored procedures. For example document cloning and advanced initialization beyond what is possible from a fields initialize property. Whats the best design pattern? How can we organize the code to be robust? I’ll attempt to show my own evolution through some examples. From the very simple to more and more complex. Any of which work so use your own judgement on which to adopt. TIERNewDocDesignPatterns.zip includes the test forms and following procedures.

Simple

I used this basic template when I first started with TIER. Not very complex and has no error checking but it works. The simplicity makes it easy to troubleshoot and fairly reliable. Some may argue against the use of Magic Numbers but I’ve found in my own experience that unless I’m going to reuse a value it is far easier to simply include a comment on a values meaning. There is less code and everything is in one place for troubleshooting. This is especially true with smaller procedures.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

/******************************************************************************
NOTES:
The most basic document creation procedure

EXAMPLE:
usp_NewDocumentTest1 @ParentID = :TIER_DocID, @UserID = :TIER_UserID

EXEC usp_NewDocumentTest1 @ParentID = 12916698, @UserID = 125

******************************************************************************/
CREATE PROCEDURE dbo.usp_NewDocumentTest1 (
@ParentID INT
, @UserID INT
) AS

DECLARE @NewDocID INT
DECLARE @UserName VARCHAR(60)

EXEC usp_GenerateNewID @ClassID = 7 --Document
, @NewDocID = @NewDocID OUTPUT

SELECT @UserName = OP__FULLNAME
FROM T4W_USERS
WHERE OP__ID = @UserID

INSERT T4W_DOCUMENTS (OP__ID
, OP__PARENTCLASSID
, OP__PARENTID
, OP__CREATIONDATETIME
, OP__CREATORID
, OP__FORMID
, OP__LASTSAVEDDATETIME
, OP__LASTSAVEDBY
, OP__LASTSAVEDBYID
, OP__STATUSORD
, OP__SIGNEDBY
, OP__COPIED
, OP__REVISION
, OP__TIERNAME
, OP__CREATORNAME
) VALUES (
@NewDocID
, 7 -- document
, @ParentID
, GETDATE()
, @UserID
, 11421
, GETDATE()
, @UserName
, @UserID
, 2 --saved
, NULL
, 'F'
, 0
, 'New Document Test Sub'
, @UserName
)

INSERT FD__NEW_DOCUMENT_TEST_SUB (OP__DOCID
, OP__PARENTID
, OP__FOLDERID
, TestField1
, TestMemo1
) VALUES (
@NewDocID
, @ParentID
, @ParentID
, 'usp_NewDocumentTest1 - Simple'
, 'A test memo'
)

Netsmart’s TP_CreateNewTierFormID

This is Netsmart’s procedure to standardize the t4w_documents creation and provide some basic error checking. TP_CreateNewTierDocumentID apears to be very similar but doesn’t include a call to TP_AUDIT. My biggest problem with this setup is that it’s creating documents based on the form name and not the form ID. This becomes a problem if a form name ever needs to change or if a developer accidentally creates two forms with the same name. Netsmart also moves the creation of a new document id into this procedure. As will be explained later when I add error checking and transactions this creates a problem.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

/******************************************************************************
NOTES:
Demonstrates the use of TP_CreateNewTierFormID

EXAMPLE:
usp_NewDocumentTest2 @ParentID = :TIER_DocID, @UserID = :TIER_UserID

EXEC usp_NewDocumentTest2 @ParentID = 12916698, @UserID = 125

******************************************************************************/
CREATE PROCEDURE dbo.usp_NewDocumentTest2 (
@ParentID INT
, @UserID INT
) AS

DECLARE @NewDocID INT

Exec TP_CreateNewTierFormID @TierUserID = @UserID
, @FormName = 'New Document Test Sub'
, @OP__ParentID = @ParentID
, @ParentClassType = 'TTierDocument'
, @ProcessName = 'usp_NewDocumentTest2'
, @ReturnDocID = @NewDocID Output

INSERT FD__NEW_DOCUMENT_TEST_SUB (OP__DOCID
, OP__PARENTID
, OP__FOLDERID
, TestField1
, TestMemo1
) VALUES (
@NewDocID
, @ParentID
, @ParentID
, 'usp_NewDocumentTest2 - TP_CreateNewTierFormID'
, 'A test memo'
)

usp_CreateT4WDocument

If we create our own stored procedure for creating t4w_documents then we can use form id instead of form name and add additional checks.

/******************************************************************************
NOTES:
Create a new record in T4W_DOCUMENTS. Meant to replace Netsmarts
TP_CreateNewTierDocumentID. We don't want to backout the docid creation in
case of error. This also includes more tests. Just pass the ParentClass instead
of making the procedure lookup it up every time. Minor speed increase.

******************************************************************************/
CREATE PROCEDURE [dbo].[usp_CreateT4WDocument2]
@NewDocID INT
, @ParentID INT
, @FormID INT
, @UserID INT
-- Can not default to blank because blank documents show no values in TIER
-- forms even if there are values in the FD_[Table].
, @Status SMALLINT = 2 -- 1 = Blank
-- 2 = Saved
-- 3 = Draft
-- 4 = Partial (signed)
-- 5 = Final (signed)
, @ParentClass INT = 7 -- 7 = Document
-- 15 = Folder
AS

DECLARE @CurrentDate DATETIME
, @UserName VARCHAR(60)
, @FormName VARCHAR(50)
, @ErrorMessage VARCHAR(100)
, @SignedBy VARCHAR(60)
, @ParentStatus INT

--Test values
IF @NewDocID IS NULL
BEGIN
RAISERROR('Error: Missing NewDocID', 16, 1)
RETURN 1
END

IF @ParentID IS NULL
BEGIN
RAISERROR('Error: Missing Missing ParentID', 16, 1)
RETURN 1
END

IF @FormID IS NULL
BEGIN
RAISERROR('Error: Missing FormID', 16, 1)
RETURN 1
END

IF @UserID IS NULL
BEGIN
RAISERROR('Error: Missing UserID', 16, 1)
RETURN 1
END

IF @Status IS NULL
BEGIN
RAISERROR('Error: Missing Status', 16, 1)
RETURN 1
END

IF @ParentClass IS NULL
BEGIN
RAISERROR('Error: Missing ParentClass', 16, 1)
RETURN 1
END

IF @ParentClass = 15
AND (SELECT OP__ID FROM T4W_FOLDERS WHERE OP__ID = @ParentID) IS NULL
BEGIN
SET @ErrorMessage = 'Error: Parent folder does not exist ('
+ CONVERT(VARCHAR,@ParentID) + ')'
RAISERROR(@ErrorMessage, 16, 1)
RETURN 1
END

IF @Status > 5 OR @Status < 1
BEGIN
SET @ErrorMessage = 'Incorrect status (' + CONVERT(VARCHAR,@Status) + ')'
RAISERROR(@ErrorMessage, 16, 1)
RETURN 1
END

IF @ParentClass = 7
BEGIN
SELECT @ParentStatus = OP__StatusOrd
FROM T4W_DOCUMENTS
WHERE OP__ID = @ParentID

--While technically possible to have a null status in reality none do so
-- safe to test existance this way.
IF @ParentStatus IS NULL
BEGIN
SET @ErrorMessage = 'Error: Parent document does not exist ('
+ CONVERT(VARCHAR,@ParentID) + ')'
RAISERROR(@ErrorMessage, 16, 1)
RETURN 1
END

IF @ParentStatus = 1
BEGIN
SET @ErrorMessage = 'Parent document is blank ('
+ CONVERT(VARCHAR,@ParentID) + ')'
RAISERROR(@ErrorMessage, 16, 1)
RETURN 1
END
END

--Get values
SELECT @FormName = OP__TIERNAME
FROM T4W_FORMS
WHERE OP__ID = @FormID

IF @FormName IS NULL
BEGIN
SET @ErrorMessage = 'Form does not exist (' + CONVERT(VARCHAR,@FormID) + ')'
RAISERROR(@ErrorMessage, 16, 1)
RETURN 1
END

SELECT @UserName = OP__FULLNAME
FROM T4W_USERS
WHERE OP__ID = @UserID

IF @UserName IS NULL
BEGIN
SET @ErrorMessage = 'User does not exist (' + CONVERT(VARCHAR,@UserID) + ')'
RAISERROR(@ErrorMessage, 16, 1)
RETURN 1
END

IF @Status = 4
OR @Status = 5
BEGIN
SET @SignedBy = @UserName
END

SET @CurrentDate = GETDATE()

INSERT T4W_DOCUMENTS (OP__ID
, OP__PARENTCLASSID
, OP__PARENTID
, OP__CREATIONDATETIME
, OP__CREATORID
, OP__FORMID
, OP__LASTSAVEDDATETIME
, OP__LASTSAVEDBY
, OP__LASTSAVEDBYID
, OP__STATUSORD
, OP__SIGNEDBY
, OP__COPIED
, OP__REVISION
, OP__TIERNAME
, OP__CREATORNAME
)
SELECT @NewDocID AS OP__ID
, @ParentClass AS OP__PARENTCLASSID
, @ParentID AS OP__PARENTID
, @CurrentDate AS OP__CREATIONDATETIME
, @UserID AS CREATORID
, @FormID AS OP__FORMID
, @CurrentDate AS OP__LASTSAVEDDATETIME
, @UserName AS OP__LASTSAVEDBY
, @UserID AS OP__LASTSAVEDBYID
, @Status AS OP__STATUSORD
, @SignedBy AS OP__SIGNEDBY
, 'F' AS OP__COPIED
, 0 AS OP__REVISION
, @FormName AS OP__TIERNAME
, @UserName AS OP__CREATORNAME

IF @@ERROR <> 0
BEGIN
DECLARE @ErrorSeverity INT
, @ErrorState INT

SELECT @ErrorMessage = ERROR_MESSAGE()
, @ErrorSeverity = ERROR_SEVERITY()
, @ErrorState = ERROR_STATE()

RAISERROR (@ErrorMessage
, @ErrorSeverity
, @ErrorState
)

END

RETURN @@ERROR --If successsful will return 0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

/******************************************************************************
NOTES:
Demonstrates the use of usp_CreateT4WDocument

EXAMPLE:
usp_NewDocumentTest3 @ParentID = :TIER_DocID, @UserID = :TIER_UserID

EXEC usp_NewDocumentTest3 @ParentID = 12916698, @UserID = 125

******************************************************************************/
CREATE PROCEDURE dbo.usp_NewDocumentTest3 (
@ParentID INT
, @UserID INT
) AS

DECLARE @NewDocID INT

EXEC usp_GenerateNewID @ClassID = 7 --Document
, @NewDocID = @NewDocID OUTPUT

EXEC usp_CreateT4WDocument2 @NewDocID = @NewDocID
, @ParentID = @ParentID
, @FormID = 11421 -- New Document Test Sub
, @UserID = @UserID

INSERT FD__NEW_DOCUMENT_TEST_SUB (OP__DOCID
, OP__PARENTID
, OP__FOLDERID
, TestField1
, TestMemo1
) VALUES (
@NewDocID
, @ParentID
, @ParentID
, 'usp_NewDocumentTest3 - usp_CreateT4WDocument'
, 'A test memo'
)

Error Checking and Transactions

So far I’ve left out error checking. Which ever approach you choose should include some basic checks and wrap the t4w_document and fd__[table] inserts into a transaction. No point in having one inserted and not the other. The developers where I currently work decided to exclude the usp_GenerateNewID call from the transaction. Since every table uses that key for document creation we felt it was too dangerous to roll it back or put a temporary lock on it. It’s safer to just “kill” the id by not using it when there is an error. I’ve used a TRY CATCH block here so those using a version of SQL Server before 2005 will need to rework it.

Thoroughly test this. Verify that errors are thrown all the way to the TIER interface. Do the value checks of usp_CreateT4WDocument work? What happens when the insert statements for t4w_documents and fd__[table] fail? For the value checks simply calling the procedure with an invalid user id will work. For testing errors of the inserts I found temporarily adjusting them to have an additional none null field gave a nice error.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

/******************************************************************************
NOTES:
Demonstrates the use of usp_CreateT4WDocument with error checkiing

EXAMPLE:
usp_NewDocumentTest4 @ParentID = :TIER_DocID, @UserID = :TIER_UserID

EXEC usp_NewDocumentTest4 @ParentID = 12916698, @UserID = 125

EXEC usp_NewDocumentTest4 @ParentID = 12916698, @UserID = 99999

******************************************************************************/
CREATE PROCEDURE dbo.usp_NewDocumentTest4 (
@ParentID INT
, @UserID INT
) AS

SET NOCOUNT ON -- Otherwise TIER wont see messages.

DECLARE @NewDocID INT
, @ErrorID INT

EXEC @ErrorID = usp_GenerateNewID @ClassID = 7 --Document
, @NewDocID = @NewDocID OUTPUT

IF @ErrorID <> 0
BEGIN
raiserror('Doc ID creation failed.',16,1)
return @ErrorID
END

-- Ensure that both records are created or neither are
BEGIN TRANSACTION InsertDoc
BEGIN TRY

EXEC usp_CreateT4WDocument2 @NewDocID = @NewDocID
, @ParentID = @ParentID
, @FormID = 11421 -- New Document Test Sub
, @UserID = @UserID

INSERT FD__NEW_DOCUMENT_TEST_SUB (OP__DOCID
, OP__PARENTID
, OP__FOLDERID
, TestField1
, TestMemo1
) VALUES (
@NewDocID
, @ParentID
, @ParentID
, 'usp_NewDocumentTest4 - error checking'
, 'A test memo'
)

END TRY
BEGIN CATCH
DECLARE @ErrorMessage VARCHAR(100)
, @ErrorSeverity INT
, @ErrorState INT

SELECT @ErrorMessage = ERROR_MESSAGE()
, @ErrorSeverity = ERROR_SEVERITY()
, @ErrorState = ERROR_STATE()

RAISERROR (@ErrorMessage
, @ErrorSeverity
, @ErrorState
)

ROLLBACK TRANSACTION InsertDoc

RETURN 1

END CATCH

COMMIT TRANSACTION InsertDoc

RETURN

Update 16 May 2012

Demystifying TIER Permissions continues this discussion and adds permissions.

This original post forgot to insert the created record into the t4w_audit table. Here is a new example of usp_CreateT4WDocument2 which does include the insert.

TIER

op__ParentID Audit and View v_DocStandardKeys

3 Comments

TIERs design stores the op__ParentID in two places. t4w_documents and the corresponding FD__[Table]. I understand why they did it as linking to the parent document from either table is often convenient. If op__ParentID only resided in one of the two places a lot of code would be that much more complex. However, this breaks the DRY principal. It’s possible for the data to get out of sync. One possible culprit is poorly written stored procedures that create documents.

In Merging Client Folders in TIER I showed the view v_DocClientKey. I recently had to audit our op__ParentIDs so I thought to expand that view. The new view v_DocStandardKeys allows us to find op__DocID, op__ParentID, op__FolderID, ClientKey, AdmissionKey, and Table Name from every FD__[Table] based on any document ID. As you can imagine this has numerous uses and simplifies a lot of code. One concern is that this view isn’t dynamic. It needs periodic up keep as tables are added to the database. It might be possible to create a view that was dynamic or even a job to run nightly to update this view but those are exercises for another day.

/******************************************************************************
NOTES:
This could eventually be expanded to replace
some of the purpose of lt__forms. Though lt__forms has a few other purposes so
perhaps not.

WARNING
This view needs periodic upkeep. The following select statement will create the
code for this view. just run it, copy the output, and then adjust the first line.

SELECT 'UNION ALL SELECT op__DocID ' +
--set to -1 if no column exists so that we can tell the difference between
--a table not having a column and a column being null
CASE
WHEN c2.id IS NOT NULL THEN ', op__ParentID'
ELSE ', -1 AS op__ParentID'
END +
CASE
WHEN c3.id IS NOT NULL THEN ', op__FolderID'
ELSE ', -1 AS op__FolderID'
END +
CASE
WHEN c4.id IS NOT NULL THEN ', ClientKey'
ELSE ', -1 AS ClientKey'
END +
CASE
WHEN c5.id IS NOT NULL THEN ', AdmissionKey'
ELSE ', -1 AS AdmissionKey'
END +
', ''' + o.name + ''' AS TableName'
+ ' FROM [' + o.name + '] '
FROM sysobjects o WITH(NOLOCK)
JOIN syscolumns c ON c.id = o.id
AND c.name = 'op__DocID' -- "FD__" type tables
LEFT JOIN syscolumns c2 ON c2.id = o.id
AND c2.name = 'op__ParentID'
LEFT JOIN syscolumns c3 ON c3.id = o.id
AND c3.name = 'op__FolderID'
LEFT JOIN syscolumns c4 ON c4.id = o.id
AND c4.name = 'ClientKey'
LEFT JOIN syscolumns c5 ON c5.id = o.id
AND c5.name = 'AdmissionKey'
WHERE o.type = 'U'
AND o.name NOT LIKE 'tlv_%'
AND o.name NOT LIKE 'x%'
AND o.name NOT LIKE 'TMP_%'
AND o.name NOT LIKE 'temp_%'
ORDER BY o.name

EXAMPLE:
SELECT *
FROM v_DocStandardKeys

HISTORY: See Source Control
******************************************************************************/
ALTER VIEW [dbo].[v_DocStandardKeys] AS

SELECT op__DocID , op__ParentID, op__FolderID, ClientKey, AdmissionKey, 'FD__ADMISSION_UR_CRITERIA' AS TableName FROM [FD__ADMISSION_UR_CRITERIA]
UNION ALL SELECT op__DocID , op__ParentID, op__FolderID, ClientKey, AdmissionKey, 'FD__ADMITDOC' AS TableName FROM [FD__ADMITDOC]
UNION ALL SELECT op__DocID , op__ParentID, op__FolderID, ClientKey, -1 AS AdmissionKey, 'FD__Adolescent_Anger_Rating' AS TableName FROM [FD__Adolescent_Anger_Rating]
UNION ALL SELECT op__DocID , op__ParentID, op__FolderID, ClientKey, AdmissionKey, 'FD__ADULT_Discharge_Criteria' AS TableName FROM [FD__ADULT_Discharge_Criteria]
UNION ALL SELECT op__DocID , op__ParentID, op__FolderID, ClientKey, AdmissionKey, 'FD__ADULT_MH_ADMISSION_CRITERI' AS TableName FROM [FD__ADULT_MH_ADMISSION_CRITERI]
UNION ALL SELECT op__DocID , op__ParentID, op__FolderID, ClientKey, AdmissionKey, 'FD__ADULT_MH_CSC' AS TableName FROM [FD__ADULT_MH_CSC]
UNION ALL SELECT op__DocID , op__ParentID, op__FolderID, ClientKey, AdmissionKey, 'FD__ADULT_SA_ADMISSION_CRITERI' AS TableName FROM [FD__ADULT_SA_ADMISSION_CRITERI]
UNION ALL SELECT op__DocID , op__ParentID, op__FolderID, ClientKey, AdmissionKey, 'FD__ADULT_SA_CSC' AS TableName FROM [FD__ADULT_SA_CSC]
UNION ALL SELECT op__DocID , op__ParentID, op__FolderID, ClientKey, AdmissionKey, 'FD__ADULT_SA_Discharge' AS TableName FROM [FD__ADULT_SA_Discharge]
UNION ALL SELECT op__DocID , op__ParentID, op__FolderID, ClientKey, AdmissionKey, 'FD__AdvancedBehavioralDirectives' AS TableName FROM [FD__AdvancedBehavioralDirectives]
UNION ALL SELECT op__DocID , op__ParentID, op__FolderID, ClientKey, -1 AS AdmissionKey, 'FD__ADVERSE_DRUG_REACTION' AS TableName FROM [FD__ADVERSE_DRUG_REACTION]
UNION ALL SELECT op__DocID , op__ParentID, op__FolderID, -1 AS ClientKey, -1 AS AdmissionKey, 'FD__AFFILIATE_CONTACT' AS TableName FROM [FD__AFFILIATE_CONTACT]
UNION ALL SELECT op__DocID , op__ParentID, op__FolderID, ClientKey, AdmissionKey, 'FD__AGENCY_ADMISSION' AS TableName FROM [FD__AGENCY_ADMISSION]
-- ...
-- Continues on but you get the point.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

--report of which tables don't have matching op__ParentIDs
--max(d.OP__TIERNAME) is used because some documents have a dynamic
--name and we only wanted to return 1 row per form.
select d.OP__FORMID
, max(d.OP__TIERNAME)
, max(d.OP__CREATIONDATETIME)
, COUNT(d.op__id)
from T4W_DOCUMENTS d
join v_docstandardkeys ds on d.OP__ID = ds.op__docid
where d.OP__PARENTID <> ds.op__parentid
and ds.op__parentid <> -1
or (ds.op__parentid IS NULL
AND d.op__parentid IS NOT NULL)
or (ds.op__parentid IS NOT NULL
AND d.op__parentid IS NULL)
group by d.OP__FORMID
order by max(d.OP__TIERNAME)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

--Details of which tables don't have matching op__ParentIDs
select d.OP__ID
, d.OP__PARENTID AS DocsParentID
, ds.op__parentid AS FDParentID
, d.OP__FORMID
, d.OP__TIERNAME
, ds.TableName
, d.OP__CREATIONDATETIME
from T4W_DOCUMENTS d
join v_docstandardkeys ds on d.OP__ID = ds.op__docid
where d.OP__PARENTID <> ds.op__parentid
and ds.op__parentid <> -1
or (ds.op__parentid IS NULL
AND d.op__parentid IS NOT NULL)
or (ds.op__parentid IS NOT NULL
AND d.op__parentid IS NULL)
order by ds.TableName
, d.OP__CREATIONDATETIME DESC

TIER

Merging Client Folders in TIER

2 Comments

It happens from time to time that a duplicate client chart will be created. The duplication wont get caught for a while and now your stuck with two incomplete charts. Your first reaction might be to try to merge the client charts together. Don’t do it! The risk of creating data integrity issues is far too great. Even with the best of care it’s highly likely that some part of the chart will not be merged correctly. The recommended way to handle this situation:

Make searching for similarly named clients the first step in client chart creation. Also searching on things like payer and or Medicaid IDs is helpful. This is easier if chart creation is limited to a few well trained staff.
Create a duplicates cabinet and lock down permissions so that charts can’t be edited if the client is in the cabinet.
Restrict your console searches to exclude the duplicate cabinet from everyone except departments like records.
Force the users to redo their documentation in the correct chart. It might be possible to allow them to open both charts so they can copy and paste. It’s your call on how that process will work.
Get everyone affected to chastise access. See step 1. Passing out tar and feathers is great for morale.

While this is less than ideal it’s safe and as long as the duplicate clients are found quickly not too much headache. You still want to merge client folders? I’ve heard that Sequest has a few merge scripts ready to go. I’ve not looked at them but if I were to do a merge I’d start there and see how they worked. If I was compelled to attempt a merge here’s how I’d do it:

Don’t do it! See above. Oh all right go ahead but you’ve been warned.
Create a duplicates cabinet and lock down permissions so that charts can’t be edited if the client is in the cabinet.
Restrict your console searches to exclude the duplicate cabinet from everyone except departments like records. While we will be merging the charts these steps allow us to leave the duplicated face sheet and folder in the system as a record that there was a duplicate.

We need to find all the id’s that we will be merging. These include ClientKey, Face Sheet Document ID, Cabinet, Folder, Admission Key(s), and possibly other ids. You will have to know your system and how things are stored and linked for each individual document / fd table. Our admissions tables are a little different from the standard workflow so you will have to adjust for your own admissions tables. But here’s a quick script which should give you most of the important id’s to be concerned about. Write these down somewhere.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

DECLARE @MergeFrom INT = 2
, @MergeTo INT = 1

select c.ClientKey
, c.OP__DOCID
, c.OP__PARENTID
, c.OP__FOLDERID
, fld.OP__PARENTID AS Cabinet
from FD__CLIENTS c
JOIN T4W_FOLDERS fld ON fld.OP__ID=c.OP__FOLDERID
where c.ClientKey in (@MergeFrom, @MergeTo)

select aa.ClientKey
, aa.OP__DOCID
, aa.AdmissionKey
, aa.OP__PARENTID
, aa.OP__FOLDERID
from FD__AGENCY_ADMISSION aa
where aa.ClientKey in (@MergeFrom, @MergeTo)
order by aa.ClientKey

Create a view so you can find all documents that contain a ClientKey. It would be possible to create a stored procedure that dynamically did our search for us but we’ve found this view to be so helpful that I recommended it. For example on a document id search we left join this view to t4w_documents so we can display a clients name.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

/*****************************************************************************
NOTES:
This could eventually be expanded to include things like table name, form_id
and replace
some of the purpose of lt__forms. Though lt__forms has a few other purposes so
perhaps not.

WARNING
This view needs periodic upkeep. The following select statement will create
the code for this view. just run it, copy the output, and then adjust the
first line.

select ' UNION ALL select op__docid, clientkey from [' + o.name + '] '
from sysobjects o with(nolock)
join syscolumns c on c.id = o.id
and c.name = 'clientkey'
join syscolumns c2 on c2.id = o.id
and c2.name = 'op__docid'
where o.type = 'U'
and o.name not like 'tlv_%'
and o.name not like 'x%'
and o.name not like 'TMP_%'
order by o.name
*****************************************************************************/
CREATE VIEW [dbo].[v_DocClientKey] AS

select op__docid, clientkey from [AMS_GRPProgressNote]
UNION ALL select op__docid, clientkey from [FD__ADULT_SA_CSC]
UNION ALL select op__docid, clientkey from [FD__ADULT_SA_Discharge]
UNION ALL select op__docid, clientkey from [FD__AdvancedBehavioralDirectives]
UNION ALL select op__docid, clientkey from [FD__ADVERSE_DRUG_REACTION]
UNION ALL select op__docid, clientkey from [FD__AGENCY_ADMISSION]
UNION ALL select op__docid, clientkey from [FD__AIMS_ASSESSMENT]
UNION ALL select op__docid, clientkey from [FD__ALCOHOLSCREEN]
UNION ALL select op__docid, clientkey from [FD__ALERTS]
UNION ALL select op__docid, clientkey from [FD__ALLEGEDABUSEREPORT]
UNION ALL select op__docid, clientkey from [FD__ALLERGIES]
-- ...
-- Continues on but you get the point.

Then run the following to find every document that contains the ClientKey

1
2
3
4
5
6
7
8
9
10
11

DECLARE @MergeFrom INT = 1

select dc.clientkey
, dc.op__docid
, d.OP__PARENTID
, d.OP__FORMID
, d.OP__TIERNAME
from v_DocClientKey dc
join T4W_DOCUMENTS d on dc.op__docid = d.OP__ID
where dc.ClientKey = @MergeFrom
order by d.OP__TIERNAME

Pick a form at a time to inspect for merging. Which IDs are stored? Are they still in use? Does op__parentid = op__folderid or are either null? Check all field names/values in case there is something strange going on. We had a developer that was creating a ParentID field instead of using the built in op__ParentID field. We’ve since properly tared and feathered him. For example:
1
2
3
4
5
6
7
DECLARE @MergeFrom INT = 2

SELECT *
FROM FD__GENERALNOTE gn
WHERE gn.ClientKey = @MergeFrom
--sometimes what is stored today isn't what we were doing in the past.
ORDER BY gn.OP__DOCID DESC

Then do the merge.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

DECLARE @ParentID int = 999
, @FolderID int = 999
, @AdmissionKey int = 999
, @MergeFrom int = 2
, @MergeTo INT = 1

UPDATE T4W_DOCUMENTS
SET OP__PARENTID = @ParentID
FROM T4W_DOCUMENTS d
JOIN FD__GENERALNOTE gn ON d.OP__ID = gn.OP__DOCID
WHERE gn.ClientKey = @MergeFrom

UPDATE FD__GENERALNOTE
SET op__parentid = @ParentID
, op__folderid = @FolderID
, AdmissionKey = @AdmissionKey
, ClientKey = @MergeTo
WHERE ClientKey = @MergeFrom

Check the new chart and verify that the documents are accessible.
Rinse repeat. It’s possible to create a script that would iterate over the tables and run dynamic sql but with out a table by table inspection it would be hard to trust it. You might be able to combine such a script with common cases like where folder id equals parent id and where folder id is the folder id. I’m sure you can see how it might get a little complex.
As a finale check do a full database search of the original id’s you inspected for. IDs like folder id and admission key. Creating a full database search is a post for another day. If you want to do this now Google can help with some ideas.

That’s the gist of it. I haven’t attempted this so I might have left out a minor step or written one slightly off but it’s the bases of what I’d do. Definitely store your scripts in case you have to do this again. Also I’d keep a copy for each migration so you have a record of what you did and when. One possibility is to limit the merge to only a few select documents like progress notes.

TIER

Django 1.3 Form API ModelForm Example

15 Comments

I’m in the midst of trying to learn Django. The documentation is usually very good but it fell flat when putting together a working example that used the Form API especially with ModelForm. Searching “Django Form API example” was far too broad and the tutorials I could find all seemed out of date or at least not in line with the documentation. After stumbling upon Advanced Django Forms Usage and a forum answer to my question I was able to piece together a working example. This is an extreme over simplification and is meant only to demonstrate the Form API using ModelForm. Note that there is an extra model in my example as I had originally hoped to include formsets. I’ve since decided to keep this example simpler. I may revisit this example to include them at a later date.

WARNING! I am not a Django developer. I don’t know what I’m doing and probably got somethings wrong. This is the best way I could see to do it based on my reading. That said this is fully working code which is more than can be said about the code snippets I could find.

examplesite.zip should contain all the code.

forms.py

1
2
3
4
5
6
7
8
9
10

from django.forms import ModelForm
from phonebook.models import Contact, PhoneNo

class ContactForm(ModelForm):
class Meta:
model = Contact

class PhoneNoForm(ModelForm):
class Meta:
model = PhoneNo

models.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

from django.db import models

class Contact(models.Model):
name = models.CharField(max_length=80)

def __unicode__(self):
return self.name

class PhoneNo(models.Model):
contact = models.ForeignKey(Contact)
phone_no = models.CharField(max_length=20)
phone_type = models.CharField(max_length=10)

def __unicode__(self):
return self.phone_no

urls.py

1
2
3
4
5
6
7
8
9
10

from django.conf.urls.defaults import patterns, include, url

urlpatterns = patterns('',
url(r'^contacts/$', 'phonebook.views.contacts'),
url(r'^contact_add/$', 'phonebook.views.contact_add'),
url(r'^contact_edit/(?P<contact_id>\d+)/$',
'phonebook.views.contact_edit'),
url(r'^contact_delete/(?P<contact_id>\d+)/$',
'phonebook.views.contact_delete'),
)

views.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

from django.shortcuts import render_to_response, redirect, get_object_or_404
from django.template import RequestContext

from phonebook.models import Contact, PhoneNo
from phonebook.forms import ContactForm, PhoneNoForm

def contacts(request):
latest_contact_list = Contact.objects.all().order_by('name')

return render_to_response('phonebook/contacts.html',
{'latest_contact_list': latest_contact_list,})

def contact_add(request):
# sticks in a POST or renders empty form
form = ContactForm(request.POST or None)
if form.is_valid():
cmodel = form.save()
#This is where you might chooose to do stuff.
#cmodel.name = 'test1'
cmodel.save()
return redirect(contacts)

return render_to_response('phonebook/contact_add.html',
{'contact_form': form},
context_instance=RequestContext(request))

def contact_edit(request, contact_id):
contact = get_object_or_404(Contact, pk=contact_id)
form = ContactForm(request.POST or None, instance=contact)
if form.is_valid():
contact = form.save()
#this is where you might choose to do stuff.
#contact.name = 'test'
contact.save()
return redirect(contacts)

return render_to_response('phonebook/contact_edit.html',
{'contact_form': form,
'contact_id': contact_id},
context_instance=RequestContext(request))

def contact_delete(request, contact_id):
c = Contact.objects.get(pk=contact_id).delete()

return redirect(contacts)

contact_add.html

1
2
3
4
5
6
7
8
9
10
11

</div>
<h1>Add Contact</h1>
{% if error_message %}<p><strong>{{ error_message }}</strong></p>{% endif %}

<form action="/phonebook/contact_add/" method="post">
{% csrf_token %}
<table>
{{ contact_form.as_table }}
</table>
<input type="submit" value="Save" />
</form>

contact_edit.html

1
2
3
4
5
6
7
8
9
10
11

</div>
<h1>Edit Contact</h1>
{% if error_message %}<p><strong>{{ error_message }}</strong></p>{% endif %}

<form action="/phonebook/contact_edit/{{ contact_id }}/" method="post">
{% csrf_token %}
<table>
{{ contact_form.as_table }}
</table>
<input type="submit" value="Save" />
</form>

contacts.html

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

</div>
<h1>Contacts</h1>

<a href="/phonebook/contact_add/">+ Add Contact</a>
{% if latest_contact_list %}
<table border=1>
<tr>
<th>Name</th>
<th> </th>
</tr>
{% for contact in latest_contact_list %}
<tr>
<td>
<a href="/phonebook/contact_edit/{{ contact.id }}/">
{{ contact.name }}
</a>
</td>
<td>
<a href="/phonebook/contact_edit/{{ contact.id }}/">edit</a>
<a href="/phonebook/contact_delete/{{ contact.id }}/">delete</a>
</td>
</tr>
{% endfor %}
</table>

{% else %}
<p>No contacts created.</p>
{% endif %}

Django

Crystal Reports Launch Speed

1 Comments

This issue relates to report load times in Crystal Reports. The fix was tested in TIER with Crystal XI, but applies to anyone running crystal reports. I would recommend any shop to test actual load speeds for issues. There just might be a surprise waiting for you.

I recently coded a report and noticed that it was taking an inordinately long time to launch. From the time the user asked TIER to run the report, it took an average of 1 minute 45 seconds before it prompted for parameters. For the visually inclined who are using TIER the time between these two screens:

I created some test reports in order to verify what exactly was taking so long.

Name	File Size	Notes
Report 01.rpt	45 KB	Minimal report, basic select, 1 parameter
Report 02.rpt	50 KB	1 sub report
Report 03.rpt	71 KB	5 sub reports
Report 04.rpt	50 KB	On demand link of sub report
Report 05.rpt	102 KB	1 50 KB image included in file
Report 06.rpt	153 KB	2 images
Report 07.rpt	205 KB	3 images
Report 08.rpt	307 KB	5 images
Report 09.rpt	411 KB	7 images
Report 10.rpt	513 KB	9 images
Report 11.rpt	1026 KB	11 images
Report 12.rpt	85 KB	16 bit image
Report 13.rpt	252 KB	30 sub reports

Pre Testing

I contacted Sequest support and they were able to help me troubleshoot some basics. For instance, smaller .rpt files were loading much faster and when we moved the offending .rpt local we were able to launch it in 10 seconds. These changes lead me to believe that it had something to do with file size on the network and so I began some further testing.

Test 1

I moved all my test reports to my local c:\ drive. Setup TIER to launch them. I then timed how long it took from launch to parameter prompt. Note that these times aren’t very exact as I was testing them by hand with a stop watch. In order to test raw file size I created a test logo of 50 KB.

Name	File Size	Notes	Time in Seconds
Report 01.rpt	45 KB	Minimal report, basic select, 1 parameter	1.3
Report 03.rpt	71 KB	5 sub reports	2.2
Report 05.rpt	102 KB	1 50 KB image included in file	1.5
Report 06.rpt	153 KB	2 images	1.6
Report 11.rpt	1026 KB	11 images	1.5
Report 13.rpt	252 KB	30 sub reports	8.4

I found that file size had almost no effect but the number of sub reports certainly did!

Test 2

For the second test, I ran the same test as Test 1 but with the .rpt files on our report server.

Name	File Size	Notes	Time in Seconds
Report 01.rpt	45 KB	Minimal report, basic select, 1 parameter	1.6
Report 11.rpt	1026 KB	11 images	3.4
Report 13.rpt	252 KB	30 sub reports	113

This time file size a had a minimal affect but sub reports were drastically slower. 113 seconds is not a typo!

Test 3

Again, I ran the same test as Test 1 but with the .rpt files on a new server IT was in the middle of setting up but not yet in production.

Name	File Size	Notes	Time in Seconds
Report 01.rpt	45 KB	Minimal report, basic select, 1 parameter	1.2
Report 11.rpt	1026 KB	11 images	1.7
Report 13.rpt	252 KB	30 sub reports	11

Solution

After showing my supervisor these results we decided to throw hardware at the problem and move the reports to a new server.

Old Server: Intel Xeon 3.00Ghz CPU, 2 GB of RAM, and Windows Server 2003. This server was also running Exchange, Active Directory, and our main file share.

New Server: 2 Intel Xeon 3.06GHz CPUs, 4 GB of RAM, and Windows Server 2008 (32-bit). With nothing else running.

It would take more testing to say if our bottle neck was CPUs, RAM, or Exchange/Active Directory, but since we had a working solution we stopped testing at this point.

Test 4

Finally, running the tests on our new report server. I also created a few new tests in order to have a scale of time per sub report count.

Name	File Size	Notes	Time in Seconds
Report 01.rpt	45 KB	Minimal report, basic select, 1 parameter	.9
Report 11.rpt	1026 KB	11 images	1.3
Report 13.rpt	252 KB	30 sub reports	8.3
Report 14.rpt	210 KB	25 sub reports	7.4
Report 15.rpt	183 KB	20 sub reports	4.6
Report 16.rpt	149 KB	15 sub reports	3.7
Report 17.rpt	114 KB	10 sub reports	3.0
Report 18.rpt	73 KB	5 sub reports	1.7

Summary

File size of the .rpt file had a minimal affect on load time.
The number of sub reports significantly affects load time.
Check your server setup! A very simple restructuring made a massive difference for us.

I did a little Googling of this problem. Readers might find the following interesting as well.

Crystal Reports Performance

Ten tips for Crystal Reports

CrystalSpeedTest.zip contains my test reports. Note that these will only be helpful to you if you have TIER. Everyone else will have to create their own. Not that it is very difficult. Also note that since the query is to select from t4w_documents they aren’t intended to be run (due to the long run time). They were created to demonstrate load times.

Crystal Reports, TIER

Peaches in season!

0 Comments

Peaches are in season! I had a couple of wonderfully tasty specimens this weekend.

Form Validation Clues

0 Comments

Preface

Generally Form/document validation usually works in 3 ways.

Application Layer Validation
1. Data is entered
2. Client clicks save
3. Application does validation
4. Any errors are displayed
5. User clicks OK
6. User corrects form
7. Client clicks save
8. Data saved
Database Validation
1. Data is entered
2. Client clicks save
3. Database does validation
4. Any errors are hopefully displayed. Application has to be coded to catch / display errors.
5. User clicks OK
6. User corrects form
7. Client clicks save
8. Data saved
In Line Validation
1. Data is entered
2. Warning messages are displayed in line with data and are removed as data is corrected.
3. User corrects data as it is entered
4. Client clicks save
5. Data saved

Usually all three of these validation methods are used to varying degrees in applications. But as developer time grows short on a project in line validation often gets overlooked. This is a disservice to our users. In line validation results in a shorter data entry cycle and helps to reign in user frustrations with data entry. If your still not convinced of its importance try a google search on the subject. I’m certain others have already written about this and articulated the arguments better than I could.

How this relates to TIER

TIER does application layer and database validation very well. Unfortunately in line validation is not something that comes free with the application layer validation. In practice I’ve not noticed many sites trying to use it. It’s not that hard to do but does require more work. With the benefit of limiting user frustration at least basic in line validation like required fields should be worked into every form and be a developers standard in form design.

The validation notices on the included example form are implemented by using a TTierEdit control with validation and HideOnValidation set to true. It’s important to note these are informative only and do not force any validation on their own. Note that I set the font size of the asterisk to 18. At the same size of the text it looked disproportionately small to me.

In Line Validation Example Form.zip

Here are some screen shots of the included example form. Format to taste.

Required fields with no data.

Required fields with data.

Alternate validation being triggered.

TIER

Cascading Signatures

0 Comments

This entry concerns TIER 5.7

I’m certain we all have those certain forms that seem to have too many sub documents that need signatures. Users get frustrated having to sign each individual sub document if there are too many. I’ll explain how to set up a form to cascade a parent documents signature down to its sub documents. This allows the users to sign all documents by entering one signature! A huge time saver on large documents. Note that this process is not Sequest approved as it requires a trigger on t4w_signatures. However, if you know what you’re doing and stay aware of what is happening on TIER updates things should be OK. The basic steps are:

Create your form and sub form as normal.
Setup a signature section on the parent form and sub form.
Set permissions so that users can sign the parent form but not the sub form. This is so users will only be prompted to save the sub documents not sign them.
Create an insert trigger on t4w_signatures. This trigger will fire when a signature is added to the parent document and then copy that signature to each sub document.
Create an update trigger on the sub forms FD__[Table] that tests for signatures and doesn’t do the update if any exists.

It’s important to keep in mind the order of execution the TIER executable is running when signing a form. First, any updates to the FD__[Table] are made. Then op__status is updated in t4w_documents. Lastly the record in t4w_Signatures is inserted. This order has a direct effect on where code can be inserted and how that code executes.

In order to keep the t4w_signatures trigger as fast as possible the very first thing is a test for the section. This prevents any extra processing if the document isn’t the parent we want to cascade.

Step 5 is necessary to prevent users from editing the sub documents immediately after signature. Unfortunately for this exercise TIER seems to cache a documents status and signature information. This means that if step 5 is omitted a user could open the sub document, close the sub document, sign the parent document, reopen the sub document and then be able to edit and save the sub document. At least until they had refreshed TIER.

One concern with this process is validation of sub documents. Since users aren’t directly signing sub documents the validation triggers “On Final Save” and “On Partial Save” wont work. Usually most sub documents are small so “On Any Save” will usually be OK. In addition, we’ve successfully implemented TTIERLookups on a parent document that test a sub document for errors. A bit of work but useful when it’s necessary.

There were a couple of differences in the original design that had to be rejected. Originally I had designed step 4 to be a stored procedure. This stored procedure was then called by a btnSave button on the parent form. In TIER 5.7 btnSave isn’t always called. When a user clicks a documents “X” to close the form and then saves. A btnSave isn’t executed. This creates a situation where parent documents were being signed but not the sub documents. Step 5 was handled a little differently as well. A TTIERLookup on the parent document would test if any sub documents existed in T4W_LOCKS (a user has document open) and then a validation was used to fail if they were. This prevented other users from having the form open but didn’t prevent the signer from editing their own signed sub documents.

Here are some sample triggers roughly edited from our current code. They show the basic logic flow but will need some checking and tweaking before use.

/******************************************************************************
NOTES:
WARNING!! WARNING!! WARNING!! WARNING!! WARNING!! WARNING!! WARNING!! WARNING!!
WARNING!! Do not put validation of sub forms in this trigger!

The tier executable inserts the fd_[table] record, updates the status
of t4w_documents, then lastly inserts the signature record. The trigger will
back out the status change of t4w_documents however the TIER executable appears
to cache that status and users will have to press F5 before they see the
correct value. Otherwise the document will use a status of signed which wont
let them correct the mistakes. Therefore sub document validation needs to be
done before using this trigger. If the parent creates blank sub forms and the
sub forms have only a few fields then it's possible to set to validation on
save (not sign). The other option (if the parent creates saved documents not
blanks) is to use lookups on the parent document to test validation on the
sub documents

This tests for certain forms (sections) being signed and if they are then
it populates that signature down to the sub docs of the document.

I wanted to put this trigger on the fd table however the signature doesn't
yet exist so we can't copy the main signature to the sub docs.

We tried putting this in a stored procedure and then calling that stored
procedure from the save button on the form. however if a user clicks the x to
close the form and signs after the "Do you want to save?" msg then that stored
procedure isn't called. This results in final signed parent docs that don't have
their sub docs signed.

Since we test the section first off this wont noticeably slow down normal
writes.
******************************************************************************/
CREATE TRIGGER [dbo].[t4w_signatures_Insrt]
ON [dbo].[t4w_signatures]
FOR INSERT
AS

BEGIN
/*nocount is mandatory if not here then the TIER executable will throw one of
the following error msgs:
Error creating signature(s): Key column information is insufficient or
incorrect. Too many rows were affected by update
Error creating signature(s): Row cannot be located for updating. Some
values may have been changed since it was last read.
*/
SET NOCOUNT ON

--keep the initial test as short and fast as possible we want to minimize the
--time it takes to put in a signature on "normal" docs.
DECLARE @SectionID INT

SELECT @SectionID = i.op__sectionid
FROM INSERTED i

/* find sig id

SELECT f.OP__ID
, f.OP__TIERNAME
, fs.OP__ID
, fs.OP__TIERNAME
FROM T4W_FORMSECTIONS fs
JOIN T4W_FORMS f ON fs.OP__PARENTID = f.OP__ID
ORDER BY f.OP__TIERNAME
, fs.OP__TIERNAME

WARNING!! If this gets used for a form with multiple sections then the code
that rolls back the doc status
will need to be adjusted.
*/
IF @SectionID = 999 -- **Adjust**
-- OR @SectionID = 999999 place holder for when we do the next form.
BEGIN
BEGIN TRY
BEGIN TRANSACTION
DECLARE @CreatorID INT
, @TierName VARCHAR(60)
, @ParentClassID INT
, @DocID INT
, @When DATETIME
, @NewSignID INT

SELECT @CreatorID = i.op__creatorid
, @TierName = i.op__tiername
, @DocID = i.op__parentid
, @When = i.op__When
FROM INSERTED i

IF @SectionID = 999 -- **Adjust**
BEGIN
DECLARE @SubDocID INT

-- Sign sub form
--==============================
SET @SubDocID = (SELECT MIN(op__DocID)
FROM FD__[SubForm] -- **Adjust**
WHERE op__ParentID = @DocID)

WHILE @SubDocID IS NOT NULL
BEGIN
EXEC TIER_GETNEWID2 @NewSignID OUTPUT, 1, 18

INSERT INTO T4W_SIGNATURES (
OP__ID
, OP__CREATIONDATETIME
, OP__CREATORID
, OP__TIERNAME
, OP__PARENTCLASSID
, OP__PARENTID
, OP__SECTIONID
, OP__USERID
, OP__USERSIGNAME
, OP__WHEN
, OP__FINALSIG
) VALUES (
@NewSignID
, @When
, @CreatorID
, @TierName
, 7
, @ProblemDocID
, 999 -- **Adjust**
, @CreatorID
, @TierName
, @When
, 'T'
)

UPDATE T4W_DOCUMENTS
SET OP__LASTSAVEDDATETIME = @When
, OP__LASTSAVEDBYID = @CreatorID
, OP__STATUSORD = 5
, OP__LASTSAVEDBY = @TierName
, OP__SIGNEDBY = @TierName
WHERE OP__ID = @SubDocID

SET @SubDocID = (SELECT MIN(op__DocID)
FROM FD__[SubForm] -- **Adjust**
WHERE op__ParentID = @DocID
AND OP__DOCID > @SubDocID)
END
END
COMMIT
END TRY

BEGIN CATCH
--have to rollback before we can undo what was done.
ROLLBACK

IF @DocID IS NOT NULL --shouldn't happen but protect ourselves.
BEGIN
UPDATE t4w_documents
SET op__statusord = 2 --saved
, op__signedby = NULL
FROM t4w_documents d
--insure we don't rollback t4w_documents if there was a sig in place
--before we tried to enter this sig.
--shouldn't be an issue but there is a tier bug with multiple sigs on
--docs so doesn't hurt to test. if we get here then this sig isn't
--entered.
LEFT JOIN t4w_signatures s ON d.op__id = s.op__parentid
WHERE d.op__id = @DocID
AND s.op__id IS NULL
END

DECLARE @ErrorMessage NVARCHAR(4000)
, @ErrorNumber INT
, @ErrorSeverity INT
, @ErrorState INT
, @ErrorLine INT
, @ErrorProcedure NVARCHAR(200)

SELECT @ErrorNumber = ERROR_NUMBER()
, @ErrorSeverity = ERROR_SEVERITY()
, @ErrorState = ERROR_STATE()
, @ErrorLine = ERROR_LINE()
, @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')

SELECT @ErrorMessage = ERROR_MESSAGE();

RAISERROR (@ErrorMessage
, @ErrorSeverity
, 1
, @ErrorNumber
, @ErrorSeverity
, @ErrorState
, @ErrorProcedure
, @ErrorLine )

END CATCH
END
END

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

/******************************************************************************
WARNING! This trigger is not setup to handle a form with multiple sections.
if duplicating this trigger to a form with multiple sections then it will need
to be adjusted.
******************************************************************************/
CREATE TRIGGER [dbo].[trg_FD__[TABLE]_Upd]
ON [dbo].[FD__[TABLE]]
FOR UPDATE
AS
BEGIN
DECLARE @SigID INT
, @ErrMsg varchar(220)

SELECT TOP 1 @SigID = s.op__id
FROM t4w_signatures s
JOIN INSERTED i ON s.op__parentid = i.op__docid
ORDER BY s.op__id DESC --last signature entered.

IF @SigID IS NOT NULL
BEGIN
SET @ErrMsg = 'Document is signed. Changes are not possible.'

RAISERROR(@ErrMsg,16,1)
END
END

TIER

Demystifying TIER Permissions

Mappings

Reporting Values

Verifying user has permission

Finding Document #s Used More Than Once

Design Patterns for Creating New Documents in TIER

Simple

Netsmart’s TP_CreateNewTierFormID

usp_CreateT4WDocument

Error Checking and Transactions

op__ParentID Audit and View v_DocStandardKeys

Merging Client Folders in TIER

Django 1.3 Form API ModelForm Example

Crystal Reports Launch Speed

Pre Testing

Test 1

Test 2

Test 3

Solution

Test 4

Summary

Peaches in season!

Form Validation Clues

Preface

How this relates to TIER

Cascading Signatures

Archives

Tags

Pages

Meta