Confluence has been updated to ver 6.15.9. In case of any troubles, please contact infra team.

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

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

    2 answers

    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 (iDalko)

        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 (iDalko)

        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 (iDalko)

        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 (iDalko)

        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 (iDalko)

        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 (iDalko)

        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 (iDalko)

        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 (iDalko)

        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 (iDalko)

        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 (iDalko)

        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 (iDalko)

        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 (iDalko)

        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 (iDalko)

        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 (iDalko)

        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 (iDalko)

        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 (iDalko)

        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 (iDalko)

        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 (iDalko)



        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 (iDalko)

        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 (iDalko)

        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 (iDalko)

        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 (iDalko)

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