How to Exalate Database Picker Custom Field (script runner)?

xl8bot · November 4, 2024, 6:56am

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

xl8bot · November 4, 2024, 6:56am

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:

Outgoing script

replica.customKeys."Customer Office" = issue.customKeys."Customer Office"

Incoming script

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

xl8bot · November 4, 2024, 6:56am

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

xl8bot · November 4, 2024, 6:56am

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

Comments:

Cristina Mogos commented on 02 November 2020

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.

there is a database picker (Script runner) customer filed where i put the database select from Jira
when creation of the ticket, the value of the field from point 1 is copied to a Custom Filed: Customer Office → Customer Office
i exalate the Customer Office

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

Francis Martens (Exalate) commented on 04 November 2020

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?

Cristina Mogos commented on 04 November 2020

Hi Francis,

yes, i have custom listener on “Issue Update”.

Best and thanks

Cristina

Francis Martens (Exalate) commented on 05 November 2020

The reason that exalate is not kicking in is because there is no Jira issue event raised.
Try to find out why that is.

Cristina Mogos commented on 05 November 2020

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

Francis Martens (Exalate) commented on 05 November 2020

What happens is (IMO)

The database field is changed
The update event is being processed
The exalate captures AND the script field is started at the same time
The outgoing sync of the exalate checks the issue, sees that nothing is changed, and the sync is skipped
The script field is then updated, and no update event is generated.

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?

Cristina Mogos commented on 06 November 2020

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

Francis Martens (Exalate) commented on 06 November 2020

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.

Cristina Mogos commented on 10 November 2020

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

Francis Martens (Exalate) commented on 10 November 2020

There is some problem with the image …

Cristina Mogos commented on 11 November 2020

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

Francis Martens (Exalate) commented on 11 November 2020

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

Cristina Mogos commented on 11 November 2020

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

"

Cristina Mogos commented on 11 November 2020

this value: 10101 is the ID from the database :

"

select id,name FROM database_tabel

where lower(name) like lower(?) || ‘%’

"

Francis Martens (Exalate) commented on 11 November 2020

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?

Cristina Mogos commented on 12 November 2020

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

Cristina Mogos commented on 12 November 2020

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

Francis Martens (Exalate) commented on 12 November 2020

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

Cristina Mogos commented on 12 November 2020

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

Francis Martens (Exalate) commented on 13 November 2020

That error is because you have a ‘throw new exception’ on line 32.
Remove this from your script and retry

Cristina Mogos commented on 13 November 2020

Hi Francis,

yes, there was the :

“throw new Exception(“Database picker = ${dbpicker.properties}”)”

and now i’m back to the initial issue:

Editing the Customer Office database picker does not trigger the sync (as it is not in the connections rules)
Text Customer office is updated; unfortunately its update does not trigger the sync;

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

Francis Martens (Exalate) commented on 13 November 2020

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?

Cristina Mogos commented on 13 November 2020

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

Francis Martens (Exalate) commented on 13 November 2020

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)

Francis Martens (Exalate) commented on 13 November 2020

It will update the replica with the value of the database picker.
Because the replica is changed the sync will be triggered

Cristina Mogos commented on 13 November 2020

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.

Francis Martens (Exalate) commented on 13 November 2020

Spot the difference

Outgoing sync

replica.customKeys."Customer Office"

Incoming sync

replica.customFields."Customer Office"

Cristina Mogos commented on 13 November 2020

yes, i changed to

replica.customKeys."Customer Office" unfortunatelly the value is empty.   
  
![](/download/thumbnails/20120644/image2020-11-13_15-30-19.png?version=1&modificationDate=1605277820044&api=v2)

Francis Martens (Exalate) commented on 13 November 2020

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…

Cristina Mogos commented on 13 November 2020

not sure how can i do the debug. with the throw exception?

Francis Martens (Exalate) commented on 13 November 2020

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.

Cristina Mogos commented on 13 November 2020

ok

Cristina Mogos commented on 13 November 2020

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"

Francis Martens (Exalate) commented on 14 November 2020

> 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

Cristina Mogos commented on 16 November 2020

Hi Francis,

sorry for the “silence” over the weekend - but here i am back

step by step

outgoing script:

//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?)

Francis Martens (Exalate) commented on 16 November 2020

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.

Cristina Mogos commented on 16 November 2020

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:

Francis Martens (Exalate) commented on 16 November 2020

I would expect on the receiving end - in the remote issues tab - a custom key ‘Customer Office’
Why is it showing ‘Organization Names’?

Cristina Mogos commented on 16 November 2020

because i was giving you teh information from the sending JSW;

here is the receiving side:

Francis Martens (Exalate) commented on 16 November 2020

So - if you assign ‘Toyota’ to the issue.customFIelds.Organizations.value, it works, but it doesn’t when you assign replica.customKeys.“Customer Office”

Cristina Mogos commented on 16 November 2020

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?

Francis Martens (Exalate) commented on 16 November 2020

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

Cristina Mogos commented on 16 November 2020

none of them works.

Cristina Mogos commented on 19 November 2020

Hi Francis,

any new ideas to try? (old community)

Thank you

Cristina

Cristina Mogos commented on 26 November 2020

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

Francis Martens (Exalate) commented on 27 November 2020

Can you check if the request type is set on the ticket where you want to set the organization?

Cristina Mogos commented on 30 November 2020

Hi Francis,

yes, the request type is set:

Many thanks

Cristina

Francis Martens (Exalate) commented on 01 December 2020

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

Cristina Mogos commented 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

Topic		Replies	Views
Is it possible to sync "Database Picker Custom Field" from Scriptrunner? General Questions exalate , jiraserver , migrated_question , scriptrunner , db	1	2	November 4, 2024
Unable to synchronize custom field Version Picker (single version) type General Questions jira-cloud , jiraserver , migrated_question	2	7	November 4, 2024
Help with synchronizing a uniqe Custom Field General Questions exalate , migrated_question	1	0	November 4, 2024
How to stop synchronization custom field General Questions onpremise-jira , migrated_question	2	1	November 4, 2024
Sync between user picker field and reporter field General Questions exalate , jira-cloud , migrated_question	1	1	December 4, 2024

How to Exalate Database Picker Custom Field (script runner)?

Comments:

Related topics