Agile shows alert and does not allow to modify List attribute to switch to another List if this attribute is already referenced by below criteria.
- Admin Criteria
- Report Criteria
- Search Criteria
To enable the attribute modification, we have to go to these Criteria and remove the references one by one. But usually in the alert window we cannot identify the criteria is of Admin, Report or Search. Also if it is used by Search, the alert does not show us who owns the Search. Below screenshot will confuse us.
Admin Criteria
Say we have “Change Orders.Page Two” list attribute named “JieListAttribute” and links to a List “test000001”. Now we create a Criteria named “JieCriteria”, add this list attribute to criteria mask like below.
Note: Do not select “IS NULL” or “IS NOT NULL” because these two do not impact attribute’s modification.
Go back to attribute “JieListAttribute”, try to select any list for it and click Save, you will get alert.
To detect Admin Criteria references, we can use below SQL to find out. When it prompts for ATTRIBUTE_ID and CLASS_NAME, input its BASE ID and class name (case sensitive).
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
SET LIN 200
COLUMN class_id format a15
COLUMN criteria_id format a20
COLUMN criteria_name format a30
COLUMN class_name format a30
SELECT criteria_id || '' criteria_id,
criteria_name,
class_id,
class_name
FROM (SELECT a.id criteria_id,
a.description criteria_name,
b.value class_id
FROM nodetable a,
propertytable b
WHERE a.parentid = 3642
AND a.id = b.parentid
AND b.propertyid = 53
AND a.id IN (SELECT DISTINCT parentid
FROM admincriteria
WHERE attid = &ATTRIBUTE_ID
AND relop NOT IN ( 9, 10 ))) criterias,
(SELECT n.id cid,
n.description class_name
FROM nodetable n,
(SELECT id
FROM nodetable
WHERE description = '&CLASS_NAME'
AND objtype IN ( 5, 13 )) nc
WHERE n.id = nc.id
OR n.parentid IN (SELECT id
FROM nodetable
WHERE objtype = 14
AND parentid = nc.id)) classes
WHERE classes.cid = criterias.class_id;
SQL>/
Enter value for attribute_id: 1271
old 15: WHERE attid = &ATTRIBUTE_ID
new 15: WHERE attid = 1271
Enter value for class_name: Change Orders
old 22: WHERE description = '&CLASS_NAME'
new 22: WHERE description = 'Change Orders'
CRITERIA_ID CRITERIA_NAME CLASS_ID CLASS_NAME
-------------------- ------------------------------ --------------- ------------------------------
2474421 JieCriteria 6000 Change Orders
Report Criteria
Let’s create a Custom Report as any user on WebClient, define its Query Definition with “Change Orders.Page Two.JieListAttribute In (xxxxx)”. Then try to modify the attribute’s list to others on JavaClient, we will get same alert.
Solution
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
SET LIN 200
COLUMN report_id format a10
COLUMN report_name format a40
COLUMN query_id format a10
SELECT report_id || '' report_id,
report_name,
query_id || '' query_id
FROM (SELECT a.id report_id,
A.NAME report_name,
B.TYPE report_class_id,
b.id query_id
FROM REPORT A,
QUERY B
WHERE A.CRITERIA_ID = B.ID
AND B.ID IN (SELECT DISTINCT QUERY_ID
FROM CRITERIA
WHERE ATTR_ID = &ATTRIBUTE_ID
AND RELATIONAL_OP NOT IN ( 9, 10 ))) reports,
(SELECT n.id cid,
n.description class_name
FROM nodetable n,
(SELECT id
FROM nodetable
WHERE description = '&CLASS_NAME'
AND objtype IN ( 5, 13 )) nc
WHERE n.id = nc.id
OR n.parentid IN (SELECT id
FROM nodetable
WHERE objtype = 14
AND parentid = nc.id)) classes
WHERE reports.report_class_id = classes.cid;
SQL>/
Enter value for attribute_id: 1271
old 13: WHERE ATTR_ID = &ATTRIBUTE_ID
new 13: WHERE ATTR_ID = 1271
Enter value for class_name: Change Orders
old 20: WHERE description = '&CLASS_NAME'
new 20: WHERE description = 'Change Orders'
REPORT_ID REPORT_NAME QUERY_ID
---------- ---------------------------------------- ----------
14325816 Can you guess who I am? (d) 14325817
14325809 Can you guess who I am? (c) 14325811
Then you can open the Report object and modify the Query Definition to remove the reference, if you have the privilege.
Search Criteria
On WebClient, create an Advanced Search, set criteria to use the same attribute. We will get same error if we go to JavaClient to modify the attribute.
Solution
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
SET LIN 200
COLUMN loginid format a15
COLUMN query_name format a40
COLUMN query_id format a10
SELECT u.loginid,
query_id || '' query_id,
query_name
FROM (SELECT A.ID query_id,
A.TYPE query_class_id,
A.NAME query_name,
A.owner ownerid
FROM QUERY A
WHERE A.ID IN (SELECT DISTINCT QUERY_ID
FROM CRITERIA
WHERE ATTR_ID = &ATTRIBUTE_ID
AND RELATIONAL_OP NOT IN ( 9, 10 ))) queries,
(SELECT n.id cid,
n.description class_name
FROM nodetable n,
(SELECT id
FROM nodetable
WHERE description = '&CLASS_NAME'
AND objtype IN ( 5, 13 )) nc
WHERE n.id = nc.id
OR n.parentid IN (SELECT id
FROM nodetable
WHERE objtype = 14
AND parentid = nc.id)) classes,
agileuser u
WHERE queries.query_class_id = classes.cid
AND queries.ownerid = u.id
AND (query_name is null OR query_id || '' <> query_name);
sql>/
Enter value for attribute_id: 1271
old 11: WHERE ATTR_ID = &ATTRIBUTE_ID
new 11: WHERE ATTR_ID = 1271
Enter value for class_name: Change Orders
old 18: WHERE description = '&CLASS_NAME'
new 18: WHERE description = 'Change Orders'
LOGINID QUERY_ID QUERY_NAME
--------------- ---------- ----------------------------------------
admin 14321593
admin 14325249
admin 14325817 admin1401373750229
admin 14325848 Can you guess who I am? (a)
admin 14325896 Can you guess who I am? (b)
You can ask the user “admin” to modify his Search criteria.
Note: if query_name is null or the format is like loginid plus a number like “admin1401373750229”, it means they are temporary Advanced Search. They must be deleted manually via SQL:
1
2
3
4
delete query where id=&query_id;
delete criteria where query_id=&query_id;
delete select_list where query_id=&query_id;
commit;