Originally asked by Cristina Mogos on 27 October 2020 (original question)
Hi,
I have a Script runner database picker custom field.
I want to sync it to another field in a Jira Service Desk instance.
replica.customFields.“Customer Office” = issue.customFields.“CustomFiled_DatabasePicker”
I get the error: " Script error for issue CPM-224. Details: Could not find custom field by key `CustomFiled_DatabasePicker`. Error line: Script12.groovy:28
many thanks for your support.
Cristina
Answer by Dmitrij POPOV on 10 March 2022
Hello Cristina Mogos ,
Scriptrunner’s Database Picker custom field is returning a Jason String value, instead of a “regular” String.
That is why you had a value “[ Toyota ]” instead of “Toyota”.
To fix this, could you, please, try the following solution:
replica.customKeys."Customer Office" = issue.customKeys."Customer Office"
issue.customFields.Organizations.value = replica.customKeys."Customer Office".value?.getAsString()
This way you will be able to cast Json String in to String.
With best regards,
Dmitrij
Answer by Cristina Mogos on 01 December 2020
Hi Francis Martens (Exalate)
i have created https://support.idalko.com/servicedesk/customer/portal/8/EASE-6837
Best and thanks
Cristina
Answer by Francis Martens (Exalate) on 27 October 2020
Are you sure that you have spelled the name correctly
Maybe instead of ‘CustomFiled_DatabasePicker’ you could use ‘CustomField_DatabasePicker’
If that doesnt work, please use the customfield id
Hi Francis,
sorry for late answer, it seems that my reply did not get into this stream.
Anyway, i made good progress on the issue.
With this trick everything seems to work as expected , with one single issue where i’m stuck again:
When i update the database picker value, the listener updates the Customer Office field but the exalate does not happen. The listener is written also using Script Runner.
Any ideas? thank you
Hi Cristina Mogos
The value of the database picker is copied into the text field, but it doesn’t trigger the exalate …
How do you update the text field, and is an event generated whenever doing so?
Hi Francis,
yes, i have custom listener on “Issue Update”.
Best and thanks
Cristina
The reason that exalate is not kicking in is because there is no Jira issue event raised.
Try to find out why that is.
Hi Francis,
i do not know how to do this! any advice is welcome!
I did also a small recording for that, because the issue update is triggered (the TEXT filed gets the new value) → but the synchronization does not start. [[CPM-237] Test Customer Office Sync CPM -_ CISU 5 - Cinemo’s JIRA - Google Chrome 2020-11-05 13-55-44.mp4](/download/attachments/20120644/%5BCPM-237%5D%20Test%20Customer%20Office%20Sync%20CPM%20-_%20CISU%205%20-%20Cinemo%27s%20JIRA%20-%20Google%20Chrome%202020-11-05%2013-55-44.mp4?version=1&modificationDate=1604581312358&api=v2)
Many thanks
Cristina
What happens is (IMO)
You will have to do some additional testing.
A trick to always have the exalate send a message is to force a change by adding something like
// ensure that the message is always sent
replica.customKeys.foo = new Date()
But probably the replica will not included the text field which is still being updated.
Can you validate that this is correct?
Hi Francis,
i did the test, unfortunately it does not work.
i did more tests and what I discovered is that inline editing is the issue (same happens for adding a label or any other field when changing via inline editing. sometime, but only sometime it works… i do not understand why…) When using the “Edit” issue button it works.
Could you please take a look into the inline editing?
Best and thanks
Cristina
Inline editing is the same as normal editing, but then a single field. It will generate an issue updated event, and then exalate will pick it up.
But if the resulting message (by processing the outgoing sync) is the same as the previous message, no sync will happen.
Can you check if the field that you change is part of the outgoing sync.
You can always test by adding a throw exception in your outgoing sync detailing the what has been changed.
For instance - showing the labels can be done by adding
throw new Exception("Labels are ${issue.labels}")
There are now 2 outcomes when changing the label
a) The exception is raised - so you know that the outgoing sync processor is run → see if this leads to a sync, and if not check if the message has changed
b) The exception is not raised - there is some reason why the issue update is not generated.
Find out why that is.
Hi Francis,
you are right, the label is not sync.
I still do not know how to “fix” this issue.
this is how it should happen:
Customer Office database picker changes → Customer office is updated → sync has to be triggered.
it works for Issue creation, but not for issue update.
any advice is welcome.
Best and thanks
Cristina
There is some problem with the image …
Hi Francis,
I took some time to summarize the need and to better describe the challenge.
Goal: Synchronize Customer Office (internal jira) with Organization (remote Jira Service Desk)
Customer Office is a custom field defined in Jira internal and is a database picker Script Runner Field. (reads value from a database, single select)
The value selected by the user in this filed has to be synced with the field Organization in Jira Service Desk using Exalate plugin.
Implementation:
Because Database picker has no documentation how to sync via Exalate, I decided to use a hidden filed: [Text] Customer Office where it is copied the value selected via the database picker.
On the “create issue” transition, I wrote the code (using script runner) for the copy value.
The issue in Jira Service Desk is created with right values (using Exalate plugin):
Outgoing sync (internal Jira):
replica.customFields.“Customer Office” = issue.customFields.“[Text] Customer Office”
Incoming sync(remote JSD)
issue.customFields.Organizations.value = replica.customFields.“Customer Office”.value
In order to catch the “issue update” use case, I have implemented a listener which copies the value from Customer Office to [Text] Customer Office.
Issue is that when Customer Office is updated/respectively the [Text] Customer Office is updated, the synchronization is not triggered.
Any idea about how I could fix this is more than welcome.
It could be that the Database picker field can be direct exalated, and I should not worry about all these workarounds?
Many many thanks in advance for all your support
Cristina
Can you show the properties of the database picker when syncing
Add for instance
def dbpicker = issue.customFields."Customer Office"
throw new Exception("Database picker = ${dbpicker.properties}")
trigger a sync (by modifying something on the source issue)
and check what the values are provided by the custom field by inspecting the error the exception generates
Hi Francis,
i get this:
"
Error Detail Message:
Script error for issue CPM-239. Details: Database picker = [value:10101, type:UNHANDLED, class:class com.exalate.basic.domain.hubobject.v1.BasicHubCustomField, uid:null, id:13400, locked:false, description:Customer Office=Organization, name:Customer Office]. Error line: Script8.groovy:33
"
this value: 10101 is the ID from the database :
"
select id,name FROM database_tabel
where lower(name) like lower(?) || ‘%’
"
OK - great
It shows what we expected a value which is the primary key in the ‘database_tabel’
So - how do you retrieve that value from the database picker into the text field?
Hi Francis,
I use script runner sql:
"
//get
def rows = DatabaseUtil.withSql(‘Local Database Connection’) { sql ->sql.firstRow(“select name from database_tabel where id=”+ID)[0]
//set
textCf2.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(textCf2),rows),changeHolder)
"
best
Cristina
Hi Francis,
updates on my side: i just got an answer from Script Runner how i could get the Database picker Text without querying the database ( I asked them for an alternative as the “selcet//” on every update is really a non best practice).
I will come back to you as soon as i have tested their proposal.
Many thanks
Cristina
Aha - that is good news.
Note that if the scriptrunner approach doesn’t work, you can also use the sql query approach in the outgoing processor
Hi Francis,
updates here: the colleagues from Script runner guded me to get the Name of the DatabasePicker field as follow:
def valueForDbPicker = textCf1.getViewHtml(fieldLayoutItem, null, issue) as String
and use its value as: valueForDbPicker?.trim()
and the copy mechanism works; but now i’m back to the initial issue:
Script error for issue CPM-243. Details: Database picker = [value:10102, type:UNHANDLED, class:class com.exalate.basic.domain.hubobject.v1.BasicHubCustomField, uid:null, id:13400, locked:false, description:Customer Office=Organization, name:Customer Office]. Error line: Script8.groovy:32
Many thanks for your support
Cristina
That error is because you have a ‘throw new exception’ on line 32.
Remove this from your script and retry
Hi Francis,
yes, there was the :
“throw new Exception(“Database picker = ${dbpicker.properties}”)”
and now i’m back to the initial issue:
Text Customer Office is updated via custom listener listener on issue Update:
maybe i should use a loop transition instead listener? would it trigger the sync?
many thanks
Cristina
The solution is to add the customer office value into the replica.
We can use the same approach as you now use to extract the value from the field, but then using the outgoing sync processor.
Can you add the code of the listener into this question?
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.issue.ModifiedValue
import com.atlassian.jira.issue.util.DefaultIssueChangeHolder
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.CustomFieldManager
import com.atlassian.jira.issue.fields.CustomField
import com.onresolve.scriptrunner.db.DatabaseUtil
import com.atlassian.jira.issue.IssueManager
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.event.type.EventDispatchOption
import com.atlassian.jira.issue.fields.layout.field.FieldLayoutItem
import com.atlassian.jira.issue.fields.layout.field.FieldLayoutManager
import org.apache.log4j.Level
import org.apache.log4j.Logger
//def log = Logger.getLogger(getClass())
//log.setLevel(Level.DEBUG)
//get custom filed value
def issueManager = ComponentAccessor.getIssueManager()
def issue = event.issue
def customFieldManager = ComponentAccessor.getCustomFieldManager()
def fieldLayoutManager = ComponentAccessor.getFieldLayoutManager()
def textCf1 = customFieldManager.getCustomFieldObjects(issue).find {it.name == “Customer Office”}
def textCf2 = customFieldManager.getCustomFieldObjects(issue).find {it.name == “[Text] Customer Office”}
//log.warn(textCf1)
def ID=issue.getCustomFieldValue(textCf1)
//log.warn(ID)
def fieldLayoutItem = fieldLayoutManager.getFieldLayout(issue).getFieldLayoutItem(textCf1.id)
//if the field has a value on the issue
if(ID) {
//get the value as displayed on the Issue.
//We need to call .trim() to remove line breaks and spaces
def valueForDbPicker = textCf1.getViewHtml(fieldLayoutItem, null, issue) as String
//log.warn(valueForDbPicker?.trim())
//Set [Text] Customer Office to the selected value
if (textCf2) {
def changeHolder = new DefaultIssueChangeHolder()
textCf2.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(textCf2),valueForDbPicker?.trim()),changeHolder)
}
}
else {
//when the creation of the Support request is triggered by the JSD (ID has no value) via exalate
def valueForDbPicker = textCf2.getViewHtml(fieldLayoutItem, null, issue) as String
//log.warn(valueForDbPicker?.trim())
if (textCf1) {
def changeHolder = new DefaultIssueChangeHolder()
textCf1.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(textCf2),valueForDbPicker?.trim()),changeHolder)
}
}
Give following code a try - place it at the top of the outgoing sync.
Outgoing sync
import com.atlassian.jira.component.ComponentAccessor
import java.nio.charset.StandardCharsets
def ism = ComponentAccessor.getIssueManager()
def cfm = ComponentAccessor.getCustomFieldManager()
def flm = ComponentAccessor.getFieldLayoutManager()
def dbPickercf = cfm.getCustomFieldObject(issue.customFields."Customer Office".id)
def jIssue = ism.getIssueObject(issue.id as Long)
def fieldLayoutItem = flm.getFieldLayout(jIssue).getFieldLayoutItem(issue.customFields."Customer Office".id as String)
def dbPickerValue = dbPickercf.getViewHtml(fieldLayoutItem, null, jIssue) as String
replica.customKeys."Customer Office" = new String(dbPickerValue.bytes, StandardCharsets.UTF_8)
It will update the replica with the value of the database picker.
Because the replica is changed the sync will be triggered
Hi,
the good news is that YES, the sync is triggered;
BUT the result is that no value is sent via
replica.customKeys."Customer Office"
in the incoming connection on the Jira Service Desk i have:
issue.customFields.Organizations.value = replica.customFields."Customer Office"
i tried also
issue.customFields.Organizations.value = replica.customFields."Customer Office".value
but in this case i get "null pointer exception.
Spot the difference
replica.customKeys."Customer Office"
replica.customFields."Customer Office"
yes, i changed to
replica.customKeys."Customer Office" unfortunatelly the value is empty.
Can you check step by step, and see if the value is extracted from the database picker and added to the custom key, available on the incoming sync…
not sure how can i do the debug. with the throw exception?
Yes indeed.
It’s a bit of a hack, but very useful, because you then start a sync, throw an exception with the info required and see how things are being interpreted.
ok
step by step: the Customer Office has the right value;
something goes wrong with the “incoming” on the other side; here is my code:
//Organization= Customer Office
issue.customFields.Organizations.value = replica.customKeys."Customer Office"
> step by step: the Customer Office has the right value;
Is this on the incoming side? Can you validate that the customer office is set on the receiving end.
> something goes wrong with the “incoming” on the other side; here is my code:
Is this the organizations field of Service Desk?
Did you check
https://docs.idalko.com/exalate/display/ED/How+to+sync+Organizations+field+on+Jira+Service+Desk
Is the value set in the replica.customKeys.“Customer Office” available in the list of Organizations.
Did you try with a fixed value - does that work
Hi Francis,
sorry for the “silence” over the weekend - but here i am back
step by step
//Get Customer Office
import com.atlassian.jira.component.ComponentAccessor
import java.nio.charset.StandardCharsets
def ism = ComponentAccessor.getIssueManager()
def cfm = ComponentAccessor.getCustomFieldManager()
def flm = ComponentAccessor.getFieldLayoutManager()
def dbPickercf = cfm.getCustomFieldObject(issue.customFields."Customer Office".id)
def jIssue = ism.getIssueObject(issue.id as Long)
def fieldLayoutItem = flm.getFieldLayout(jIssue).getFieldLayoutItem(issue.customFields."Customer Office".id as String)
def dbPickerValue = dbPickercf.getViewHtml(fieldLayoutItem, null, jIssue) as String
def test = new String(dbPickerValue.bytes, StandardCharsets.UTF_8)
//throw new Exception("Database picker test= $test")
replica.customKeys."Customer Office" = test
the throw exception shows me that test has teh value that i have selected from ( in the database picker). but for some reason the
replica.customKeys."Customer Office" = test
puts in the customKeys an " empty value.
2. Yes, is this the organizations field of Service Desk. yes i have read and use it;
3. yes the value is also in the organization list
3. Yes, it works perfectly with a fixed hard coded value.
i dare to ask for a 30 minutes session with screen sharing; i could show you the step by step; doe sit work for you this afternoon, tomorrow morning (before 10:30?)
I think we are almost there …
For some reason the customKeys entry is not set.
Can you change ‘test’ into custOfficeValue
and then check with the exception that the replica.customKeys.“Customer Office” is set correctly
Also raise an exception on the incoming side. In the error details you can see what the content is of the remote replica.
Check if the customKeys is there.
replica.customKeys.“Customer Office” → OK
on the incoming side→ remote replica : OK
i validated with this code, ofcourse not commented:
//Organization= Customer Office
//throw new Exception(replica.customKeys."Customer Office")
issue.customFields.Organizations.value = replica.customKeys."Customer Office"
//throw new Exception(issue.customFields.Organizations.value)
still, the Organization Names remains empty:
I would expect on the receiving end - in the remote issues tab - a custom key ‘Customer Office’
Why is it showing ‘Organization Names’?
because i was giving you teh information from the sending JSW;
here is the receiving side:
So - if you assign ‘Toyota’ to the issue.customFIelds.Organizations.value, it works, but it doesn’t when you assign replica.customKeys.“Customer Office”
YES
i just tested again:
this is the code on the receiving side:
//Organization= Customer Office
//throw new Exception(replica.customKeys."Customer Office")
//issue.customFields.Organizations.value = replica.customKeys."Customer Office"
issue.customFields.Organizations.value = "Toyota"
//throw new Exception(issue.customFields.Organizations.value)
this is what i see on the “sending” side: (but also i see that the organization was set):
might it be cast issue?
Lets focus on the receiving end (where the organization needs to be set)
Can you try
issue.customFields.Organizations.value = [ replica.customKeys."Customer Office" ]
If that doesn’t work, try
issue.customFields.Organizations.value = replica.customKeys."Customer Office" as String
none of them works.
Hi Francis,
any new ideas to try? (old community)
Thank you
Cristina
Hi Francis,
Back to you - we’ve just setup a new Service Desk server preparing the rollout. Please help to get the “organizations” synced with “Customer Office”.
Maybe we could plan the call-sharing for 20 minutes or so?
Many thanks
Cristina
Can you check if the request type is set on the ticket where you want to set the organization?
Hi Francis,
yes, the request type is set:
Many thanks
Cristina
Cristina Mogos
Lets have a look at your configuration
Can you raise an issue in our portal (here), and share a support zip after reproducing the case.
Probably something I’m misunderstanding
Hi Francis Martens (Exalate)
i have created https://support.idalko.com/servicedesk/customer/portal/8/EASE-6837
Best and thanks
Cristina