Hi Robbert Ijsselsteijn ,
Here is the the solution
import groovy.sql.Sql
def id=""
def projectName=""
if(replica.assignment_group.display_value=="ABC"){
workItem.projectKey = "Demo_Exalate"
workItem.typeName = "Bug"
workItem.iterationPath = "Demo_Exalate"
workItem.areaPath = "Demo_Exalate"
workItem."System.TeamProject"="Demo_Exalate"
projectName="Demo_Exalate"
}
else
{
workItem.projectKey = "Demo Project"
workItem.typeName = "Bug"
workItem.iterationPath = "Demo Project"
workItem.areaPath = "Demo Project"
workItem."System.TeamProject"="Demo Project"
projectName="Demo Project"
}
if(!firstSync)
{
id=workItem.key
def someMap = [
"project": projectName
]
def json = groovy.json.JsonOutput.toJson(someMap)
def sqlStmts = [
"""
SELECT id, issue_urn, field_values
FROM replica
WHERE issue_id_str = '"""+id+"""';
""",
"""UPDATE replica SET field_values = '"""+json+"""' WHERE issue_id_str = '"""+id+"""';""",
"""
SELECT id, issue_urn, field_values
FROM replica
WHERE issue_id_str = '"""+id+"""';
"""
]
def sb = new StringBuilder()
def sqlConnectionFactory = syncHelper.syncRequestReplication.db.source
def sqlConnection = null
try {
sqlConnection = sqlConnectionFactory.createConnection()
def sql = new Sql(sqlConnection)
sqlStmts.each { sqlStmt ->
sb << "-----------------------------------\nSQL: $sqlStmt\nResults: \n"
if (sqlStmt.toUpperCase().matches("\\s*UPDATE\\s+.+")) {
def result = sql.execute(sqlStmt)
sb << (!result ? sql.updateCount : " no updated row number ")
} else {
def counter = 0
sql.eachRow(sqlStmt) { rs ->
def nCols = rs.getMetaData().columnCount
if (counter == 0 && nCols > 0) {
for (i in 1..nCols) {
sb << rs.getMetaData().getColumnName(i)+ ((i<(nCols)) ? "|" : "")
}
sb<<"\n"
}
for (i in 1..nCols) {
sb << ""+rs.getObject(i)+ ((i<(nCols)) ? "|" : "")
}
sb<<"\n"
counter = counter + 1
}
}
sb << "-----------------------------------\n"
}
} finally {
sqlConnection?.close()
}
log.error("#sql sb=${sb.toString()}")
}
workItem.summary = replica.summary
workItem.description = replica.description
....
Kindly let us know in case of any concerns.
Thanks,
Sonal
Hey Sonal, thanks a lot! It works perfectly. Can you describe what the code does so I can troubleshoot and for future reference? Does it remap the exalate connection to another project or something? Thanks again for the help 
Hi,
Here is the solution for following use case:
Move the workitem between Azure Devops projects based on ServiceNow's Assignment Group value:
if(replica.assignment_group.display_value=="ABC"){
workItem.projectKey = "Demo_Exalate"
workItem.typeName = "Bug"
workItem.iterationPath = "Demo_Exalate"
workItem.areaPath = "Demo_Exalate"
workItem."System.TeamProject"="Demo_Exalate"
}
else
{
workItem.projectKey = "Demo Project"
workItem.typeName = "Bug"
workItem.iterationPath = "Demo Project"
workItem.areaPath = "Demo Project"
workItem."System.TeamProject"="Demo Project"
}
workItem.summary = replica.summary
workItem.description = replica.description.........<remaining script for setting other field values>
Let us know in case you need further help for your use case.
Thanks,
Sonal
Hey Sonal, Jillani,
At first I thought this would work, but I seem to got stuck again. I can indeed move work items this way and it works well.
However, what now happens is the following:
I create an incident in ServiceNow > a work item is created in Azure DevOps.
I change assignment group in ServiceNow, based on mapping, the project of the work item now changes in Azure DevOps (so far so good).
If I now try to update the work item in Azure DevOps it will not send this update back to ServiceNow, I do not see any sync happening. For instance if I send a comment to ServiceNow it is not visible.
If I change the item back to the original servicenow group and try to send an update from Azure DevOps it will synchronize again (including the old comment I placed earlier. Interestingly the comment will be placed in ServiceNow and in a subsequent sync the exact same comment will be posted back to Azure DevOps too).
Can you guys help out further? Thanks in advance.
I am glad that Sonal shared the solution. May I know if it did served the purpose? Please let us know. Looking forward to it.
BR,
Jillani
Hey Jillani, the solution worked semi-well. There's a blocking issue now, that if the work item is moved from one Azure DevOps project to the next, the work item is unable to send back updates to servicenow, somehow its not being recognized, it doesn't give an error, just nothing happens.
Thanks looking forward to it
Hi Sonal,
Thanks for the answer. I had not expected this to work as I tried this once before with altering the project key, iteration and area paths, but I did not alter the system.teamproject that time. That's the variable I seemed to have missed! Thanks again!
I have configured a connection for ServiceNow - Azure DevOps. The trigger for this connection is on the ServiceNow side based on the assignment group. We use assignment group as the identifier to steer an entity to the correct project in Azure DevOps.
Background:
Entities, such as incidents, tend to change assignment groups often. What I have scripted now is that when the assigment group is changed that the work item is removed on the destination side and the synchronisation is broken.
Since the assignment group is the trigger, if there is another connection available mapped to the new assignment group it will regenerate the sync using the secondary connection and thus regenerates the work item in the new destination project.
It works alright, but stakeholders don’t like the approach as they aren’t able to track their progress properly over multiple projects since the work item is not moved but regenerated.
Query:
Stakeholders require work items to be moved from one project in Azure DevOps to the next if the assignment group changes in ServiceNow.
Do you guys have any customers who have had this query previously and how did you guys solve it?
My own thought would be to create a list in the code mapping all the assignment groups to their respective projects and using REST-API of Azure DevOps to move items if current assignment group =! previous assignment group. Does that sound logical to you guys? Is there something in Exalate already programmed for moving work items from one Azure DevOps project to the next so I skip coding the API myself alltogether?
Thanks for the help
(posting it on the behalf of a client)