Forgot password? | Forgot username? | Register

Filtering records on department

Filtering records on department

Hi all,

I need to filter records on a department, specified in SecDepartment_tab, however I can't seem to get the Texql right.

For example, I have a record IRN 11 and I'm querying the associated narratives like so:

$query->Select("irn_1");
$query->Table = "enarratives";
$query->Where = "irn_1 = 11"

What I need to do is filter the department so I prevent users from seeing IRNs (by way of injection in the URL).

The first value in "SecDepartment_tab" array in the query result is "Fake Department", however I don't know how to add this value in the query or specify it in a WHERE clause.

I tried to add SecDepartment_tab->egroups->GroupName to the query, but the query crashes saying "Unable to resolve 'Fake'. No such file or directory (2)" (or some such)...

e.g. $query->Select("SecDepartment_tab->egroups->GroupName");

Is it because there's a space in the value? Are departments in egroups?


ps. I use "Fake Department" here, but the department is really another two worded value.

Edited by: - 01-Jan-70 09:00:00

Administrator has disabled public posting. Please login or register in order to proceed.

Re: Filtering records on department

SecDepartment_tab exists in most/all modules, including Groups. It is the field by which the Security Registry settings apply the appropriate filters.

The example KE give in their help system of how to apply Record Level Security based on Department is below:

Key 1 User
Key 2 user1
Key 3 Table
Key 4 Default
Key 5 Security
Key 6 Edit
Value SecDepartment_tab=Fine Art

This means that user1 is effectively only permitted edit to records where the SecDepartment_tab contains Fine Art. Because Key 4 is "Default" rather than a specific module, this means that any record in any module can be edited by user 1 so long as that record has a Department = "Fine Art".

If I am reading you correctly you want to filter out certain narratives from the results based on the narrative record's department field. The Department field (SecDepartment_tab) is a nested table, and therefore requires a nested select query.

I don't know the syntax, but the conept in simple SQL:

SELECT irn_1 FROM enarratives
WHERE irn_1 = '11'
AND SecDepartment IN (SELECT SecDepartment FROM SecDepartment
WHERE SecDepartment = 'Fake Department')


To demonstrate this within EMu using Show Search:

select all
from enarratives
where true and
(
exists
(
SecDepartment_tab
where
(
SecDepartment contains '"Fake Department"'
)
)
)
and
(
irn = 11
)



Hope this makes some sense... I'm not an SQL coder.

Mark - NGA

Mark Bradley – Assistant Registrar, Documentation (EMu)
National Gallery of Australia

Mark Bradley
Assistant Registrar (EMu Guy)
useravatar
Offline
147 Posts
Male  Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Filtering records on department

Hello Gavin,

The SecDepartment_tab column is a text column containing a list of all the departments that claim some ownership of the record. The values in the column do not link to any module, they are simply text.

From the code segment:

$query->Select("SecDepartment_tab->egroups->GroupName");

it looks like you are trying to look up the value(s) in the SecDepartment_tab column in the GroupName column in the egroups table. I assume you want to return a list of IRNs as stored in the egroup table?

If this is the case the following Texql will work:

select irn_1, (UNNEST(SELECT Keys_tab FROM egroups where GroupName IN narrative.SecDepartment_tab) on Keys_tab)
from enarratives AS narrative
where irn_1 = 11

The query result will look like:

(11,?|2|3|4|5])

where "11" is the narratives IRN and 1,2,3,4,5 are the catalogue IRNs from the egroups table.

I hope this is what you are after. If not please email me a little more detail and will try and provide a more accurate reply.

Regards

bern. (Bernard.Marshall at mel.kesoftware.com)
Bernard Marshall
KE Software
Melbourne, Australia

Bernard Marshall (Axiell Melbourne)
useravatar
Offline
43 Posts
Administrator has disabled public posting. Please login or register in order to proceed.
There are 0 guests and 0 other users also viewing this topic