12-26-2013 03:03 AM
I have two fields Coordinator Group(assign.dept) and Coordinators(assigned.to)
Coordinator Group field is linked with assignment table and Coordiantors field is linked with operators table.
The groups populated in Coordinator Group field contains operators.
I want to write a simple query for Coordinators(assigned.to), which will check the assign.dept and will populate the operators assigned to the group.
Merry Christmas and wish you all a happy new year in Advance.
12-26-2013 03:19 AM
Feels that you want to say "The groups populated in Coordinator Group field contains 'Department'" instead of "The groups populated in Coordinator Group field contains operators."
Are you capturing dept in operator table ? if so then use simple link query like,
if (not null(assign.dept in $File)) then ($query="dept_in_operator # assign.dept in $File") else ($query="true")
Otherwise dept is used to capture in contact table, so similarly can use link line fro contact table.
12-26-2013 03:26 AM
The assign.dept is linked with assignment group
assigned.to is linked to operators.
Depending on assign.dept, assigned.to will be populated.
Shall I use the above stated query to fetch operators from groups.
12-26-2013 03:39 AM
Here you can do one thing make one more field on form (also in table) which will capture dept along with assignment group.
Now use this new field to pull the respective operators.
Say new field name is 'dept' , then use query for link line 'assigned.to',
if (not null(dept in $File)) then ($query="name # dept in $File") else ($query="true")
if you do not want to add new field in table and form then try using variable add,
in link line of 'assign.dept'
source || target
dept || $dept
now in link line 'assigned.to',
if (not null($dept)) then ($query="name # $dept") else ($query="true")
12-26-2013 03:47 AM
Thanks for the response as always.
I dont want department to be populated.
I want to populate only the operators in one field related to the groups populated in another field.
Same as Assignment Group and assignee fields in Incident management.
12-26-2013 03:59 AM
This will not populate department.
Also need to know whether you are using department name instead of assignment group ?where
assign.dept == dept name from assignment table
then use as above post.
also post snap of link line and form using showing correlation.
12-26-2013 06:14 AM
Please find the images below
The Assignment Group and operator field
assigned.to(query Not Working)
The assigned.to query is not working. Altough the Group does contain the operator but clicking on the Change Coordinator fill button doesn't populates the Operators related to the Group.
Please check the query for assigned.to
12-26-2013 06:42 AM
I am not getting your requirements , you are having mixing up stuff.
How Group will contains operators when you are filling the assignment group name ?
****You must have assign.dept in operator file.****
Also in second link (assigned.to) you have to put query like for first two lines,
if (not null(assign.dept in $File)) then ($query=" and dept.name # assign.dept in $File") else ($query="true") == use this line only if 'dept.name' is there in operator file.
if (not null(assign.to in $File)) then ($query=" and name # assign.to in $File") else ($query="true")
In third line replace '#' with '='
Link can only fetch data from single file, it built query from fields of single file.
12-26-2013 09:57 PM
May be I was not able to explain the requirement properly.
Group does contain operators.
Filling the assignment group will only populate the group name. However while filling the assignee, it should populate the operators related to the Assignment Group.
But that's not happening in this scenario that i have posted.
One more question....why do I need dept.name in the query? The relation is based only on the assignment groups and its related operators.
Looking forward for a feedback from your end.
12-27-2013 02:10 AM
Thanks it worked. Yes assign.dept is nothing but assignment groups.
However, one thing I noticed the query is taking longer time to search for the operators related to the selected group. Another thing I noticed is that, the operators added manually to the groups are only getting populated. Whereas, the operators imported from excel file are not getting populated.
Can you please help me by your valuable suggestions.
12-27-2013 03:10 AM - edited 12-27-2013 03:11 AM
1) query is taking longer time to search -- It is because you might have large number of users in operator table and query go for full index search which is heavy one. So check it you have to map the assignment.group array to new sub array table (An). It may take time in hours.
Modify the operator table in dbdict to avoid the performance issue when opening the Assignee selection page in the incident workflow
i. Click Tailoring > Database Dictionary.
ii. Type operator into the File Name field and then click Search.
iii. In the SQL Tables tab, add a new table a7.
Note: If the a7 value has already been used, use the first available value between a8 and a99, depending on your specific implementation.
iv. In the Keys tab, place your cursor on the blank header at the bottom of the key list, and then click the New Field/Key button to add a new key:
o Type: Nulls and Duplicates
o Field: assignment.groups
v. In the Fields tab, change the SQL table for the assignment.groups fields:
o assignment.groups (array type): change the SQL table from null to a7 (or whatever value you assigned in step iii above).
o assignment.groups (character type): change the SQL table from m1 to a7 (or whatever value you assigned in step iii above) and change the SQL Type from TEXT to the following value that matches your database:
- SQL Server: VARCHAR(60)
- Oracle: VARCHAR2(60)
Caution: With this change, field value will be automatically truncated if its length is greater than 60. To prevent data loss, you can adjust the VARCHAR or VARCHAR2 length accordingly.
vi. Click OK to save the changes.
2) operators added manually to the groups are only getting populated -- It should be because operator added in assignment not getting updated in operator table. There is trigger on assignment table which update the operator table with assignment group to which operator id added. And in case of mass upload this trigger might not getting executed and assignment group is not in respective operator profile.
Title: Operators and assignments are not in sync Document ID: KM00466129 Product: Service Manager Version: Updated: 2013-Dec-06 Summary: When adding/removing operators from the assignment table the assignment isn't being added to the corresponding operator record, so the operator and assignment records are out of sync. When adding/removing operators from the assignment table the assignment isn't being added to the corresponding operator record, so the operator and assignment records are out of sync. Solution
Prefix each operator with “xyzzy”……
for $i = 1 to lng(denull(operators in $file)) do ($modOper="xyzzy";$modOper+=$i in operators in $file;$i in operators in $file=$modOper)
Remove the prefix…..
for $i = 1 to lng(denull(operators in $file)) do ($modOper=$i in operators in $file;$i in operators in $file=substr($modOper, 6, lng($modOper)
12-27-2013 04:34 AM - edited 12-27-2013 04:39 AM
Can't login to HPSM. Not even using falcon.
1796( 804) 12/27/2013 17:38:05 RTE E Error: SQL State: 42S02-208 Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'OPERATORA7'.
1796( 804) 12/27/2013 17:38:05 RTE I Row Number = 1, Column number = -1
1796( 804) 12/27/2013 17:38:05 RTE E Error: SQL State: 42000-8180 Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
1796( 804) 12/27/2013 17:38:05 RTE I Row Number = 1, Column number = -1
1796( 804) 12/27/2013 17:38:05 RTE E API=SQLNumResultCols [in _describe], Statement=SELECT * FROM OPERATORA7
12-27-2013 04:45 AM
What do you mean by can not login to DB.
Did you performed the backup ? if not so then would be difficult to correct.
Have you followed all steps.
Also post snap of dbdict field added
12-27-2013 04:59 AM
There is not any way to save your operator table data if no backup form DB side is there. You must have backup before any such activity.
12-27-2013 05:09 AM - edited 12-27-2013 05:15 AM
Its not a production Box. Went to Database and searched for the table OPERATORA7, the table is not created. What I am thinking is if I can create OPERATORA7 in database with the same columns defined in OPERATORM1, can this solve the issue?If OPERATORA7 was created then how would the structure look like? Same as OPERATORA5 table!
12-27-2013 06:26 AM
There are tables OPERATORM1, OPERATORA1, OPERATORA2, OPERATORA3, OPERATORA4, OPERATORA5.
Shall I restore all the table to make SM working again?
12-27-2013 06:45 AM
Better to restore complete database.
Post restoring perform below step to overcome original issue:-
fd>Form Name>select change coordinator fill button and make below changes
1.disable fill button visible
2.Enable combo button visible
3.Add below line in visible list condition and display list condition:-
You can test this.This will not take time and will not create any performance issue.