Re: Populate operators for assignment group (686 Views)
Reply
Trusted Contributor
SanjeevDas
Posts: 383
Registered: ‎06-17-2013
Message 1 of 24 (697 Views)

Populate operators for assignment group

Hi Experts,

 

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.

 

Thanks,

Sanjeev

Please use plain text.
Honored Contributor
Piku
Posts: 3,810
Registered: ‎06-17-2010
Message 2 of 24 (693 Views)

Re: Populate operators for assignment group

Hi Sanjeev.

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.


hth,
Please use plain text.
Trusted Contributor
SanjeevDas
Posts: 383
Registered: ‎06-17-2013
Message 3 of 24 (689 Views)

Re: Populate operators for assignment group

Hi Piku,

 

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.

 

Thanks,

Sanjeev

 

Please use plain text.
Honored Contributor
Piku
Posts: 3,810
Registered: ‎06-17-2010
Message 4 of 24 (686 Views)

Re: Populate operators for assignment group

HI,

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")

OR
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")


hth,
Please use plain text.
Trusted Contributor
SanjeevDas
Posts: 383
Registered: ‎06-17-2013
Message 5 of 24 (683 Views)

Re: Populate operators for assignment group

Hi Piku,

 

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.

 

Thanks,

Sanjeev

Please use plain text.
Honored Contributor
Piku
Posts: 3,810
Registered: ‎06-17-2010
Message 6 of 24 (677 Views)

Re: Populate operators for assignment group

Hi,

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.

hth,
Please use plain text.
Trusted Contributor
SanjeevDas
Posts: 383
Registered: ‎06-17-2013
Message 7 of 24 (660 Views)

Re: Populate operators for assignment group

Hi Piku,

 

Please find the images below

 

The Assignment Group and operator field

chg_group_operator_link.JPG

 

Link:cm3r

assign.dept(query working)

chgauto_assigndept.JPG

 

assigned.to(query Not Working)

chgauto_assignedto.JPG

 

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

 

Thanks,

Sanjeev

Please use plain text.
Honored Contributor
Piku
Posts: 3,810
Registered: ‎06-17-2010
Message 8 of 24 (653 Views)

Re: Populate operators for assignment group

Hi,

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,
$query="true"
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.

hth,
Please use plain text.
Trusted Contributor
SanjeevDas
Posts: 383
Registered: ‎06-17-2013
Message 9 of 24 (634 Views)

Re: Populate operators for assignment group

Hi Piku,

 

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.

 

Thanks,

Sanjeev

Please use plain text.
Honored Contributor
Piku
Posts: 3,810
Registered: ‎06-17-2010
Message 10 of 24 (627 Views)

Re: Populate operators for assignment group

[ Edited ]

Hi,

 


If you want to say that assign.dept is nothing but assignmnet group name then follow OOB flow like,

untitled.JPG

 

hth,

Please use plain text.
Trusted Contributor
SanjeevDas
Posts: 383
Registered: ‎06-17-2013
Message 11 of 24 (616 Views)

Re: Populate operators for assignment group

Hi Piku,

 

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.

 

Thanks,

Sanjeev

 

Please use plain text.
Honored Contributor
Piku
Posts: 3,810
Registered: ‎06-17-2010
Message 12 of 24 (612 Views)

Re: Populate operators for assignment group

[ Edited ]

Hi,

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)

- DB2:VARCHAR(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

Use the following mass update logic and  attached JavaScript code to sync the operators with the assignment groups.

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) 

 

hth,

Please use plain text.
Trusted Contributor
SanjeevDas
Posts: 383
Registered: ‎06-17-2013
Message 13 of 24 (608 Views)

Re: Populate operators for assignment group

[ Edited ]

Hi Piku,

 

Fatal Error!

 

Can't login to HPSM. Not even using falcon.

 

Error:

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

 

Please help,

Sanjeev
 

Please use plain text.
Honored Contributor
Piku
Posts: 3,810
Registered: ‎06-17-2010
Message 14 of 24 (605 Views)

Re: Populate operators for assignment group

Hi,

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

hth,
Please use plain text.
Trusted Contributor
SanjeevDas
Posts: 383
Registered: ‎06-17-2013
Message 15 of 24 (604 Views)

Re: Populate operators for assignment group

Hi Piku,

 

Sorry!! Not in DB but in Service Manager.

Pls help....

 

Regards,

Sanjeev.

Please use plain text.
Honored Contributor
Piku
Posts: 3,810
Registered: ‎06-17-2010
Message 16 of 24 (599 Views)

Re: Populate operators for assignment group

Is it your production box ?

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.

hth
Please use plain text.
Trusted Contributor
SanjeevDas
Posts: 383
Registered: ‎06-17-2013
Message 17 of 24 (598 Views)

Re: Populate operators for assignment group

[ Edited ]

Hi Piku,

 

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!

 

Thanks,

Sanjeev

Please use plain text.
Honored Contributor
Piku
Posts: 3,810
Registered: ‎06-17-2010
Message 18 of 24 (593 Views)

Re: Populate operators for assignment group

No this might not solve issue, because some mapping of data would be there from app end.
So can take the DB backup of this table and load it in your box.
Please use plain text.
Trusted Contributor
SanjeevDas
Posts: 383
Registered: ‎06-17-2013
Message 19 of 24 (584 Views)

Re: Populate operators for assignment group

Hi Piku,

 

Which table are you suggesting here? I think I need to restore the full database...rite!

 

Sanjeev

Please use plain text.
Honored Contributor
Piku
Posts: 3,810
Registered: ‎06-17-2010
Message 20 of 24 (578 Views)

Re: Populate operators for assignment group

No, you can restore only operator table.
Please use plain text.
Trusted Contributor
SanjeevDas
Posts: 383
Registered: ‎06-17-2013
Message 21 of 24 (574 Views)

Re: Populate operators for assignment group

Hi Piku,

 

There are tables OPERATORM1, OPERATORA1, OPERATORA2, OPERATORA3, OPERATORA4, OPERATORA5.

Shall I restore all the table to make SM working again?

 

Thanks,

Sanjeev

 

Please use plain text.
Super Advisor
deepsm
Posts: 205
Registered: ‎06-29-2011
Message 22 of 24 (566 Views)

Re: Populate operators for assignment group

Hi Sanjeev

 

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:-

select("operators","assignment","name",[assign.dept])


You can test this.This will not take time and will not create any performance issue.

Thanks,

Deepesh
Please use plain text.
Honored Contributor
Piku
Posts: 3,810
Registered: ‎06-17-2010
Message 23 of 24 (549 Views)

Re: Populate operators for assignment group

Hi Sanjeev,

If you have multiple operator table, then have to restore each and every one otherwise data in left table will be lost.

hth,
Please use plain text.
Super Advisor
deepsm
Posts: 205
Registered: ‎06-29-2011
Message 24 of 24 (512 Views)

Re: Populate operators for assignment group

Hi Sanjeev

 

Let us know if the issue resolved.

Thanks,

Deepesh
Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation