Sage 100

I'm trying to put together a security analysis report via a crosstab query in MS Access. I want to list the security tasks down the left, the roles defined across the columns, and reflect the security setting for each within the corresponding grid. I know I can run a security report from within 100 but I would have to tear them apart and past them side by side to get a comparison by role. To achieve the above, I have a cross tab query created but I'm missing a linkage between the SY_RoleSecurity table and the SY_Task table. RoleSecurity has the column ""TaskKey"" which is 9 characters long. The first 2 digits appear to correspond with the Module Number and the last 3 digits appear to correspond with the TaskID in SY_Task. However I don't know what the 4 characters in the middle represent and they seem significant. I tried linking TaskKey to the SY_Menu table as they both contain a 9-character column by this name. However, SY_Menu doesn't have rows for ever TaskKey contained in the RoleSecurity table. (for example, there is no row for Bank Reconciliation Setup yet there is a Security Setting for this. What am I missing?

Does one of the flavors of the role report give you what you want? If so you can preview the report, rename it it to the default report work file name, then do an ODBC read of that file.

1-2 = SY_Task.Module 3-3 = SY_Task.TaskOwner 4-6 = SY_Task.Developer Code 7-9 = SY_Task.Task ID Attached is an example crosstab with the value entries set to the concatenated values of Create+Modify+Remove+Print+Update

@DanBurleson what is the significance of 3-3? What does TaskOwner tell me, if anything? The first, third, and fourth segments I've got.

I don't have the foggiest, but it does correlate between the SY_RoleSecurity.TaskKey.3 to the SY_Task.TaskOwner based on allowed values (0, 1, 2, 9)

Task Owner: 0 = Program (e.g. G/L Account Maintenance - 0 is most common task owner) 1 = Menu (e.g. General Ledger Budget Menu) 2 = Security Event (e.g. Allow Out-of-Balance Journal Entries) 9 = For Tasks Added On The Fly (not by the system). A task that ends up on a menu: (e.g. Report Manager report, V/I Import or Export that is added to Jobs menu, a UDT where you chose Add UDT to Desktop so it appears under Custom Office / UDT Maintenance)

So this is why Sy_Menu would not have a corresponding row for every task key. E..g Where TaskKey contains TaskOwner = 2 (a security event) that row would not exist in Sy_Menu

@AlnoorCassim and @DanBurleson thank you both for your help. This is exactly what I was looking for. Is this something that is documented somewhere or is it a tribal knowledge kind of thing. You just have to learn it from someone who traveled that way before you? There seem to be a lot of things when it comes to development such as database design and BOI functionality that is tribal rather than documented.

Welcome the real life survivor island. Stick with the tribe!!!!