1
0
-1

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


    CommentAdd your comment...

    3 answers

    1.  
      1
      0
      -1

      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

        CommentAdd your comment...
      1.  
        1
        0
        -1

        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

        1. Cristina Mogos

          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.

          1. there is a database picker (Script runner)  customer filed where i put the database select from Jira
          2. when creation of the ticket, the value of the field from point 1 is copied to a Custom Filed: Customer Office → Customer Office
          3. 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

        2. Francis Martens (Exalate)

          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?

        3. Cristina Mogos

          Hi Francis,


          yes, i have  custom listener on "Issue Update".


          Best and thanks

          Cristina

        4. Francis Martens (Exalate)

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


        5. Cristina Mogos

          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

          Many thanks

          Cristina

        6. Francis Martens (Exalate)

          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?

        7. Cristina Mogos

          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

        8. Francis Martens (Exalate)

          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.


        9. Cristina Mogos

          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


        10. Francis Martens (Exalate)

          There is some problem with the image ...

        11. Cristina Mogos

          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

        12. Francis Martens (Exalate)

          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

        13. Cristina Mogos

          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

          "

        14. Cristina Mogos

          this value: 10101 is the ID from the database :

          "

          select id,name FROM database_tabel

          where lower(name) like lower(?) || '%'

          "


        15. Francis Martens (Exalate)

          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?


        16. Cristina Mogos

          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

        17. Cristina Mogos

          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

        18. Francis Martens (Exalate)

          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


        19. Cristina Mogos

          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

        20. Francis Martens (Exalate)

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

        21. Cristina Mogos

          Hi Francis,


          yes, there was the :

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


          and now i'm back to the initial issue:

          1. Editing the Customer Office database picker does not trigger the sync (as it is not in the connections rules)
          2. 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

        22. Francis Martens (Exalate)

          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?


        23. Cristina Mogos
          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)    
             }
          }
        24. Francis Martens (Exalate)

          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)
        25. Francis Martens (Exalate)

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

        26. Cristina Mogos

          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.



        27. Francis Martens (Exalate)

          Spot the difference 

          • Outgoing sync
          replica.customKeys."Customer Office"
          • Incoming sync
          replica.customFields."Customer Office"



        28. Cristina Mogos

          yes, i changed to

          replica.customKeys."Customer Office" unfortunatelly the value is empty. 

        29. Francis Martens (Exalate)

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

        30. Cristina Mogos

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

        31. Francis Martens (Exalate)

          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.



        32. Cristina Mogos

          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"
        33. Francis Martens (Exalate)

          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



        34. Cristina Mogos

          Hi Francis,


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

          step by step

          1. 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?)

        35. Francis Martens (Exalate)

          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.


        36. Cristina Mogos

          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:



        37. Francis Martens (Exalate)



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

        38. Cristina Mogos

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

          here is the receiving side:


        39. Francis Martens (Exalate)

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



        40. Cristina Mogos

          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?


        41. Francis Martens (Exalate)

          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



        42. Cristina Mogos

          none of them works.

        43. Cristina Mogos

          Hi Francis,


          any new ideas to try? (smile)


          Thank you

          Cristina

        44. Cristina Mogos

          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

        45. Francis Martens (Exalate)

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

        46. Cristina Mogos

          Hi Francis,


          yes, the request type is set:



          Many thanks

          Cristina

        47. Francis Martens (Exalate)

          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



        CommentAdd your comment...