Jira Connector - Source Code
<ApiConfig Name="Jira"
Slug="jira-connector"
Version="9"
EngineVersion="16"
Id="288c083b-6edb-4705-851b-80ecd6357347"
Desc="Jira Connector can be used to integrate Jira and your defined data source, e.g. Microsoft SQL, Oracle, Excel, Power BI, etc. Get, write, delete Issues, Users, Worklogs, Comments just in a few clicks!"
Logo="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAJMAAACZCAYAAAArfghzAAAJk2lDQ1BERUxMIFVQMzIxNlEgQ29sb3IgUHJvZmlsZSwgRDY1MDAAAHicnZZ3UFTJFof73js5AMMMQ4YhDTnDMIDkOATJUVRwyJkhipgQcQUURUQElAURCQom4ioioBhYFBRQMQyyCCjr4iqigvJgfVXuq31Vr+r9qrrPV6dP1znd/UcfAAiWVrbWdrAQAHHxKTxPB2uGf0AgA/sCoAEdCAAmoIRwkxNdvey9waps7VxcwD/0cRRAa/aB5to6Cd+rNMdIeQdw+2xuhVUe+Gf8f4gQGpbMXbXLqyOHm8hLAQDatcrs9JTENW5fZRpvtahVHljjiO/MX+Mt3/nDXzHenjYAwDgAcKSIv/GWvzE3MiR0NSVtNV7j33nXpLZWN8PHXV9Pl+XBsEmITeAx3HkJ4VGxYRoMW5ahjs7/OMT/q5SwjJQ1a5OQuJUXFRGZwlDhqjL0dHQNGbZhsbEMTjxX67/tW3uj7/Tj7n/4TrMA8FktGdv1w6cVAEDFZQAodT98CvYACEgB0NHPTeWlffeh1iY0IABBQAPiQAYoABWgCfSAETAFlsAOOAE34A0CwCbABZEgDvBAOsgCu0EuyAeHwFFQBipBDagHTeAiaANXwHVwE9wF98EIGAd8MAXegHnwESxBEISFyBAVEodkISVIHdKD2JA5ZAe5QJ5QABQMRUDxUCqUBe2B8qEiqAyqguqhC1AHdB26DQ1Bj6EJaBb6E/oCIzAJpsHSMBPWhtmwFewMe8Mb4Qg4Cc6Ec+CDcClcDZ+FW+Hr8F14BObDb+AFBCBEhI7IIZoIG7FB3JBAJBzhITuQPKQEqUaakE6kH3mA8JE55DMKg6KiGChNlCnKEeWD4qKSUDtQBagyVB2qFdWHeoCaQM2jvqHJaCm0OtoEzUH7oyPQ6ehcdAm6Ft2CvoEeQU+hP2IwGDpGGWOEccQEYKIx2zAFmBOYZkw3ZggziVnAYrHiWHWsGdYNG4JNweZij2PPYq9hh7FT2E84Ik4Wp4ezxwXi4nHZuBJcA64LN4ybxi3hhfBKeBO8Gz4UvxVfiK/Bd+Lv4afwSwQKQZlgRvAmRBN2E0oJTYQbhKeE90QiUZ5oTPQgRhF3EUuJ54m3iBPEzyRhkhrJhhRESiUdJJ0hdZMek96TyWQm2ZIcSE4hHyTXk3vJz8mfBKgCWgIcgVCBnQLlAq0CwwJvBfGCSoJWgpsEMwVLBC8J3hOcE8ILMYVshEKEdgiVC3UIjQktUKgUXYobJY5SQGmg3KbMCGOFmcJ2wqHCOcKnhHuFJ6kIVYFqQ+VS91BrqDeoUzQMTZnGoUXT8mnnaIO0eRFhEQMRX5EMkXKRqyJ8OkJn0jn0WHoh/SJ9lP5FVFrUSjRMdL9ok+iw6KKYpJilWJhYnliz2IjYF3GGuJ14jPhh8TbxZxIoCTUJD4l0iZMSNyTmJGmSppJcyTzJi5JPpGApNSlPqW1Sp6QGpBakZaQdpBOlj0v3Ss/J0GUsZaJlimW6ZGZlqbLmslGyxbLXZF8zRBhWjFhGKaOPMS8nJecolypXJTcotySvLO8jny3fLP9MgaDAVghXKFboUZhXlFV0VcxSbFR8ooRXYitFKh1T6ldaZCoz/Zj7mG3MGWUxZY5ypnKj8lMVsoqFSpJKtcpDVYwqWzVG9YTqfTVYzVAtUq1c7Z46rM5Sj1I/oT6kgdYw1ojXqNYY0yRpWmmmaTZqTmjRtVy0srXatN5qK2oHah/W7tf+pmOoE6tTozOuK6zrpJut26n7p56aHlevXO+hPlnfXn+nfrv+OwN1gzCDkwaPDKmGrob7DHsMv7KMWDxWE2vWSNEo2KjCaIxNY7uzC9i3jNHG1sY7ja8YfzZhmaSYXDT5w1TTNMa0wXRmnfK6sHU16ybN5M1CzKrM+OYM82Dzn835FnIWIRbVFi8tFSxDLWstp61UraKtzlq9tdax5lm3WC/amNhst+m2RWwdbPNsB+2E7Xzsyuye28vbR9g32s87GDpsc+h2RDs6Ox52HONIc7ices68k5HTdqc+Z5Kzl3OZ80sXNReeS6cr7OrkesT16Xql9fHr29yAG8ftiNszd2X3JPdfPDAe7h7lHq88dT2zPPu9qF6bvRq8Pnpbexd6j/uo+KT69PgK+gb51vsu+tn6Ffnx/bX9t/vfDZAIiApoD8QG+gbWBi5ssNtwdMNUkGFQbtDoRuWNGRtvb5LYFLvp6mbBzSGbLwWjg/2CG4KXQ9xCqkMWtnC2VGyZ59pwj3HfhFqGFofOhpmFFYVNh5uFF4XPRJhFHImYjbSILImci7KJKot6F+0YXRm9GOMWcyZmJdYvtjkOFxcc1xEvHB8T35cgk5CRMJSonpibyE8ySTqaNM9z5tUmQ8kbk9tTaKsf40CqSure1Ik087TytE/pvumXMigZ8RkDW9W27t86nWmfeXobaht3W0+WXNburIntVturdkA7tuzo2amwM2fn1C6HXXW7Cbtjdv+arZNdlP1hj9+ezhzpnF05k3sd9jbmCuTycsf2me6r/An1U9RPg/v19x/f/y0vNO9Ovk5+Sf5yAbfgzgHdA6UHVg6GHxwsZBWePIQ5FH9o9LDF4boiSlFm0eQR1yOtxYzivOIPRzcfvV1iUFJ5jHAs9Ri/1KW0/bji8UPHl8siy0bKrcubK6Qq9lcsngg9MXzS8mRTpXRlfuWXn6N+flTlUNVazawuOYU5lXbqVY1vTf9p9un6Wona/NqvZ+LP8Os86/rqjerrG6QaChvhxtTG2bNBZ++fsz3X3qTZVNVMb84/D86nnn99IfjC6EXniz2X2JeaLitdrmihtuS1Qq1bW+fbItv47QHtQx1OHT2dpp0tv2j9cuaK3JXyqyJXC7sIXTldK9cyry10J3bPXY+4PtmzuWe817/3YZ9H3+AN5xu3btrf7O236r92y+zWldsmtzvusO+03WXdbR0wHGj51fDXlkHWYOs9o3vt943vdw6tG+oathi+/sD2wc2HnId3R9aPDI36jD4aCxrjPwp9NPM49vG7J2lPlsZ3PUU/zXsm9KzkudTz6heqL5r5LP7VCduJgZdeL8cnuZNvfkv+bXkq5xX5Vcm07HT9jN7MlVn72fuvN7yeepP4Zmku93fK7xVvVd5e/sPyj4F5//mpd7x3K38WvBd/f+aDwYeeBfeF5x/jPi4t5n0S/1T3mf25/4vfl+ml9GXsculX1a+d35y/PV2JW1lJDtfX+6sVgEi2q63Ai5WV98zVXqIYgK+FKytLVSsrX6sBQMYB6E79F4bKmf4rzD3rAAAACXBIWXMAABYlAAAWJQFJUiTwAAANFUlEQVR4nO3d23MT1x3A8WNbF+tqS/IVGwwx0MlMZ/rUJ17yH/Sxj4FCp02bBpIwoSkQmVsbWpqQ0KRpRxp47WPf+gf0qU/pS2YaBDZgW8YXyZZ1tWyp+xM6Qlrvrnals7vnnD3fGWMdWdi70meOVitp5arX60ik3dErucsv7obv2b0ctOeyewFoL/rO8n9i09M//sEd9MP/XUVn7V4emhOYNAqdf/rd1Nzcm4NDQzB8WwKFBCj1BCaVAFJsegpDwglQGglMCgXOPU6FI9F5r8+v9GMBSiWBSRZAcrnd8+FYTOtiApRCAlNbAKlaLs8fOfGGnosLULIEpmYAqZjLzc+eOo1k20laCVBtCUzoNaTgyCjyh0JG/7sA1czxmDAkOD1+9Givv0aAQg7H1A4JNrjdHk8/v87xoByLqR0SFJs+QuLXOhqUIzHJIRGYldpzLCjHYZJDgsJRzX1KveRIUI7CpAQJZqQeHsHpyXGgHINJCRI0OjFp5p91FChHYFKDBPlDQbP/vGNAcY9JCxLcxak8mUs6R4DiGpMWJMhnzraSWtyD4hZTN0iQP2gpJohrUFxi0gMJcpHbt2QkbkFxh0kvJMikXQJ64hIUV5iMQCK4x7vXuAPFDSYjkCCXx2vm4uiNK1BcYAr9LJUq7e7qhkRZ3IBiHlP4wtNUMZ+fHxgYaIz1vqnUgp2VRuICFNOYwj9fTJWakIDQQPN8Rt+lzDwoZjGN/uJZqlwozCMJUgsREqDsjElMkV8+b0Bq3bUhJEBREHOYou8sp8rFYseMBBkFVTs4MG8h+49JUExhiv16JVUpFRVnJPm4G6hysWTegpKJOVDMYBp7Ny1BKmnOSPKxuMuzNiYwjb+3ltorl3TNSPKxGihphjNrcUnHDCjqMU1cXJcglQ3NSPKxEijYZoIvA+/etTMmQFGNafLSRuO9/73MSPKxEihpQ97OJ3uNRj0oajFNvb+ZqlYqr2akNgkkQZXyuyxhgqgGRSWm6Q8zqf29vc4ZyQRQFfof0SlFLSjqMM1czjYgKW4jEQZVKuSJLruFUQmKKkwzH+2kqtWq9jYSQVD1Wg3ld7YRHP2EwagDRQ2mo1dyqYMmJLW7pdaYIKjCzg6rmCCqQFGB6djH+dT+/n5rRoKsAlXI5fpefpujBpTtmOZ+V0gdNCHp3XBujQmAgru6XCaDwtFo/ytjX1SAshXT8Wul1MHBgeqMJB+bBSqfzbKOCbIdlG2YTlwvp2pNSL0+tG+N+wRVLhZQKZ9HviBVr77sJVtB2YLpjU8qqVqtpntGko/NAJVdf8kDJsg2UJZjml+opur1uuEZST4mDQqeWoH9Tr6AANVrlmI6eWO/BQlHE6itdBrNnjyle30oz3JQlmE6dauWkr71PSPJxyRBVSsVtLO1iUZiY7rXi/IsBWUJptO36w1Iai/+73dMEtT2xgYKhMJ2HYfAjCwDZTomaUW+k2aj1hskuyLocUwKFOx3evniOZqZP9l95djJElCmYpJW4JH07U04bWjvdo9jUqDg7m5jZRmNz8xqrh9jmQ7KNExNSG+3n8cSKHjOzhcIoOBoRHH9GM1UUKZgUoKEYwnU5upq42W9fmkbiqNMA0UckxYkHFOgVlbR1HEP8gwPa60Sa5kCiigmPZBwrICq12vwVMs/JEw/7b5WTEUcFDFMRiDhWADl8fvja5+P3ZTW71/S8GH3tWIqoqCIYOoFEo5mUF6fL5795thNOE+6wh/BFY8EKNX6xtQPJByNoDwSpMzXszfbl1OA0q4vTCQg4WgCJW0fxbe+mumAhBOg1OsZE0lIOBpAuSVImw+mFSHhBCjlesJkBiScnaA8w974+pdTmpBwAtThDGMyExLODlAurwTpiwldkHACVGeGMFkBCWclKLfHE1+7P24IEk6Aep1uTFZCwlkByiVBSn8W6wkSToB6lS5MdkDCmQkKZqTVe5G+IOEEKB2Y7ISEMwOUy+2Or/xplAgknNNBaWKiARKOJCiAtHw3TBQSzsmgVDHRBAlHAtSQyxV/8WnIFEg4p4JSxEQjJFw/oFwSpGe/D5gKCedEUIcw0QwJ1wuowaGh+LM7fksg4ZwGqgMTC5BwRkANSZAWbw1bCgnnJFAtTCxBwukBNTg4GH9602sLJJxTQDUwSWfcR4xBwmmBAkhPFty2QsJxDmpJWr8Fl3TiLemMi3YvUT8pgZLOi6fiLiog4TgGFYd7NpiZztq9JCRqByUN4o+vD1IFCccxqAXA9JbdS0GqJqj499cGqISE4xTUccA0Z/dSEOyftEPCNUFdkE6esXtZSAWY/it9/cjuBSHUT6Qb6CzcUHYvSLdO364npG9nOu6eGQ8wfYv4wQQ9bD5cfWT3gqh16lYtger1863DC/EB6lvAtIAY3S2gEbWgTt3YT9SakCD4lwNQO9LXfZd0hS9JV/w5xNfGIEQdqPmFaqJeq7VmJBwHoC6Bo8ZOS04fXUDUgJqP7yUODg4OzUg4hkGdw9dv6+kUAcq8TnxSSdT291VnJPmYIVDn2q/Xjid6BSjynbhWSuw3IUFqgORjBkCdk1+fh16CIkCR6/jVYmK/WtU9I8nHFIM6BAlSfHGcANV/c1cLiere3nl8SFi9gORjCkEpQoJUX7YrQPXesY/zEqTKqxmpTQkHoFQhQZpvKBCgjHf0t7lEtVI5j9q3kfgApQkJ6vpWJwFKf7NXdhJ75XJjRmpHwQGorpAgXW/CFKC6N/PRdmKvVGrNSBAnoHRBgnS/PVyAUu/I5WyiUioempEgxkHphgQZOnCFAHW46Q8ziUqxoDojyccMgTIECTJ8SB0B6nVTH2wmyoVC1xlJPmYAlGFIUE8H+xKgJEjvbyRK+bzuGUk+phhUT5Cgng9D6GRQExfXJUi7hmck+ZhCUD1Dgvo6QKoTQU289zJR3M31PCPJxxSB6gsS1Pehm50Eavw36UQhl+t7RpKPKQDVNySIyEHlnQBq7N3VRGFnh9iMJB/bCIoIJIjYx13wDGry0vqF3Wz2DOkZST62ARQxSBDRD+LhFZQ/FD6T29pCtVqtMeYEFFFIEPGPCOMRFHw82NTxE2htaZEXUMQhQaZ8eCGvoKKTU2hzdaXzRm+7DCOgTIEEmfaxqjyCCkYiqFrdQzsbG6yCMg0SZOoHPvMIKjIxicqFAqoUi6yBSpr9ClPTP4qeR1DwqeIrT1KNj61nAtQrSBe6rlifmY4J4g2Uy+NBo+MTKLOWPnyjt12OBlADg4PJ768NmA4JsgQTxBuokbExtJvNoGqlQi2oIQnS4+uDlkCCLMME8QYqNn0EpRefNk7TBmpwaCj5OO6yDBJkKSaIJ1C+YBAN+wOoXCw0xrSAcrlcyScLbkshNf6u1X8Q4glUZHISrT590hrbDWrI7U4+ueGxHBJkCyaIF1AwO/kCAVQqFFrn2QXKLUFavOm1BRJkGyaIF1DBSBSV8nntG13lZ/2OMSi325NcvO2zDRJkKyaIB1DhaBRtrix37HeCrALl8XqTS3f8tkKCbMcE8QAqEB5p7CroOouo/KzXsds7nHx2J2A7JIgKTBDroAIjIyiX2WqctgqUZ3g4+fwPQSogQdRgglgGFRwdRfX665vdbFAeny/54tMwNZAgqjBBLIPyBUPShvhua2wWKK/fn1y+O0IVJIg6TBCroPyhYAOTmTOU1x9IrvxxlDpIEJWYIBZBwcyEULpx2gxQw4FgcvVehEpIELWYINZAub3ejjFJUMPBYDL95yi1kCCqMUEsgXJ7PIfOIwHKHwol1z4boxoSRD0miCVQXp8PVUqljvP6AeULhZNrn49TDwliAhPECqjBIeWrtBdQgfBIcv2LCSYgQcxgglgBpZYRUIERCdKXk8xAgpjCBNEOyu31oFJe/ed6QAVHR5MbD6aZggQxhwmiGZTSRrg8LVChSCS5+ZcjzEGCmMQE0Qqquren63JKoEKRaHLrqxkmIUHMYoJoBFWt6MMEtYMKR6PJzF9nmYUEMY0JohGUkQBUOBZLZr85xjQkiHlMEMugRmJjye2/zTEPCeICE0QLqPZXDXSrAenvfECCuMEE2Q2qdnCg+7K8QYK4wgTZCapcLOq6HI+QIO4wQXaB0nMXxyskiEtMkB2girsau74R35AgbjFBVoPSmpl4hwRxjQmyClR+e1v1Z06ABHGPCbICVH5HGZNTIEGOwASZDUppZnISJMgxmCCzQDWOES7bx+Q0SJCjMEFmgNrZ2uoYOxES5DhMEElQxd3djkdxToUEORITRArUVjrdOu1kSJBjMUH9gmqflZwOCXI0JqgfUHhWEpBe5XhMUC+gsuvrjVlJQHqdwNTMCCh4nfdWelVAkiUwtaUX1NrSEgqNRgQkWQKTrG6gYEaCY0gKSIcTmBRSAwV7uvf3qgKSSgKTSnJQlVIRVcqlBxKk92xdMIoTmDTCoCRID3OZzIPM17MCkkYCU5cAVOxX2ZQE6d92Lwvt/R+ac9dhflI3hQAAAABJRU5ErkJggg=="
>
<VersionHistory>
<Change Ver="9" Date="2024-10-03" Type="Fix">Create new issue with Labels fails (Its not treated as Raw fragment)</Change>
<Change Ver="9" Date="2024-10-03" Type="New">Allow to use Formatted Comment (Raw JSON) in create_worklog or update_worklog operations</Change>
<Change Ver="9" Date="2024-10-03" Type="New">Add Environment and EnvironmentFormatted columns (Environment was null in Get Issues)</Change>
<Change Ver="9" Date="2024-10-03" Type="New">Change BodyContentText, Description, Comment datatype to DT_NTEXT from DT_WSTR in get_issues and get_worklogs endpoints to allow larger tahn 2000 chars in ETL tools like SSIS</Change>
<Change Ver="9" Date="2024-10-03" Type="New">Change Environment datatype to DT_NTEXT from DT_WSTR in create_issues and update_issues endpoints to allow larger tahn 2000 chars in ETL tools like SSIS</Change>
<Change Ver="9" Date="2024-10-03" Type="New">Change Labels to DT_WSTR rather than DT_NTEXT in get_issues endpoint</Change>
<Change Ver="9" Date="2024-10-03" Type="New">Allow to Update/Insert formatted values for Custom Paragraph (TextArea) Field (e.g. DescriptionFormatted)</Change>
<Change Ver="9" Date="2024-10-03" Type="New">Add new AggregateProgressPercent feild in get_issues endpoint.</Change>
<Change Ver="9" Date="2024-10-03" Type="Fix">Clicking on [Show Advanced Parameters] checkbox throws error for Fields table / endpoint - Invalid ConnectionString format.</Change>
<Change Ver="9" Date="2024-10-03" Type="Fix">Change Custom Paragraph fields (e.g. Long Summary) to DT_NTEXT (more than 2000 chars) so ETL tools like SSIS works fine for longer string.</Change>
<Change Ver="9" Date="2024-10-03" Type="Fix">Encode value used for textarea field INSERT / UPDATE (i.e. Description) - If you include double quote, new line, tab, slash etc in value error parsing JSON.</Change>
<Change Ver="9" Date="2024-10-03" Type="Fix">Improve Speed for metadata fetch (added UseRawCache to avoid multiple API calls).</Change>
<Change Ver="9" Date="2024-10-03" Type="Fix">Some fields like DueDate detected as datetime (i.e. DT_DATE) rather than date (i.e. DT_DBDATE - without time part). DT_DATE can return date and time both. Use DT_DBDATE if field should just return date (no time part)</Change>
<Change Ver="9" Date="2024-10-03" Type="Fix">BREAKING: Improve Speed by reducing number of unused columns from the output (e.g. customfields_value only needed for option fields, customfields_values only needed for array fields, customfields_text only needed for paragraph fields). After this change some of the unsed columns from your ETL Mappings / Reporting tools may be removed or throw error about column is missing, just ignore that warning / error becuase these unwanted columns were always set as null. Refresh your metadata and you will see number of custom field columns reduced drastically from you mappings.</Change>
<Change Ver="9" Date="2024-10-03" Type="New">Allow to Update/Insert raw values for Custom Paragraph (TextArea) Field (e.g. set customfield_1234_text = 'your long description').</Change>
<Change Ver="9" Date="2024-10-03" Type="New">Allow to Update/Insert raw values for Custom Option / Array / User / Team Field (e.g. Radio / Checkboxes / List Multiselect). (e.g. set customfield_1234 = '[{"value":"AA"}, {"value":"BB"}]').</Change>
<Change Ver="9" Date="2024-10-03" Type="New">Allow to Update/Insert value for Custom Option Field (Dropdown / Radio) by value or option id. To update by Option entry id use customfield_nnnnn_value='BBB' and to update by id customfield_nnnnn_id = '100023').</Change>
<Change Date="2024-07-12" Type="New">Add option to Continue on 404 Error (Record not found) on UPDATE / DELETE operations</Change>
<Change Date="2024-07-12" Type="New">Updated many examples</Change>
<Change Date="2024-07-12" Type="New">Add JQL Parameter examples and dropdown options</Change>
<Change Date="2024-07-12" Type="Fix">Added support for Id field in WHERE clause for SELECT / DELETE / UPDATE operations (now you can call FROM YourTable WHERE Id=1234).</Change>
<Change Date="2024-07-12" Type="Fix">Some fields (e.g. Summary) are too small for ETL tools like SSIS.</Change>
<Change Date="2024-04-25" Type="New">Added option to use PAT (Personal Access Token) for Authentication rather than userid / password.</Change>
<Change Date="2024-04-19" Type="New">Added option to choose custom domain in service URL.</Change>
<Change Date="2023-07-13" Type="New">Custom fields not listed correctly after you select them.</Change>
<Change Date="2023-05-23" Type="New">Added support for OAuth.</Change>
<Change Date="2023-05-23" Type="New">Added Lookup Support for Issues so now you can search by many Keys.</Change>
<Change Date="2023-05-23" Type="New">Improved speed by changing maxResults to 100 and use PagingMaxRowsExpr to detect total rows (one less request in pagination).</Change>
<Change Date="2022-05-02" Type="New">Added new endpoints get_custom_fields, get_custom_field_contexts and get_custom_field_option_for_context.</Change>
<Change Date="2022-02-28" Type="New">Added new endpoints get_changelogs, get_changelog_details and get_changelogs_by_ids.</Change>
<Change Date="2022-02-28" Type="New">Updated the Issue table [WorkRatio] column data type to String as it contains long number value (e.g.9117xxxxxxxxxxx2983).</Change>
<Change Date="2022-02-21" Type="New">Added customfield value support for single / multi value fields. For multi select you can see comma separated values now.</Change>
<Change Date="2021-03-29">Initial version</Change>
</VersionHistory>
<ServiceUrls>
<ServiceUrl Name="V3" Url="https://[$Subdomain$].atlassian.net/rest/api/3" />
<ServiceUrl Name="V3 (custom domain)" Url="https://something.mycompany.com/rest/api/3" Desc="If you added custom domain then enter domain and API URL here (V3 recommended)"/>
<ServiceUrl Name="V2 Legacy - Some Operations wont work" Url="https://[$Subdomain$].atlassian.net/rest/api/2" />
<ServiceUrl Name="V2 Legacy - Some Operations wont work" Url="https://something.mycompany.com/rest/api/2" Desc="If you added custom domain then enter domain and API URL here (V3 recommended)"/>
<ServiceUrl Name="V3 OAuth" Url="https://api.atlassian.com/ex/jira/[$OrganizationId$]/rest/api/3" Desc="Example: https://api.atlassian.com/ex/jira/c6613685-699f-46dc-8013-f487134bc9fd/rest/api/3"/>
</ServiceUrls>
<Auths>
<Auth Name="Http" Label="API Key based Authentication" Type="Http" ConnStr="CredentialType=Basic;" TestEndPoint="get_fields">
<Params>
<Param Name="Subdomain" Required="True" Label="Subdomain" Desc="Your company's Atlassian subdomain. Subdomain parameter is ignored this if you choose custom domain in service URL" />
<Param Name="UserName" Required="True" Label="Atlassian User Name (email)" HelpLink="" />
<Param Name="Password" Required="True" Label="API Key" HelpLink="https://id.atlassian.com/manage-profile/security/api-tokens" Secret="True" Desc="Get your API key by going to https://id.atlassian.com/manage-profile/security/api-tokens" />
<Param Name="CustomColumnsRegex" Template="CustomColumnsRegexParam" />
</Params>
<Notes>
<![CDATA[Firstly, login into your Atlassian account and then go to your Jira profile:
<p />
<ol>
<li>Go to <a target="_blank" href="https://id.atlassian.com/manage-profile/security">Profile > Security</a>.</li>
<li>Click <a target="_blank" href="https://id.atlassian.com/manage-profile/security/api-tokens">Create and manage API tokens</a>.</li>
<li>Then click <b>Create API token</b> button and give your token a label.</li>
<li>When window appears with new API token, copy and use it in this connection manager.</li>
<li>That's it!</li>
</ol>
]]>
</Notes>
</Auth>
<Auth Name="PAT" Label="Personal Access Token (PAT) Authentication" Type="Http" ConnStr="CredentialType=Token;" TestEndPoint="get_fields" HelpLink="https://confluence.atlassian.com/enterprise/using-personal-access-tokens-1026032365.html">
<Params>
<Param Name="Subdomain" Required="True" Label="Subdomain" Desc="Your company's Atlassian subdomain. Subdomain parameter is ignored this if you choose custom domain in service URL" />
<Param Name="Password" Required="True" Label="Token (PAT Bearer Token)" HelpLink="https://confluence.atlassian.com/enterprise/using-personal-access-tokens-1026032365.html" Secret="True" Desc="Get your Personal Access Token by following steps listed here https://confluence.atlassian.com/enterprise/using-personal-access-tokens-1026032365.html" />
<Param Name="CustomColumnsRegex" Template="CustomColumnsRegexParam" />
</Params>
<Notes>
<![CDATA[Copy below URL and open in browser to see instructions about creating PAT (Personal Access Token) to access JIRA data using API.
<p />
https://confluence.atlassian.com/enterprise/using-personal-access-tokens-1026032365.html
]]>
</Notes>
</Auth>
<Auth Type="OAuth" Name="oauth" Label="OAuth (**Must change API Base URL to V3 OAuth**)" Desc="OAuth App must be created in Atlassian Developer Console. It is found at https://developer.atlassian.com/console/myapps/"
HelpLink="https://developer.atlassian.com/cloud/jira/platform/oauth-2-3lo-apps/"
ConnStr="Provider=Custom;OAuthVersion=OAuth2;AuthUrl=https://auth.atlassian.com/authorize;TokenUrl=https://auth.atlassian.com/oauth/token;Scope=[$Scope$];ScopeSeparator={space};ContentType=application/json;ExtraAttributesForAuthRequest=audience=api.atlassian.com&prompt=consent"
TestEndPoint="get_fields">
<Notes>
<![CDATA[Firstly, login into your Atlassian account and then create Jira application:
<p />
<ol>
<li>Go to <a target="_blank" href="https://developer.atlassian.com/console/myapps/">Atlassian Developer area.</a></li>
<li>Click <a target="_blank" href="https://developer.atlassian.com/console/myapps/create-3lo-app/">Create app</a> and select <b>OAuth 2.0 (3LO) integration</b> app to create an OAuth app.</li>
<li>Give your app a name, accept the terms and hit <b>Create</b>.</li>
<li>Then hit <b>App details</b> panel and copy paste <i>Client ID</i> and <i>Secret</i> into the API Connection Manager configuration grid into the proper places.</li>
<li>Then go back and click <b>Permissions</b> panel to enable permissions/scopes for your application. You can enable them all.</li>
<li>Go back again to application settings and click <b>Authorization</b> panel.</li>
<li>Click <b>Configure</b> button and in <b>Callback URL</b> enter your own Callback URL or simply enter <i>https://zappysys.com/oauth</i> if you don't have one.</li>
<li>Use this same <i>Callback URL (Return URL)</i> in a API Connection Manager configuration grid.</li>
<li>In <i>API Connection Manager</i> enter one or more of these scopes (<a target="_blank" href="https://developer.atlassian.com/cloud/jira/platform/scopes-for-connect-and-oauth-2-3LO-apps/#oauth-2-0-authorization-code-only-scopes">read more</a> about available scopes) into <b>Scopes</b> property:
<ol>
<li>offline_access <i>(a must)</i></li>
<li>read:jira-user</li>
<li>read:jira-work</li>
<li>write:jira-work</li>
<li>manage:jira-project</li>
<li>manage:jira-configuration</li>
</ol>
</li>
<li>Also configure your company's <b>Subdomain</b> in the properties grid.</li>
<li>Click <b>Generate Token</b> to generate tokens.</li>
<li>That's it!</li>
</ol>
]]>
</Notes>
<Params>
<Param Name="ClientId" Required="True" />
<Param Name="ClientSecret" Secret="True" Required="True"/>
<Param Name="Scope"
Required="True"
Options="offline_access;read:jira-user;read:jira-work;write:jira-work;manage:jira-project;manage:jira-configuration;manage:jira-webhook"
MultiSelectSeparator=" "
MultiSelect="True"
Value="offline_access~read:jira-user~read:jira-work~write:jira-work~manage:jira-project~manage:jira-configuration"
Desc="Permissions you want to use."
Example="offline_access read:jira-user read:jira-work write:jira-work manage:jira-project manage:jira-configuration manage:jira-webhook"
HelpLink="https://developer.atlassian.com/cloud/jira/platform/scopes-for-connect-and-oauth-2-3LO-apps/#oauth-2-0-authorization-code-only-scopes"/>
<Param Name="ReturnUrl" Value="https://zappysys.com/oauth" Required="True"/>
<Param Name="OrganizationId" OptionsEndPoint="get_resources" OptionsEndPointValueColumn="Id" OptionsEndPointLabelColumn="Name" Required="True" Label="Organization Id (Select after clicking [Generate Token])" Desc="Your company's Alphanumeric ID (e.g. c66xxx-xxx-xxx). This is obtained by calling GET " />
<Param Name="CustomColumnsRegex" Template="CustomColumnsRegexParam" Label="Custom Columns for output (Select after clicking [Generate Token])"/>
</Params>
</Auth>
</Auths>
<Template>
<Param Name="CustomColumnsRegexParam" LabelXX="Custom columns for output"
OptionsEndPoint="get_custom_fields" OptionsEndPointLabelColumn="ScopeProjectId,Name"
OptionsEndPointValueColumn="Key" OptionsExtra="All Custom Columns=(.*);No Custom Columns=$^"
OptionsEndPointParameters="CustomColumnsRegex=(.*)"
OptionsEndPointSortBy="ScopeProjectId desc,Name" MultiSelect="True" MultiSelectSeparator="|"
Value=""
Desc="Enter regular expression for custom columns which you like to output. Use Pipe to type keys (internal names for columns). You can use Regex too (e.g. field_100\d+ )"
/>
<EndPoint Name="WorklogTemplate"
ResponseFormat="Json"
JsonRowFormat="Multicontent"
ContentType="application/json"
Body="{$rows$}">
<Params>
<Param Type="Placeholder" Name="IssueIdOrKey" Desc="Enter Issue ID or Key" Example="100002 or MYISSUE-1"/>
</Params>
<InputColumns>
<Column Name="visibility.type" Label="VisibilityType" Desc="Enter 'group' or 'role'" Example="group" />
<Column Name="visibility.value" Label="VisibilityValue" Desc="The name of the group or role to which visibility of this item is restricted." Example="jira-developers" />
<Column Name="comment" Label="Comment" Raw="True"
ValueTemplate="{ "type": "doc", "version": 1, "content": [ { "type": "paragraph", "content": [ { "text": "<<{$value$},FUN_JSONENC>>", "type": "text" } ] } ] }"
DataType="DT_NTEXT"
/>
<Column Name="comment" Label="CommentFormatted" DataType="DT_NTEXT" Raw="True"/>
<Column Name="started" Label="StartedAt" DataType="DT_WSTR" Length="100" Desc="Datetime with timezone" Example="2020-02-23T16:20:30.123+0300"/>
<Column Name="timeSpent" Label="TimeSpent" DataType="DT_WSTR" Length="50" Desc="Defines a time spent in hours and minutes" Example="3h 20m" />
<Column Name="timeSpentSeconds" Label="TimeSpentInSeconds" DataType="DT_I8" Desc="Defines a time spent in seconds" Example="12000" />
</InputColumns>
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="50" />
<Column Name="issueId" Label="IssueId" DataType="DT_WSTR" Length="50" />
<Column Name="self" Label="Self" DataType="DT_WSTR" Length="150" />
<Column Name="author.self" Label="AuthorSelf" DataType="DT_WSTR" Length="150" />
<Column Name="author.accountId" Label="AuthorAccountId" DataType="DT_WSTR" Length="100" />
<Column Name="author.emailAddress" Label="AuthorEmailAddress" DataType="DT_WSTR" Length="100" />
<Column Name="author.displayName" Label="AuthorDisplayName" DataType="DT_WSTR" Length="200" />
<Column Name="author.active" Label="AuthorIsActive" DataType="DT_BOOL" />
<Column Name="author.timeZone" Label="AuthorTimeZone" DataType="DT_WSTR" Length="50" />
<Column Name="author.accountType" Label="AuthorAccountType" DataType="DT_WSTR" Length="50" />
<Column Name="comment.content" Label="CommentFormatted" DataType="DT_NTEXT" />
<Column Name="comment.content" Label="Comment" DataType="DT_NTEXT" Functions="[$1]|~|$..content[*].text|~| ,FUN_JSON_TO_TEXT" Desc="Join formatted text blocks using space separator (new lines will be lost)"/>
<Column Name="updateAuthor.self" Label="UpdateAuthorSelf" DataType="DT_WSTR" Length="150" />
<Column Name="updateAuthor.accountId" Label="UpdateAuthorAccountId" DataType="DT_WSTR" Length="150" />
<Column Name="updateAuthor.emailAddress" Label="UpdateAuthorEmailAddress" DataType="DT_WSTR" Length="100" />
<Column Name="updateAuthor.displayName" Label="UpdateAuthorDisplayName" DataType="DT_WSTR" Length="50" />
<Column Name="updateAuthor.active" Label="UpdateAuthorActive" DataType="DT_BOOL" />
<Column Name="updateAuthor.timeZone" Label="UpdateAuthorTimeZone" DataType="DT_WSTR" Length="50" />
<Column Name="updateAuthor.accountType" Label="UpdateAuthorAccountType" DataType="DT_WSTR" Length="50" />
<Column Name="created" Label="Created" DataType="DT_DBTIMESTAMP" />
<Column Name="updated" Label="Updated" DataType="DT_DBTIMESTAMP" />
<Column Name="started" Label="Started" DataType="DT_DBTIMESTAMP" />
<Column Name="timeSpent" Label="TimeSpent" DataType="DT_WSTR" Length="50" />
<Column Name="timeSpentSeconds" Label="TimeSpentInSeconds" DataType="DT_I8" />
<Column Name="author.avatarUrls.48x48" Label="AuthorAvatarUrls48x48" DataType="DT_WSTR" Length="500" />
<Column Name="author.avatarUrls.24x24" Label="AuthorAvatarUrls24x24" DataType="DT_WSTR" Length="500" />
<Column Name="author.avatarUrls.16x16" Label="AuthorAvatarUrls16x16" DataType="DT_WSTR" Length="500" />
<Column Name="author.avatarUrls.32x32" Label="AuthorAvatarUrls32x32" DataType="DT_WSTR" Length="500" />
<Column Name="updateAuthor.avatarUrls.48x48" Label="UpdateAuthorAvatarUrls48x48" DataType="DT_WSTR" Length="500" />
<Column Name="updateAuthor.avatarUrls.24x24" Label="UpdateAuthorAvatarUrls24x24" DataType="DT_WSTR" Length="500" />
<Column Name="updateAuthor.avatarUrls.16x16" Label="UpdateAuthorAvatarUrls16x16" DataType="DT_WSTR" Length="500" />
<Column Name="updateAuthor.avatarUrls.32x32" Label="UpdateAuthorAvatarUrls32x32" DataType="DT_WSTR" Length="500" />
</OutputColumns>
</EndPoint>
<EndPoint Name="IssuesTemplate" Url="/search?startAt=0&maxResults=100" Method="GET" BatchSize="500" RequestFormat="Csv" Filter="$.issues[*]">
<Params>
<Param Name="SearchBy"
Label="Search by"
Options="Project;JQL;Key"
/>
<Param Name="Project"
Key="jql"
Required="False"
OptionsEndPoint="get_projects"
OptionsEndPointValueColumn="key"
OptionsEndPointLabelColumn="name"
ValueTemplate="project="{$value$}""
Type="Query"
VisibleIf="SearchBy==Project"
Desc="Specify a project to search issues for"
/>
<Param Name="JQL"
Key="jql"
Required="False"
Options="Search Ids (IN)=key IN(10001, 10002, 10003);
Search Keys (IN)=key IN(CS-1, CS-2, CS-3);
Search Projects (IN)=project IN(PROJ1, PROJ2, PROJ3);
Search Status (EQUAL)=status='Done';
Search Date (Expression 1)=created >=-5d;
Search Date (Expression 2)=created >=startOfMonth() AND created <=now();
Search Date (Expression 3)=created >=startOfYear() AND created <=startOfDay();
Search Date (Static)=created >= 2008/12/31;
Search Date (Static with time)=created >= 2008/12/31 23:59;
Search Project and Status (AND + IN)=project=CS AND status NOT IN ('Done', 'Open', 'Closed');
Search Assignee and Created Date=assignee is NOT EMPTY and created < -1d;
Search Text (Contains - Fuzzy)=Summary ~ 'some words' OR description ~ 'some words';
Search Text (Contains - Fuzzy Wildcard)=Summary ~ 'some*' OR description ~ 'some*';
Search Text (Contains - Exact)=Summary ~ '\"exact words\"' OR description ~ '\"exact words\"';
Search Text (Does Not Contain - Fuzzy)=Summary !~ 'some words' OR description !~ 'some words';
Search Empty OR Null=fixVersion is empty OR fixVersion is null;
Search Is Not Empty OR Is Not Null=fixVersion is not empty OR fixVersion is not null;
Search WAS Operator (previous value)=status WAS "Resolved" BY (jsmith,srogen) BEFORE "2019/02/02";
Search WAS IN Operator=status WAS IN ("Resolved", "In Progress");
Search WAS NOT IN Operator=status WAS NOT IN ("Resolved", "In Progress");
Search WAS + BY + DURING (date range)=status WAS "Resolved" BY (jsmith,srogen) DURING("2019/02/02", "2020/02/02");
Search CHANGED operator=assignee CHANGED;
Search CHANGED operator (multiple)=status CHANGED FROM "In Progress" TO "Open""
Type="Query"
VisibleIf="SearchBy==JQL"
Desc="Use JQL to use an advanced mode of searching. Few links are https://www.atlassian.com/blog/jira/jql-the-most-flexible-way-to-search-jira-14 and als check https://wac-cdn.atlassian.com/dam/jcr:241d9c9b-475f-411f-90a2-97ca344442b3/atlassian-jql-cheat-sheet-2.pdf?cdnVersion=2169"
Example="project=MyProjectKey"
HelpLink="https://www.atlassian.com/software/jira/guides/expand-jira/jql#advanced-search"
/>
<Param Name="Key"
Label="Issue Key(s) or Ids - Comma Separated (PRJA-10,PRJA-13)"
IsKey="True"
ReadAs="Id"
Key="jql"
Required="False"
Type="Query"
VisibleIf="SearchBy==Key"
Functions="{$rows$}"
ValueTemplate="Key in ({$value$})"
Desc="Use comma separated IDs or Keys for Issues. 1000 ids max or JIRA internal limit for Payload."
/>
<Param Name="CustomColumnsRegex" Template="CustomColumnsRegexParam" />
<Param Name="PagingMode" Type="Property" Value="1" />
<Param Name="PagingByUrlAttributeName" Type="Property" Value="startAt" />
<Param Name="PagingIncrementBy" Type="Property" Value="100" Desc="This must be same as maxResults in URL"/>
<Param Name="PagingMaxRowsExpr" Type="Property" Value="$.total" />
<Param Name="PagingMaxRowsDataPathExpr" Type="Property" Value="$.issues[*]" />
</Params>
<LayoutMap>
<![CDATA[<?xml version="1.0" encoding="utf-8"?>
<settings>
<dataset id="root" readfrominput="True" />
<map name="Key" src="Key" />
</settings>]]>
</LayoutMap>
</EndPoint>
<EndPoint Name="FieldsTemplate" CachedTtl="30" UseRawCache="True" Url="/field" Method="GET" >
<Params>
<Param Name="EnableRawOutputModeSingleRow" Type="Property" Value="True" Hidden="True" />
<Param Name="RawOutputDataRowTemplate" Type="Property" Value="{data: [$1]}" Hidden="True" />
</Params>
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="120" />
<Column Name="key" Label="Key" DataType="DT_WSTR" Length="120" />
<Column Name="name" Label="Name" DataType="DT_WSTR" Length="250" />
<Column Name="custom" Label="Custom" DataType="DT_BOOL" />
<Column Name="orderable" Label="Orderable" DataType="DT_BOOL" />
<Column Name="navigable" Label="Navigable" DataType="DT_BOOL" />
<Column Name="searchable" Label="Searchable" DataType="DT_BOOL" />
<Column Name="clauseNames" Label="ClauseNames" DataType="DT_WSTR" Length="900" />
<Column Name="schema.type" Label="SchemaType" DataType="DT_WSTR" Length="200" />
<Column Name="schema.system" Label="SchemaSystem" DataType="DT_WSTR" Length="200" />
<Column Name="untranslatedName" Label="UntranslatedName" DataType="DT_WSTR" Length="200" />
<Column Name="schema.items" Label="SchemaItems" DataType="DT_WSTR" Length="200" />
<Column Name="schema.custom" Label="SchemaCustom" DataType="DT_WSTR" Length="100" />
<Column Name="schema.customId" Label="SchemaCustomId" DataType="DT_I8" />
<Column Name="scope.type" Label="ScopeType" DataType="DT_WSTR" Length="50" />
<Column Name="scope.project.id" Label="ScopeProjectId" DataType="DT_WSTR" Length="50" />
</OutputColumns>
</EndPoint>
</Template>
<EndPoints>
<!-- ==================
ORGS
=================== -->
<EndPoint Name="get_resources" Label="Read Resources" Url="https://api.atlassian.com/oauth/token/accessible-resources" >
<OutputColumns>
<Column Name="id" Label="Id"/>
<Column Name="name" Label="Name"/>
<Column Name="url" Label="Url"/>
<Column Name="scopes" Label="Scopes" DataType="DT_WSTR" Length="1000" />
</OutputColumns>
</EndPoint>
<!-- ==================
FIELDS
=================== -->
<EndPoint Name="get_fields" Label="Read Fields" Template="FieldsTemplate" Filter="$.data[*]">
<Params>
<Param Name="Filter" Type="Property" Value="$.data[*]"
Options="All fields=$.data[*];
Custom fields=$.data[?(@custom==true)];
System fields (non-custom)=$.data[?(@custom==false)]
Custom Column name contains=$.data[?(@custom==true && @name=~/ProjectACol1|ProjectACol2/)];
Custom Column key contains=$.data[?(@custom==true && @key=~/customfield_10001|customfield_10002/)];
Custom Column name starts with=$.data[?(@custom==true && @name=~/^order|^survey/)];
Custom Column name ends with=$.data[?(@custom==true && @name=~/order$|survey$/)];
Custom Column name exact match=$.data[?(@custom==true && @name=~/^ordernumber$|^surveyquestion$/)];
Custom Column without option and textarea type=$.data[?(@.custom==true && @schema.type!='option' && @schema.custom=~/^((?!textarea).)*$/)]"
/>
</Params>
</EndPoint>
<!-- ==================
Custom FIELDS
=================== -->
<EndPoint Name="get_custom_fields" Label="Read Custom Fields" Template="FieldsTemplate"
Filter="$.data[?(@custom==true && @key=~/[$CustomColumnsRegex$]/)]">
<Params>
<Param Name="CustomColumnsRegex" Template="CustomColumnsRegexParam"/>
</Params>
</EndPoint>
<!-- =======================
ISSUE TYPES
======================== -->
<EndPoint Name="get_types" Label="Read Issue Types" Url="/issuetype" Method="GET" Cached="True" CachedTtl="60">
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="50" />
<Column Name="description" Label="Description" DataType="DT_WSTR" Length="1000" />
<Column Name="name" Label="Name" DataType="DT_WSTR" Length="200" />
<Column Name="untranslatedName" Label="UntranslatedName" DataType="DT_WSTR" Length="200" />
<Column Name="subtask" Label="Subtask" DataType="DT_BOOL" />
<Column Name="avatarId" Label="AvatarId" DataType="DT_I8" />
<Column Name="hierarchyLevel" Label="HierarchyLevel" DataType="DT_I8" />
<Column Name="scope.type" Label="ScopeType" DataType="DT_WSTR" Length="50" />
<Column Name="scope.project.id" Label="ScopeProjectId" DataType="DT_WSTR" Length="50" />
<Column Name="iconUrl" Label="IconUrl" DataType="DT_WSTR" Length="500" />
<Column Name="self" Label="Self" DataType="DT_WSTR" Length="150" />
</OutputColumns>
</EndPoint>
<!-- =====================
USERS
====================== -->
<EndPoint Name="get_users" Label="Read Users" Url="/users/search?startAt=0&maxResults=50" Method="GET">
<Params>
<Param Name="PagingMode" Type="Property" Value="1" />
<Param Name="PagingByUrlAttributeName" Type="Property" Value="startAt" />
<Param Name="PagingIncrementBy" Type="Property" Value="50" />
</Params>
<OutputColumns>
<Column Name="displayName" Label="DisplayName" DataType="DT_WSTR" Length="200" />
<Column Name="emailAddress" Label="EmailAddress" DataType="DT_WSTR" Length="200" />
<Column Name="accountId" Label="AccountId" DataType="DT_WSTR" Length="150" />
<Column Name="accountType" Label="AccountType" DataType="DT_WSTR" Length="50" />
<Column Name="active" Label="Active" DataType="DT_BOOL" />
<Column Name="locale" Label="Locale" DataType="DT_WSTR" Length="50" />
<Column Name="self" Label="Self" DataType="DT_WSTR" Length="150" />
<Column Name="avatarUrls.48x48" Label="AvatarUrls48x48" DataType="DT_WSTR" Length="500" />
<Column Name="avatarUrls.24x24" Label="AvatarUrls24x24" DataType="DT_WSTR" Length="500" />
<Column Name="avatarUrls.16x16" Label="AvatarUrls16x16" DataType="DT_WSTR" Length="500" />
<Column Name="avatarUrls.32x32" Label="AvatarUrls32x32" DataType="DT_WSTR" Length="500" />
</OutputColumns>
</EndPoint>
<!-- INSERT USER -->
<EndPoint Name="create_user" Label="Create User" Url="/user" Method="POST"
JsonRowFormat="Multicontent"
ContentType="application/json"
Body="{$rows$}">
<InputColumns>
<Column Name="password" Label="Password" DataType="DT_WSTR" Length="50" Desc="If password is not supplied, random one is generated" />
<Column Name="emailAddress" Label="EmailAddress" DataType="DT_WSTR" Length="200" />
<Column Name="displayName" Label="DisplayName" DataType="DT_WSTR" Length="200" />
</InputColumns>
<OutputColumns>
<Column Name="displayName" Label="DisplayName" DataType="DT_WSTR" Length="200" />
<Column Name="emailAddress" Label="EmailAddress" DataType="DT_WSTR" Length="200" />
<Column Name="accountId" Label="AccountId" DataType="DT_WSTR" Length="150" />
<Column Name="accountType" Label="AccountType" DataType="DT_WSTR" Length="50" />
<Column Name="active" Label="Active" DataType="DT_BOOL" />
<Column Name="locale" Label="Locale" DataType="DT_WSTR" Length="50" />
<Column Name="self" Label="Self" DataType="DT_WSTR" Length="150" />
<Column Name="avatarUrls.48x48" Label="AvatarUrls48x48" DataType="DT_WSTR" Length="500" />
<Column Name="avatarUrls.24x24" Label="AvatarUrls24x24" DataType="DT_WSTR" Length="500" />
<Column Name="avatarUrls.16x16" Label="AvatarUrls16x16" DataType="DT_WSTR" Length="500" />
<Column Name="avatarUrls.32x32" Label="AvatarUrls32x32" DataType="DT_WSTR" Length="500" />
</OutputColumns>
</EndPoint>
<!-- DELETE USER -->
<EndPoint Name="delete_user" Label="Delete User" Url="/user" Method="DELETE">
<Params>
<Param Name="AccountId" Key="accountId" Type="Query" Example="5b10ac8d82e05b22cc7d4ef5"/>
<Param Name="ContinueOn404Error" Key="ContineOn404Error" Label="Continue On 404 Error (When record not found)" Type="Property" Value="False" />
</Params>
<OutputColumns>
<Column Name="Response" DataType="DT_NTEXT" />
</OutputColumns>
<InputColumns>
<Column Name="AccountId" DataType="DT_WSTR" MapToParam="True" Key="True" />
</InputColumns>
</EndPoint>
<!-- ====================
PROJECTS
===================== -->
<EndPoint Name="get_projects" Label="Read Projects" Url="/project/search" Method="GET" Filter="$.values[*]">
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_I8" />
<Column Name="key" Label="Key" DataType="DT_WSTR" Length="50" />
<Column Name="name" Label="Name" DataType="DT_WSTR" Length="100" />
<Column Name="projectCategory.id" Label="ProjectCategoryId" DataType="DT_WSTR" Length="50" />
<Column Name="projectCategory.name" Label="ProjectCategoryName" DataType="DT_WSTR" Length="50" />
<Column Name="projectCategory.description" Label="ProjectCategoryDescription" DataType="DT_WSTR" Length="150" />
<Column Name="projectCategory.self" Label="ProjectCategorySelf" DataType="DT_WSTR" Length="150" />
<Column Name="simplified" Label="Simplified" DataType="DT_BOOL" />
<Column Name="style" Label="Style" DataType="DT_WSTR" Length="50" />
<Column Name="insight.totalIssueCount" Label="InsightTotalIssueCount" DataType="DT_I8"/>
<Column Name="insight.lastIssueUpdateTime" Label="InsightLastIssueUpdateTime" DataType="DT_DBTIMESTAMP"/>
<Column Name="avatarUrls.48x48" Label="AvatarUrls48x48" DataType="DT_WSTR" Length="500" />
<Column Name="avatarUrls.24x24" Label="AvatarUrls24x24" DataType="DT_WSTR" Length="500" />
<Column Name="avatarUrls.16x16" Label="AvatarUrls16x16" DataType="DT_WSTR" Length="500" />
<Column Name="avatarUrls.32x32" Label="AvatarUrls32x32" DataType="DT_WSTR" Length="500" />
</OutputColumns>
</EndPoint>
<!-- CREATE PROJECT -->
<EndPoint Name="create_project" Label="Create Project" Url="/project" Method="POST"
ContentType="application/json"
Body="{$rows$}"
JsonRowFormat="Multicontent">
<InputColumns>
<Column Name="key" Label="ProjectKey" DataType="DT_WSTR" Length="50" Desc="A key uniquely representing your project" Example="PRJ" />
<Column Name="name" Label="Name" DataType="DT_WSTR" Length="500" />
<Column Name="projectTypeKey" Label="ProjectTypeKey" Desc="The project type, which dictates the application-specific feature set. Possible values: business, service_desk, software" Example="business" DataType="DT_WSTR" Length="100" />
<Column Name="description" Label="Description" DataType="DT_WSTR" Length="2000" />
<Column Name="leadAccountId" Label="LeadAccountId" DataType="DT_WSTR" Length="100" Desc="The account ID of the project lead" Example="12551:12be2e5a-9ebf-1235-be58-c3ea8c98b9e2" />
<Column Name="url" Label="Url" DataType="DT_WSTR" Length="400" Desc="A link to information about this project, such as project documentation" />
<Column Name="assigneeType" Label="AssigneeType" Desc="The default assignee when creating issues for this project. Possible values: PROJECT_LEAD, UNASSIGNED" Example="PROJECT_LEAD" DataType="DT_WSTR" Length="100" />
<Column Name="avatarId" Label="AvatarId" DataType="DT_I8" Desc="An integer value for the project's avatar."/>
<Column Name="issueSecurityScheme" Label="IssueSecurityScheme" DataType="DT_I8" Desc="The ID of the issue security scheme for the project, which enables you to control who can and cannot view issues." />
<Column Name="permissionScheme" Label="PermissionScheme" DataType="DT_I8" Desc="The ID of the permission scheme for the project."/>
<Column Name="notificationScheme" Label="NotificationScheme" DataType="DT_I8" Desc="The ID of the notification scheme for the project."/>
<Column Name="categoryId" Label="CategoryId" DataType="DT_I8" Desc="The ID of the project's category." />
</InputColumns>
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_I8" />
<Column Name="key" Label="Key" DataType="DT_WSTR" Length="50" />
<Column Name="self" Label="Self" DataType="DT_WSTR" Length="200" />
</OutputColumns>
</EndPoint>
<!-- UPSERT PROJECT -->
<EndPoint Name="upsert_project" Label="Upsert Project" Url="/project/[$ProjectIdOrKey$]" Method="PUT"
ContentType="application/json"
Body="{$rows$}"
JsonRowFormat="Multicontent">
<Params>
<Param Name="ProjectIdOrKey" Type="Placeholder" />
</Params>
<InputColumns>
<Column Name="key" Label="ProjectKey" DataType="DT_WSTR" Length="50" Desc="A key uniquely representing your project" Example="PRJ" />
<Column Name="name" Label="Name" DataType="DT_WSTR" Length="100" />
<Column Name="description" Label="Description" DataType="DT_WSTR" Length="300" />
<Column Name="leadAccountId" Label="LeadAccountId" DataType="DT_WSTR" Length="100" Desc="The account ID of the project lead" Example="12551:12be2e5a-9ebf-1235-be58-c3ea8c98b9e2" />
<Column Name="url" Label="Url" DataType="DT_WSTR" Length="400" Desc="A link to information about this project, such as project documentation" />
<Column Name="assigneeType" Label="AssigneeType" Desc="The default assignee when creating issues for this project. Possible values: PROJECT_LEAD, UNASSIGNED" Example="PROJECT_LEAD" DataType="DT_WSTR" Length="100" />
<Column Name="avatarId" Label="AvatarId" DataType="DT_I8" Desc="An integer value for the project's avatar."/>
<Column Name="issueSecurityScheme" Label="IssueSecurityScheme" DataType="DT_I8" Desc="The ID of the issue security scheme for the project, which enables you to control who can and cannot view issues." />
<Column Name="permissionScheme" Label="PermissionScheme" DataType="DT_I8" Desc="The ID of the permission scheme for the project."/>
<Column Name="notificationScheme" Label="NotificationScheme" DataType="DT_I8" Desc="The ID of the notification scheme for the project."/>
<Column Name="categoryId" Label="CategoryId" DataType="DT_I8" Desc="The ID of the project's category." />
</InputColumns>
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="50" />
<Column Name="key" Label="Key" DataType="DT_WSTR" Length="50" />
<Column Name="name" Label="Name" DataType="DT_WSTR" Length="100" />
<Column Name="description" Label="Description" DataType="DT_NTEXT" />
<Column Name="issueTypes" Label="IssueTypes" DataType="DT_NTEXT" />
<Column Name="expand" Label="Expand" DataType="DT_WSTR" Length="300" />
<Column Name="self" Label="Self" DataType="DT_WSTR" Length="150" />
<Column Name="entityId" Label="EntityId" DataType="DT_WSTR" Length="100" />
<Column Name="uuid" Label="Uuid" DataType="DT_WSTR" Length="100" />
<Column Name="properties" Label="Properties" DataType="DT_NTEXT" />
<Column Name="components" Label="Components" DataType="DT_NTEXT" />
<Column Name="lead.self" Label="LeadSelf" DataType="DT_WSTR" Length="500" />
<Column Name="lead.accountId" Label="LeadAccountId" DataType="DT_WSTR" Length="100" />
<Column Name="lead.avatarUrls.48x48" Label="LeadAvatarUrls48x48" DataType="DT_WSTR" Length="700" />
<Column Name="lead.avatarUrls.24x24" Label="LeadAvatarUrls24x24" DataType="DT_WSTR" Length="700" />
<Column Name="lead.avatarUrls.16x16" Label="LeadAvatarUrls16x16" DataType="DT_WSTR" Length="700" />
<Column Name="lead.avatarUrls.32x32" Label="LeadAvatarUrls32x32" DataType="DT_WSTR" Length="700" />
<Column Name="lead.displayName" Label="LeadDisplayName" DataType="DT_WSTR" Length="100" />
<Column Name="lead.active" Label="LeadActive" DataType="DT_BOOL" />
<Column Name="projectTypeKey" Label="ProjectTypeKey" DataType="DT_WSTR" Length="50" />
<Column Name="assigneeType" Label="AssigneeType" DataType="DT_WSTR" Length="50" />
<Column Name="versions" Label="Versions" DataType="DT_WSTR" Length="50" />
<Column Name="simplified" Label="Simplified" DataType="DT_BOOL" />
<Column Name="style" Label="Style" DataType="DT_WSTR" Length="50" />
<Column Name="isPrivate" Label="IsPrivate" DataType="DT_BOOL" />
<Column Name="roles.atlassian-addons-project-access" Label="RolesAtlassianAddonsProjectAccess" DataType="DT_WSTR" Length="500" />
<Column Name="roles.Administrator" Label="RolesAdministrator" DataType="DT_WSTR" Length="500" />
<Column Name="roles.Viewer" Label="RolesViewer" DataType="DT_WSTR" Length="500" />
<Column Name="roles.Member" Label="RolesMember" DataType="DT_WSTR" Length="500" />
<Column Name="avatarUrls.48x48" Label="AvatarUrls48x48" DataType="DT_WSTR" Length="500" />
<Column Name="avatarUrls.24x24" Label="AvatarUrls24x24" DataType="DT_WSTR" Length="500" />
<Column Name="avatarUrls.16x16" Label="AvatarUrls16x16" DataType="DT_WSTR" Length="500" />
<Column Name="avatarUrls.32x32" Label="AvatarUrls32x32" DataType="DT_WSTR" Length="500" />
</OutputColumns>
</EndPoint>
<!-- DELETE PROJECT -->
<EndPoint Name="delete_project" Label="Delete Project" Url="/project/[$ProjectIdOrKey$]" Method="DELETE"
ResponseFormat="Json" >
<Params>
<Param Name="ProjectIdOrKey" IsKey="True" ReadAs="Id" Type="Placeholder" Desc="Project ID or Key, uniquely representing a project" Example="10002, MYPRJ" />
<Param Name="EnableUndo" Key="enableUndo" Type="Query" />
<Param Name="ContinueOn404Error" Key="ContineOn404Error" Label="Continue On 404 Error (When record not found)" Type="Property" Value="False" />
</Params>
<OutputColumns>
<Column Name="Response" DataType="DT_NTEXT" />
</OutputColumns>
<InputColumns>
<Column Name="ProjectIdOrKey" Label="Id" DataType="DT_WSTR" MapToParam="True" Key="True" />
</InputColumns>
</EndPoint>
<!-- =====================
READ ISSUES
====================== -->
<EndPoint Name="get_issues" Template="IssuesTemplate" Label="Read Issues" >
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_I8" />
<Column Name="key" Label="Key" DataType="DT_WSTR" Length="50" />
<Column Name="fields.project.key" Label="ProjectKey" DataType="DT_WSTR" Length="50" />
<Column Name="fields.project.name" Label="ProjectName" DataType="DT_WSTR" Length="500" />
<Column Name="fields.status.name" Label="StatusName" DataType="DT_WSTR" Length="500" />
<Column Name="fields.summary" Label="Summary" DataType="DT_WSTR" Length="2000" />
<Column Name="fields.created" Label="Created" DataType="DT_DBTIMESTAMP" />
<Column Name="fields.updated" Label="Updated" DataType="DT_DBTIMESTAMP" />
<Column Name="fields.description.content" Label="Description" DataType="DT_NTEXT" Functions="[$1]|~|$..content[*].text|~| ,FUN_JSON_TO_TEXT" Desc="Join formatted text blocks using space separator (new lines will be lost)"/>
<Column Name="fields.description.content" Label="DescriptionFormatted" DataType="DT_NTEXT" />
<Column Name="fields.creator.accountId" Label="CreatorAccountId" DataType="DT_WSTR" Length="100" />
<Column Name="fields.creator.emailAddress" Label="CreatorEmailAddress" DataType="DT_WSTR" Length="100" />
<Column Name="fields.reporter.accountId" Label="ReporterAccountId" DataType="DT_WSTR" Length="100" />
<Column Name="fields.reporter.emailAddress" Label="ReporterEmailAddress" DataType="DT_WSTR" Length="100" />
<Column Name="fields.assignee.accountId" Label="AssigneeAccountId" DataType="DT_WSTR" Length="100"/>
<Column Name="fields.assignee.emailAddress" Label="AssigneeEmailAddress" DataType="DT_WSTR" Length="100" />
<Column Name="fields.resolution.id" Label="ResolutionId" DataType="DT_I8" />
<Column Name="fields.resolution.description" Label="ResolutionDescription" DataType="DT_WSTR" Length="4000" />
<Column Name="fields.resolution.name" Label="ResolutionName" DataType="DT_WSTR" Length="500" />
<Column Name="fields.resolution" Label="Resolution" DataType="DT_NTEXT" />
<Column Name="fields.timespent" Label="TimespentInSeconds" DataType="DT_I8" />
<Column Name="fields.duedate" Label="DueDate" DataType="DT_DBDATE" />
<Column Name="expand" Label="Expand" DataType="DT_WSTR" Length="150" />
<Column Name="fields.statuscategorychangedate" Label="StatusCategoryChangeDate" DataType="DT_DBTIMESTAMP" />
<Column Name="self" Label="Self" DataType="DT_WSTR" Length="150" />
<Column Name="fields.issuetype.self" Label="IssueTypeSelf" DataType="DT_WSTR" Length="150" />
<Column Name="fields.issuetype.id" Label="IssueTypeId" DataType="DT_I8" />
<Column Name="fields.issuetype.description" Label="IssueTypeDescription" DataType="DT_NTEXT" />
<Column Name="fields.issuetype.iconUrl" Label="IssueTypeIconUrl" DataType="DT_WSTR" Length="500" />
<Column Name="fields.issuetype.name" Label="IssueTypeName" DataType="DT_WSTR" Length="100" />
<Column Name="fields.issuetype.subtask" Label="IssueTypeSubtask" DataType="DT_BOOL" />
<Column Name="fields.issuetype.avatarId" Label="IssueTypeAvatarId" DataType="DT_I4" />
<Column Name="fields.issuetype.entityId" Label="IssueTypeEntityId" DataType="DT_GUID" />
<Column Name="fields.issuetype.hierarchyLevel" Label="IssueTypeHierarchyLevel" DataType="DT_I4" />
<Column Name="fields.project.self" Label="ProjectSelf" DataType="DT_WSTR" Length="150" />
<Column Name="fields.project.id" Label="ProjectId" DataType="DT_I8" />
<Column Name="fields.project.projectTypeKey" Label="ProjectTypeKey" DataType="DT_WSTR" Length="100" />
<Column Name="fields.project.simplified" Label="ProjectSimplified" DataType="DT_BOOL" />
<Column Name="fields.project.avatarUrls.48x48" Label="ProjectAvatarUrls48x48" DataType="DT_WSTR" Length="500" />
<Column Name="fields.project.avatarUrls.24x24" Label="ProjectAvatarUrls24x24" DataType="DT_WSTR" Length="500" />
<Column Name="fields.project.avatarUrls.16x16" Label="ProjectAvatarUrls16x16" DataType="DT_WSTR" Length="500" />
<Column Name="fields.project.avatarUrls.32x32" Label="ProjectAvatarUrls32x32" DataType="DT_WSTR" Length="500" />
<Column Name="fields.fixVersions" Label="FixVersions" DataType="DT_NTEXT" />
<Column Name="fields.aggregatetimespent" Label="AggregateTimespentInSeconds" DataType="DT_I8" />
<Column Name="fields.resolutiondate" Label="ResolutionDate" DataType="DT_DBTIMESTAMP" />
<Column Name="fields.workratio" Label="WorkRatio" DataType="DT_WSTR" Length="100" />
<Column Name="fields.watches.self" Label="WatchesSelf" DataType="DT_WSTR" Length="150" />
<Column Name="fields.watches.watchCount" Label="WatchesWatchCount" DataType="DT_I4" />
<Column Name="fields.watches.isWatching" Label="WatchesIsWatching" DataType="DT_BOOL"/>
<Column Name="fields.lastViewed" Label="LastViewed" DataType="DT_DBTIMESTAMP" />
<Column Name="fields.priority.self" Label="PrioritySelf" DataType="DT_WSTR" Length="150" />
<Column Name="fields.priority.iconUrl" Label="PriorityIconUrl" DataType="DT_WSTR" Length="500" />
<Column Name="fields.priority.name" Label="PriorityName" DataType="DT_WSTR" Length="100" />
<Column Name="fields.priority.id" Label="PriorityId" DataType="DT_I4" />
<Column Name="fields.labels" Label="Labels" DataType="DT_NTEXT" />
<Column Name="fields.aggregatetimeoriginalestimate" Label="AggregateTimeOriginalEstimate" DataType="DT_WSTR" Length="50"/>
<Column Name="fields.timeestimate" Label="TimeEstimate" DataType="DT_WSTR" Length="50" />
<Column Name="fields.versions" Label="Versions" DataType="DT_NTEXT" />
<Column Name="fields.issuelinks" Label="IssueLinks" DataType="DT_NTEXT" />
<Column Name="fields.assignee.self" Label="AssigneeSelf" DataType="DT_WSTR" Length="150" />
<Column Name="fields.assignee.avatarUrls.48x48" Label="AssigneeAvatarUrls48x48" DataType="DT_WSTR" Length="500" />
<Column Name="fields.assignee.avatarUrls.24x24" Label="AssigneeAvatarUrls24x24" DataType="DT_WSTR" Length="500" />
<Column Name="fields.assignee.avatarUrls.16x16" Label="AssigneeAvatarUrls16x16" DataType="DT_WSTR" Length="500" />
<Column Name="fields.assignee.avatarUrls.32x32" Label="AssigneeAvatarUrls32x32" DataType="DT_WSTR" Length="500" />
<Column Name="fields.assignee.displayName" Label="AssigneeDisplayName" DataType="DT_WSTR" Length="500" />
<Column Name="fields.assignee.active" Label="AssigneeActive" DataType="DT_BOOL" Length="32" />
<Column Name="fields.assignee.timeZone" Label="AssigneeTimeZone" DataType="DT_WSTR" Length="100" />
<Column Name="fields.assignee.accountType" Label="AssigneeAccountType" DataType="DT_WSTR" Length="100" />
<Column Name="fields.status.self" Label="StatusSelf" DataType="DT_WSTR" Length="150" />
<Column Name="fields.status.description" Label="StatusDescription" DataType="DT_NTEXT" />
<Column Name="fields.status.iconUrl" Label="StatusIconUrl" DataType="DT_WSTR" Length="500" />
<Column Name="fields.status.id" Label="StatusId" DataType="DT_I4" />
<Column Name="fields.status.statusCategory.self" Label="StatusCategorySelf" DataType="DT_WSTR" Length="150" />
<Column Name="fields.status.statusCategory.id" Label="StatusCategoryId" DataType="DT_I4" />
<Column Name="fields.status.statusCategory.key" Label="StatusCategoryKey" DataType="DT_WSTR" Length="100" />
<Column Name="fields.status.statusCategory.colorName" Label="StatusCategoryColorName" DataType="DT_WSTR" Length="100" />
<Column Name="fields.status.statusCategory.name" Label="StatusCategoryName" DataType="DT_WSTR" Length="100" />
<Column Name="fields.components" Label="Components" DataType="DT_NTEXT" />
<Column Name="fields.timeoriginalestimate" Label="TimeOriginalEstimate" DataType="DT_WSTR" Length="50" />
<Column Name="fields.description.version" Label="DescriptionVersion" DataType="DT_I4" />
<Column Name="fields.description.type" Label="DescriptionType" DataType="DT_WSTR" Length="50" />
<Column Name="fields.security" Label="Security" DataType="DT_WSTR" Length="150" />
<Column Name="fields.aggregatetimeestimate" Label="AggregateTimeEstimate" DataType="DT_I8" />
<Column Name="fields.creator.self" Label="CreatorSelf" DataType="DT_WSTR" Length="150" />
<Column Name="fields.creator.avatarUrls.48x48" Label="CreatorAvatarUrls48x48" DataType="DT_WSTR" Length="500" />
<Column Name="fields.creator.avatarUrls.24x24" Label="CreatorAvatarUrls24x24" DataType="DT_WSTR" Length="500" />
<Column Name="fields.creator.avatarUrls.16x16" Label="CreatorAvatarUrls16x16" DataType="DT_WSTR" Length="500" />
<Column Name="fields.creator.avatarUrls.32x32" Label="CreatorAvatarUrls32x32" DataType="DT_WSTR" Length="500" />
<Column Name="fields.creator.displayName" Label="CreatorDisplayName" DataType="DT_WSTR" Length="200" />
<Column Name="fields.creator.active" Label="CreatorActive" DataType="DT_BOOL" />
<Column Name="fields.creator.timeZone" Label="CreatorTimeZone" DataType="DT_WSTR" Length="100" />
<Column Name="fields.creator.accountType" Label="CreatorAccountType" DataType="DT_WSTR" Length="100" />
<Column Name="fields.subtasks" Label="Subtasks" DataType="DT_NTEXT" />
<Column Name="fields.reporter.self" Label="ReporterSelf" DataType="DT_WSTR" Length="150" />
<Column Name="fields.reporter.avatarUrls.48x48" Label="ReporterAvatarUrls48x48" DataType="DT_WSTR" Length="500" />
<Column Name="fields.reporter.avatarUrls.24x24" Label="ReporterAvatarUrls24x24" DataType="DT_WSTR" Length="500" />
<Column Name="fields.reporter.avatarUrls.16x16" Label="ReporterAvatarUrls16x16" DataType="DT_WSTR" Length="500" />
<Column Name="fields.reporter.avatarUrls.32x32" Label="ReporterAvatarUrls32x32" DataType="DT_WSTR" Length="500" />
<Column Name="fields.reporter.displayName" Label="ReporterDisplayName" DataType="DT_WSTR" Length="100" />
<Column Name="fields.reporter.active" Label="ReporterActive" DataType="DT_BOOL" />
<Column Name="fields.reporter.timeZone" Label="ReporterTimeZone" DataType="DT_WSTR" Length="100" />
<Column Name="fields.reporter.accountType" Label="ReporterAccountType" DataType="DT_WSTR" Length="100" />
<Column Name="fields.aggregateprogress.percent" Label="AggregateProgressPercent" DataType="DT_I4" />
<Column Name="fields.aggregateprogress.progress" Label="AggregateProgressInSeconds" DataType="DT_I8" />
<Column Name="fields.aggregateprogress.total" Label="AggregateProgressTotalInSeconds" DataType="DT_I8" />
<Column Name="fields.environment.content" Label="Environment" DataType="DT_NTEXT" Functions="[$1]|~|$..content[*].text|~| ,FUN_JSON_TO_TEXT" Desc="Join formatted text blocks using space separator (new lines will be lost)"/>
<Column Name="fields.environment.content" Label="EnvironmentFormatted" DataType="DT_NTEXT" />
<Column Name="fields.progress.progress" Label="ProgressInSeconds" DataType="DT_I8" />
<Column Name="fields.progress.total" Label="ProgressTotalInSeconds" DataType="DT_I8" />
<Column Name="fields.votes.self" Label="VotesSelf" DataType="DT_WSTR" Length="150" />
<Column Name="fields.votes.votes" Label="Votes" DataType="DT_I4" />
<Column Name="fields.votes.hasVoted" Label="HasVoted" DataType="DT_BOOL"/>
<Column Name="fields.parent.id" Label="ParentId" DataType="DT_I4" />
<Column Name="fields.parent.key" Label="ParentKey" DataType="DT_WSTR" Length="50" />
<Column Name="fields.parent.self" Label="ParentSelf" DataType="DT_WSTR" Length="150" />
<Column Name="fields.parent.fields.summary" Label="ParentSummary" DataType="DT_WSTR" Length="2000" />
<Column Name="fields.parent.fields.status.self" Label="ParentStatusSelf" DataType="DT_WSTR" Length="150" />
<Column Name="fields.parent.fields.status.description" Label="ParentStatusDescription" DataType="DT_WSTR" Length="2000" />
<Column Name="fields.parent.fields.status.iconUrl" Label="ParentStatusIconUrl" DataType="DT_WSTR" Length="500" />
<Column Name="fields.parent.fields.status.name" Label="ParentStatusName" DataType="DT_WSTR" Length="100" />
<Column Name="fields.parent.fields.status.id" Label="ParentStatusId" DataType="DT_I8" />
<Column Name="fields.parent.fields.status.statusCategory.self" Label="ParentStatusCategorySelf" DataType="DT_WSTR" Length="150" />
<Column Name="fields.parent.fields.status.statusCategory.id" Label="ParentStatusCategoryId" DataType="DT_I4" />
<Column Name="fields.parent.fields.status.statusCategory.key" Label="ParentStatusCategoryKey" DataType="DT_WSTR" Length="50" />
<Column Name="fields.parent.fields.status.statusCategory.colorName" Label="ParentStatusCategoryColorName" DataType="DT_WSTR" Length="50" />
<Column Name="fields.parent.fields.status.statusCategory.name" Label="ParentStatusCategoryName" DataType="DT_WSTR" Length="50" />
<Column Name="fields.parent.fields.priority.self" Label="ParentprioritySelf" DataType="DT_WSTR" Length="150" />
<Column Name="fields.parent.fields.priority.iconUrl" Label="ParentpriorityIconUrl" DataType="DT_WSTR" Length="500" />
<Column Name="fields.parent.fields.priority.name" Label="ParentpriorityName" DataType="DT_WSTR" Length="50" />
<Column Name="fields.parent.fields.priority.id" Label="ParentpriorityId" DataType="DT_I4" />
<Column Name="fields.parent.fields.issuetype.self" Label="ParentIssueTypeSelf" DataType="DT_WSTR" Length="150" />
<Column Name="fields.parent.fields.issuetype.id" Label="ParentIssueTypeId" DataType="DT_I8" />
<Column Name="fields.parent.fields.issuetype.description" Label="ParentIssueTypeDescription" DataType="DT_WSTR" Length="2000" />
<Column Name="fields.parent.fields.issuetype.iconUrl" Label="ParentIssueTypeIconUrl" DataType="DT_WSTR" Length="500" />
<Column Name="fields.parent.fields.issuetype.name" Label="ParentIssueTypeName" DataType="DT_WSTR" Length="50" />
<Column Name="fields.parent.fields.issuetype.subtask" Label="ParentIssueTypeSubtask" DataType="DT_BOOL" />
<Column Name="fields.parent.fields.issuetype.avatarId" Label="ParentIssueTypeAvatarId" DataType="DT_I4" />
<Column Name="fields.parent.fields.issuetype.entityId" Label="ParentIssueTypeEntityId" DataType="DT_GUID" />
<Column Name="fields.parent.fields.issuetype.hierarchyLevel" Label="ParentIssueTypeHierarchyLevel" DataType="DT_I4" />
<Column Name="fields.assignee" Label="Assignee" DataType="DT_WSTR" Length="150" />
<Column Name="fields.resolution.self" Label="ResolutionSelf" DataType="DT_WSTR" Length="150" />
<!-- DYNAMIC CUSTOM FIELDS -->
<!-- Textbox, Number, Date, Url... customfield_123: "xx" or customfield_123: ["tag1", "tag2"] -->
<Column Name="fields.[$parent.key$]" Label="[$parent.key$]" Desc="[$parent.name$]" DataType="DT_WSTR" Length="2000"
Expand="True"
DataEndPoint="get_custom_fields"
DataEndPointParameters="Filter=$.data[?(@.custom==true)]"
ColumnInfoMap="Name=key;DataType=schema.type"
DataTypeMap="DT_DBDATE=date;DT_DBTIMESTAMP=datetime;DT_NUMERIC=number;DT_RAW=array;DT_BOOL=?;"
/>
<!-- Dropdown Single / Radio (value - i.e. label)... customfield_123: {value: xx} -->
<Column Name="fields.[$parent.key$].value" Label="[$parent.key$]_value" Desc="[$parent.name$]_value" DataType="DT_WSTR" Length="2000"
Expand="True"
DataEndPoint="get_custom_fields"
DataEndPointParameters="Filter=$.data[?(@.custom==true && @schema.type=='option')]"
ColumnInfoMap="Name=key;DataType=schema.type"
DataTypeMap="DT_DBDATE=date;DT_DBTIMESTAMP=datetime;DT_NUMERIC=number;DT_RAW=array;DT_BOOL=?;"
/>
<!-- Dropdown Single / Radio (id)... customfield_123: {id: xx} -->
<Column Name="fields.[$parent.key$].id" Label="[$parent.key$]_id" Desc="[$parent.name$]_id" DataType="DT_WSTR" Length="20"
Expand="True"
DataEndPoint="get_custom_fields"
DataEndPointParameters="Filter=$.data[?(@.custom==true && @schema.type=='option')]"
ColumnInfoMap="Name=key;DataType=schema.type"
DataTypeMap="DT_DBDATE=date;DT_DBTIMESTAMP=datetime;DT_NUMERIC=number;DT_RAW=array;DT_BOOL=?;"
/>
<!-- Multi Select Checkbox / List ... customfield_123: [{value: xx}, {value: yy}, {value: zz}]. $.data[*].value is used becuase when Array format needs filter we wrap behind the scene unde { data: <content> } so filter can be invoked at root level-->
<Column Name="fields.[$parent.key$]" Label="[$parent.key$]_values" Desc="[$parent.name$]_values" DataType="DT_WSTR" Length="2000"
Expand="True"
DataEndPoint="get_custom_fields"
DataEndPointParameters="Filter=$.data[?(@.custom==true && @schema.type=='array')]"
ColumnInfoMap="Name=key;DataType=schema.type"
DataTypeMap="DT_DBDATE=date;DT_DBTIMESTAMP=datetime;DT_NUMERIC=number;DT_RAW=array;DT_BOOL=?;"
Functions="[$1]|~|$.data[*].value|~|,,FUN_JSON_TO_TEXT"
/>
<!-- Paragraph ... customfield_123: { content:[ {content: [{text: zzz},{text: zzz}...]} , {content: [{text: zzz}, {text: zzz},..]} , ...] } -->
<Column Name="fields.[$parent.key$].content" Label="[$parent.key$]_text" Desc="[$parent.name$]_text Join formatted text blocks using space separator (new lines will be lost)" DataType="DT_NTEXT"
Expand="True"
DataEndPoint="get_custom_fields"
DataEndPointParameters="Filter=$.data[?(@.custom==true && @schema.custom=~/textarea$/)]"
ColumnInfoMap="Name=key;"
Functions="[$1]|~|$..text|~| ,FUN_JSON_TO_TEXT"
/>
</OutputColumns>
</EndPoint>
<!-- INSERT ISSUES -->
<EndPoint Name="create_issues" Label="Create Issues" Url="/issue/bulk" Method="POST"
BatchSize="100" ContentType="application/json" DotAsPath="True" Filter="$.issues[*]">
<Body>
<![CDATA[{ "issueUpdates" : {$rows$} } ]]>
</Body>
<InputColumns>
<Column Name="fields.project.id" Label="ProjectId" DataType="DT_WSTR" Length="50" Desc="Project in which to create an issue. Must use this field or ProjectKey"/>
<Column Name="fields.project.key" Label="ProjectKey" DataType="DT_WSTR" Length="50" Desc="Project in which to create an issue. Must use this field or ProjectId"/>
<Column Name="fields.summary" Label="Summary" DataType="DT_WSTR" Length="150" Desc="Issue title"/>
<Column Name="fields.description" Label="Description" Raw="True"
ValueTemplate="{ "type": "doc", "version": 1, "content": [ { "type": "paragraph", "content": [ { "text": "<<{$value$},FUN_JSONENC>>", "type": "text" } ] } ] }"
DataType="DT_NTEXT"
/>
<Column Name="fields.description" Label="DescriptionFormatted" DataType="DT_NTEXT" Raw="True"
Example="{ "type": "doc", "version": 1, "content": [ { "type": "paragraph", "content": [ { "text": "Your description", "type": "text" } ] } ] }"
HelpLink="https://community.developer.atlassian.com/t/how-to-show-description-text-in-visual-format-instead-of-text-while-creating-issue-using-rest-call/37667"
/>
<Column Name="fields.environment" Label="Environment" Raw="True"
ValueTemplate="{ "type": "doc", "version": 1, "content": [ { "type": "paragraph", "content": [ { "text": "<<{$value$},FUN_JSONENC>>", "type": "text" } ] } ] }"
DataType="DT_NTEXT"
/>
<Column Name="fields.environment_formatted" Label="EnvironmentFormatted" Desc="" DataType="DT_NTEXT"
Raw="True"
Example="{ "type": "doc", "version": 1, "content": [ { "type": "paragraph", "content": [ { "text": "Your description", "type": "text" } ] } ] }"
HelpLink="https://community.developer.atlassian.com/t/how-to-show-description-text-in-visual-format-instead-of-text-while-creating-issue-using-rest-call/37667"
/>
<Column Name="fields.issuetype.id" Label="IssueTypeId" DataType="DT_WSTR" Length="50" />
<Column Name="fields.issuetype.name" Label="IssueTypeName" DataType="DT_WSTR" Length="50"/>
<Column Name="fields.assignee.id" Label="AssigneeUserId" DataType="DT_WSTR" Length="50" Desc="User ID representing a user assigned to an issue" Example="12551:12be2e5a-9ebf-1235-be58-c3ea8c98b9e2"/>
<Column Name="fields.reporter.id" Label="ReporterUserId" DataType="DT_WSTR" Length="50" Desc="User ID representing a user who reported an issue" Example="12551:12be2e5a-9ebf-1235-be58-c3ea8c98b9e2"/> />
<Column Name="fields.labels" Label="Labels" Raw="True" Desc="Labels, assigned to an issue in JSON string array format" Example="["myBadge","anotherBadge"]" DataType="DT_WSTR" Length="2000" />
<Column Name="fields.duedate" Label="DueDate" DataType="DT_DBDATE" Desc="A date refering to due date of an issue" Example="2029-11-25" />
<!-- all custom fields (to treat type as raw add in RawInfoMap and DT_RAW both places for now TFS8293)-->
<Column Name="fields.[$parent.key$]" Label="[$parent.key$]" Desc="[$parent.name$]" DataType="DT_WSTR" Length="2000"
Expand="True"
DataEndPoint="get_fields"
DataEndPointParameters="Filter=$.data[?(@.custom==true && @schema.custom=~/^((?!textarea).)*$/)]"
ColumnInfoMap="Name=key;DataType=schema.type;Raw=schema.type"
RawInfoMap="option,array,user,team"
DataTypeMap="DT_DBDATE=date;DT_DBTIMESTAMP=datetime;DT_NUMERIC=number;DT_RAW=option,array,user,team;DT_BOOL=?;"
/>
<!-- all custom fields with type=option (dropdown) support easy write by value or id -->
<Column Name="fields.[$parent.key$].value" Label="[$parent.key$]_value" Desc="Set [$parent.name$] value by option text" DataType="DT_WSTR" Length="2000"
Expand="True"
DataEndPoint="get_fields"
DataEndPointParameters="Filter=$.data[?(@.custom==true && @schema.type=='option')]"
ColumnInfoMap="Name=key;DataType=schema.type"
DataTypeMap="DT_DBDATE=date;DT_DBTIMESTAMP=datetime;DT_NUMERIC=number;DT_BOOL=?;"
/>
<Column Name="fields.[$parent.key$].id" Label="[$parent.key$]_id" Desc="Set [$parent.name$] value by option item id" DataType="DT_WSTR" Length="20"
Expand="True"
DataEndPoint="get_fields"
DataEndPointParameters="Filter=$.data[?(@.custom==true && @schema.type=='option')]"
ColumnInfoMap="Name=key;DataType=schema.type"
DataTypeMap="DT_DBDATE=date;DT_DBTIMESTAMP=datetime;DT_NUMERIC=number;DT_BOOL=?;"
/>
<!-- Paragraph ... customfield_123: { content:[ {content: [{text: zzz},{text: zzz}...]} , {content: [{text: zzz}, {text: zzz},..]} , ...] } -->
<Column Name="fields.[$parent.key$]" Label="[$parent.key$]_text" Desc="[$parent.name$]_text" DataType="DT_NTEXT"
Expand="True"
DataEndPoint="get_custom_fields"
DataEndPointParameters="Filter=$.data[?(@.custom==true && @schema.custom=~/textarea$/)]"
ColumnInfoMap="Name=key"
Raw="True"
ValueTemplate="{
"type": "doc",
"version": 1,
"content": [
{
"type": "paragraph",
"content": [
{
"type": "text",
"text": "<<{$value$},FUN_JSONENC>>"
}
]
}
]
}"
/>
</InputColumns>
<OutputColumns>
<Column Name="id" DataType="DT_WSTR" Length="50" />
<Column Name="key" DataType="DT_WSTR" Length="50" />
<Column Name="self" DataType="DT_WSTR" Length="450" />
</OutputColumns>
</EndPoint>
<!-- UPDATE ISSUES -->
<EndPoint Name="update_issue" Label="Update Issue"
Url="/issue/[$IssueIdOrKey$]" Method="PUT"
ContentType="application/json" DotAsPath="True" JsonRowFormat="Multicontent">
<Params>
<Param Name="IssueIdOrKey" Type="Placeholder"
Desc="Enter Issue ID or Key"
Example="100002 or MYISSUE-1"
ReadAs="Id"
IsKey="True"
/>
<Param Name="NotifyUsers" Key="notifyUsers" Options=";true;false" Type="Query" Value="false" Desc="Whether a notification email about the issue update is sent to all watchers. Possible values: true, false" Example="false" />
<Param Name="OverrideScreenSecurity" Options=";true;false" Key="overrideScreenSecurity" Type="Query" Value="false" Desc="Whether screen security should be overridden to enable hidden fields to be edited. Possible values: true, false" Example="true" />
<Param Name="OverrideEditableFlag" Options=";true;false" Key="overrideEditableFlag" Type="Query" Value="false" Desc="Whether screen security should be overridden to enable uneditable fields to be edited. Possible values: true, false" Example="false" />
<Param Name="ContinueOn404Error" Key="ContineOn404Error" Label="Continue On 404 Error (When record not found)" Type="Property" Value="False" />
</Params>
<Body>
<![CDATA[ {$rows$} ]]>
</Body>
<OutputColumns>
<Column Name="body" DataType="DT_WSTR" />
</OutputColumns>
<InputColumns>
<Column Name="IssueIdOrKey" Label="Id" MapToParam="True" Key="True" DataType="DT_STR" Length="50" ExcludeFromRowMap="True" Desc="Issue Id or Key" />
<Column Name="fields.project.id" Label="ProjectId" DataType="DT_WSTR" Length="50" Desc="Project in which to create an issue. Must use this field or ProjectKey"/>
<Column Name="fields.project.key" Label="ProjectKey" DataType="DT_WSTR" Length="50" Desc="Project in which to create an issue. Must use this field or ProjectId"/>
<Column Name="fields.summary" Label="Summary" DataType="DT_WSTR" Length="150" Desc="Issue title"/>
<Column Name="fields.description" Label="Description" Raw="True"
ValueTemplate="{ "type": "doc", "version": 1, "content": [ { "type": "paragraph", "content": [ { "text": "<<{$value$},FUN_JSONENC>>", "type": "text" } ] } ] }"
DataType="DT_NTEXT"
/>
<Column Name="fields.description" Label="DescriptionFormatted" DataType="DT_NTEXT"
Raw="True"
Example="{ "type": "doc", "version": 1, "content": [ { "type": "paragraph", "content": [ { "text": "Your description", "type": "text" } ] } ] }"
HelpLink="https://community.developer.atlassian.com/t/how-to-show-description-text-in-visual-format-instead-of-text-while-creating-issue-using-rest-call/37667"
/>
<Column Name="fields.environment" Label="Environment" Raw="True"
ValueTemplate="{ "type": "doc", "version": 1, "content": [ { "type": "paragraph", "content": [ { "text": "<<{$value$},FUN_JSONENC>>", "type": "text" } ] } ] }"
DataType="DT_NTEXT"
/>
<Column Name="fields.environment_formatted" Label="EnvironmentFormatted" Desc="" DataType="DT_NTEXT"
Raw="True"
Example="{ "type": "doc", "version": 1, "content": [ { "type": "paragraph", "content": [ { "text": "Your description", "type": "text" } ] } ] }"
HelpLink="https://community.developer.atlassian.com/t/how-to-show-description-text-in-visual-format-instead-of-text-while-creating-issue-using-rest-call/37667"
/>
<Column Name="fields.issuetype.id" Label="IssueTypeId" DataType="DT_WSTR" Length="50" />
<Column Name="fields.issuetype.name" Label="IssueTypeName" DataType="DT_WSTR" Length="50"/>
<Column Name="fields.assignee.id" Label="AssigneeUserId" DataType="DT_WSTR" Length="50" Desc="User ID representing a user assigned to an issue" Example="12551:12be2e5a-9ebf-1235-be58-c3ea8c98b9e2"/>
<Column Name="fields.reporter.id" Label="ReporterUserId" DataType="DT_WSTR" Length="50" Desc="User ID representing a user who reported an issue" Example="12551:12be2e5a-9ebf-1235-be58-c3ea8c98b9e2"/> />
<Column Name="fields.labels" Label="Labels" Desc="Labels, assigned to an issue in JSON string array format"
Raw="True"
Example="["myBadge","anotherBadge"]"
DataType="DT_WSTR"
Length="2000"
/>
<Column Name="fields.duedate" Label="DueDate" DataType="DT_DBDATE" Desc="A date refering to due date of an issue" Example="2029-11-25" />
<!-- all custom fields (to treat type as raw add in RawInfoMap and DT_RAW both places for now TFS8293)-->
<Column Name="fields.[$parent.key$]" Label="[$parent.key$]" Desc="[$parent.name$]" DataType="DT_WSTR" Length="2000"
Expand="True"
DataEndPoint="get_fields"
DataEndPointParameters="Filter=$.data[?(@.custom==true && @schema.custom=~/^((?!textarea).)*$/)]"
ColumnInfoMap="Name=key;DataType=schema.type;Raw=schema.type"
RawInfoMap="option,array,user,team"
DataTypeMap="DT_DBDATE=date;DT_DBTIMESTAMP=datetime;DT_NUMERIC=number;DT_RAW=option,array,user,team;DT_BOOL=?;"
/>
<!-- all custom fields with type=option (dropdown) support easy write by value or id -->
<Column Name="fields.[$parent.key$].value" Label="[$parent.key$]_value" Desc="Set [$parent.name$] value by option text" DataType="DT_WSTR" Length="2000"
Expand="True"
DataEndPoint="get_fields"
DataEndPointParameters="Filter=$.data[?(@.custom==true && @schema.type=='option')]"
ColumnInfoMap="Name=key;DataType=schema.type"
DataTypeMap="DT_DBDATE=date;DT_DBTIMESTAMP=datetime;DT_NUMERIC=number;DT_BOOL=?;"
/>
<Column Name="fields.[$parent.key$].id" Label="[$parent.key$]_id" Desc="Set [$parent.name$] value by option item id" DataType="DT_WSTR" Length="20"
Expand="True"
DataEndPoint="get_fields"
DataEndPointParameters="Filter=$.data[?(@.custom==true && @schema.type=='option')]"
ColumnInfoMap="Name=key;DataType=schema.type"
DataTypeMap="DT_DBDATE=date;DT_DBTIMESTAMP=datetime;DT_NUMERIC=number;DT_BOOL=?;"
/>
<!-- Paragraph ... customfield_123: { content:[ {content: [{text: zzz},{text: zzz}...]} , {content: [{text: zzz}, {text: zzz},..]} , ...] } -->
<Column Name="fields.[$parent.key$]" Label="[$parent.key$]_text" Desc="[$parent.name$]_text" DataType="DT_NTEXT"
Expand="True"
DataEndPoint="get_custom_fields"
DataEndPointParameters="Filter=$.data[?(@.custom==true && @schema.custom=~/textarea$/)]"
ColumnInfoMap="Name=key"
Raw="True"
ValueTemplate="{
"type": "doc",
"version": 1,
"content": [
{
"type": "paragraph",
"content": [
{
"type": "text",
"text": "<<{$value$},FUN_JSONENC>>"
}
]
}
]
}"
/>
</InputColumns>
</EndPoint>
<!-- DELETE ISSUE -->
<EndPoint Name="delete_issue" Label="Delete Issue" Url="/issue/[$IssueIdOrKey$]" Method="DELETE"
ResponseFormat="Json" >
<Params>
<Param Name="IssueIdOrKey" IsKey="True" Type="Placeholder" Desc="Enter Issue ID or Key" Example="100002 or MYISSUE-1"/>
<Param Name="ContinueOn404Error" Key="ContineOn404Error" Label="Continue On 404 Error (When record not found)" Type="Property" Value="False" />
</Params>
<OutputColumns>
<Column Name="Response" DataType="DT_NTEXT" />
</OutputColumns>
<InputColumns>
<Column Name="IssueIdOrKey" Label="Id" MapToParam="True" DataType="DT_WSTR" Key="True" />
</InputColumns>
</EndPoint>
<!--
========
WORKLOGS (Lookup by Id not possible due to 2 params required so use child endpoint technique)
========
-->
<EndPoint Name="get_worklogs" Label="Read Worklogs" Template="IssuesTemplate"
Method="GET" Filter="$.issues[*]">
<Params>
<Param Name="Key"
Label="Issue Key(s) or Ids - Comma Separated (PRJA-10,PRJA-13)"
IsKey="True"
ReadAs="IssueId"
Key="jql"
Required="False"
Type="Query"
VisibleIf="SearchBy==Key"
Functions="{$rows$}"
ValueTemplate="Key in ({$value$})"
Desc="Use comma separated IDs or Keys for Issues. 1000 ids max or JIRA internal limit for Payload."
/>
</Params>
<OutputColumns>
<Column Name="issueId" Label="IssueId" DataType="DT_WSTR" Length="20" />
<Column Name="p1_key" Label="IssueKey" DataType="DT_WSTR" Length="50" />
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="20" />
<Column Name="self" Label="Self" DataType="DT_WSTR" Length="150" />
<Column Name="P_startAt" Label="StartAt" DataType="DT_I4" />
<Column Name="P_maxResults" Label="MaxResults" DataType="DT_I4" />
<Column Name="P_total" Label="Total" DataType="DT_I8" />
<Column Name="author.self" Label="AuthorSelf" DataType="DT_WSTR" Length="200" />
<Column Name="author.accountId" Label="AuthorAccountId" DataType="DT_WSTR" Length="150" />
<Column Name="author.emailAddress" Label="AuthorEmailAddress" DataType="DT_WSTR" Length="200" />
<Column Name="author.displayName" Label="AuthorDisplayName" DataType="DT_WSTR" Length="200" />
<Column Name="author.active" Label="AuthorIsActive" DataType="DT_BOOL" />
<Column Name="author.timeZone" Label="AuthorTimeZone" DataType="DT_WSTR" Length="100" />
<Column Name="author.accountType" Label="AuthorAccountType" DataType="DT_WSTR" Length="50" />
<Column Name="comment.content" Label="CommentFormatted" DataType="DT_NTEXT"/>
<Column Name="comment.content" Label="Comment"
DataType="DT_NTEXT"
Functions="[$1]|~|$..content[*].text|~| ,FUN_JSON_TO_TEXT"
Desc="Join formatted text blocks using space separator (new lines will be lost)"/>
<Column Name="updateAuthor.self" Label="UpdateAuthorSelf" DataType="DT_WSTR" Length="500" />
<Column Name="updateAuthor.accountId" Label="UpdateAuthorAccountId" DataType="DT_WSTR" Length="200" />
<Column Name="updateAuthor.emailAddress" Label="UpdateAuthorEmailAddress" DataType="DT_WSTR" Length="200" />
<Column Name="updateAuthor.displayName" Label="UpdateAuthorDisplayName" DataType="DT_WSTR" Length="200" />
<Column Name="updateAuthor.active" Label="UpdateAuthorActive" DataType="DT_BOOL" />
<Column Name="updateAuthor.timeZone" Label="UpdateAuthorTimeZone" DataType="DT_WSTR" Length="80" />
<Column Name="updateAuthor.accountType" Label="UpdateAuthorAccountType" DataType="DT_WSTR" Length="50" />
<Column Name="created" Label="Created" DataType="DT_DBTIMESTAMP" />
<Column Name="updated" Label="Updated" DataType="DT_DBTIMESTAMP" />
<Column Name="started" Label="Started" DataType="DT_DBTIMESTAMP" />
<Column Name="timeSpent" Label="TimeSpent" DataType="DT_WSTR" Length="50" />
<Column Name="timeSpentSeconds" Label="TimeSpentInSeconds" DataType="DT_I8" />
<Column Name="author.avatarUrls.48x48" Label="AuthorAvatarUrls48x48" DataType="DT_WSTR" Length="500" />
<Column Name="author.avatarUrls.24x24" Label="AuthorAvatarUrls24x24" DataType="DT_WSTR" Length="500" />
<Column Name="author.avatarUrls.16x16" Label="AuthorAvatarUrls16x16" DataType="DT_WSTR" Length="500" />
<Column Name="author.avatarUrls.32x32" Label="AuthorAvatarUrls32x32" DataType="DT_WSTR" Length="500" />
<Column Name="updateAuthor.avatarUrls.48x48" Label="UpdateAuthorAvatarUrls48x48" DataType="DT_WSTR" Length="500" />
<Column Name="updateAuthor.avatarUrls.24x24" Label="UpdateAuthorAvatarUrls24x24" DataType="DT_WSTR" Length="500" />
<Column Name="updateAuthor.avatarUrls.16x16" Label="UpdateAuthorAvatarUrls16x16" DataType="DT_WSTR" Length="500" />
<Column Name="updateAuthor.avatarUrls.32x32" Label="UpdateAuthorAvatarUrls32x32" DataType="DT_WSTR" Length="500" />
</OutputColumns>
<EndPoint Name="get_issue_worklogs" Label="Read Worklogs"
Url="/issue/[$parent.id$]/worklog" Method="GET"
Filter="$.worklogs[*]" IncludeParentColumns="True">
</EndPoint>
</EndPoint>
<!-- CREATE WORKLOGS -->
<EndPoint Template="WorklogTemplate" Name="create_worklog" Label="Create Worklog" Url="/issue/[$IssueIdOrKey$]/worklog" Method="POST"
ResponseFormat="Json"
JsonRowFormat="Multicontent"
ContentType="application/json"
Body="{$rows$}">
</EndPoint>
<!-- UPDATE WORKLOGS -->
<EndPoint Template="WorklogTemplate" Name="update_worklog" Label="Update Worklog" Url="/issue/[$IssueIdOrKey$]/worklog/[$WorklogId$]" Method="PUT"
ResponseFormat="Json"
JsonRowFormat="Multicontent"
ContentType="application/json"
Body="{$rows$}">
<Params>
<Param Name="IssueIdOrKey" Type="Placeholder" />
<Param Name="WorklogId" Type="Placeholder" />
<Param Name="ContinueOn404Error" Key="ContineOn404Error" Label="Continue On 404 Error (When record not found)" Type="Property" Value="False" />
</Params>
<InputColumns>
<Column Name="WorklogId" Label="Id" MapToParam="True" Key="True" DataType="DT_STR" Length="50" ExcludeFromRowMap="True" />
</InputColumns>
</EndPoint>
<!-- DELETE WORKLOGS -->
<EndPoint Name="delete_worklogs" Label="Delete Worklog" Url="/issue/[$IssueIdOrKey$]/worklog/[$WorklogId$]" Method="DELETE"
ResponseFormat="Json" >
<Params>
<Param Name="IssueIdOrKey" Type="Placeholder" />
<Param Name="WorklogId" Type="Placeholder" />
<Param Name="ContinueOn404Error" Key="ContineOn404Error" Label="Continue On 404 Error (When record not found)" Type="Property" Value="False" />
</Params>
<OutputColumns>
<Column Name="Response" DataType="DT_NTEXT" />
</OutputColumns>
<InputColumns>
<Column Name="WorklogId" Label="Id" DataType="DT_WSTR" MapToParam="True" Key="True" />
</InputColumns>
</EndPoint>
<!--
========
COMMENTS (Lookup by Id not possible due to 2 params required so use child endpoint technique)
========
-->
<EndPoint Name="get_comments" Label="Read Comments" Template="IssuesTemplate"
Url="/search?startAt=0&maxResults=50" Method="GET" Filter="$.issues[*]">
<Params>
<Param Name="Key"
Label="Issue Key(s) or Ids - Comma Separated (PRJA-10,PRJA-13)"
IsKey="True"
ReadAs="IssueId"
Key="jql"
Required="False"
Type="Query"
VisibleIf="SearchBy==Key"
Functions="{$rows$}"
ValueTemplate="Key in ({$value$})"
Desc="Use comma separated IDs or Keys for Issues. 1000 ids max or JIRA internal limit for Payload."
/>
</Params>
<OutputColumns>
<Column Name="p1_id" Label="IssueId" DataType="DT_WSTR" Length="20" />
<Column Name="p1_key" Label="IssueKey" DataType="DT_WSTR" Length="50" />
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="20" />
<Column Name="created" Label="Created" DataType="DT_DBTIMESTAMP" />
<Column Name="updated" Label="Updated" DataType="DT_DBTIMESTAMP" />
<Column Name="self" Label="Self" DataType="DT_WSTR" Length="150" />
<Column Name="body.type" Label="BodyType" DataType="DT_WSTR" Length="50" />
<Column Name="body.content" Label="BodyContent" DataType="DT_NTEXT" />
<Column Name="body.content" Label="BodyContentText" DataType="DT_NTEXT" Functions="[$1]|~|$..content[*].text|~| ,FUN_JSON_TO_TEXT" Desc="Join formatted text blocks using space separator (new lines will be lost)" />
<Column Name="body.version" Label="BodyVersion" DataType="DT_NUMERIC" />
<Column Name="author.self" Label="AuthorSelf" DataType="DT_WSTR" Length="150" />
<Column Name="author.accountId" Label="AuthorAccountId" DataType="DT_WSTR" Length="100" />
<Column Name="author.emailAddress" Label="AuthorEmailAddress" DataType="DT_WSTR" Length="100" />
<Column Name="author.avatarUrls.48x48" Label="AuthorAvatarUrls48x48" DataType="DT_WSTR" Length="500" />
<Column Name="author.avatarUrls.24x24" Label="AuthorAvatarUrls24x24" DataType="DT_WSTR" Length="500" />
<Column Name="author.avatarUrls.16x16" Label="AuthorAvatarUrls16x16" DataType="DT_WSTR" Length="500" />
<Column Name="author.avatarUrls.32x32" Label="AuthorAvatarUrls32x32" DataType="DT_WSTR" Length="500" />
<Column Name="author.displayName" Label="AuthorDisplayName" DataType="DT_WSTR" Length="100" />
<Column Name="author.active" Label="AuthorActive" DataType="DT_BOOL" />
<Column Name="author.timeZone" Label="AuthorTimeZone" DataType="DT_WSTR" Length="100" />
<Column Name="author.accountType" Label="AuthorAccountType" DataType="DT_WSTR" Length="50" />
<Column Name="updateAuthor.self" Label="UpdateAuthorSelf" DataType="DT_WSTR" Length="150" />
<Column Name="updateAuthor.accountId" Label="UpdateAuthorAccountId" DataType="DT_WSTR" Length="50" />
<Column Name="updateAuthor.emailAddress" Label="UpdateAuthorEmailAddress" DataType="DT_WSTR" Length="100" />
<Column Name="updateAuthor.avatarUrls.48x48" Label="UpdateAuthorAvatarUrls48x48" DataType="DT_WSTR" Length="500" />
<Column Name="updateAuthor.avatarUrls.24x24" Label="UpdateAuthorAvatarUrls24x24" DataType="DT_WSTR" Length="500" />
<Column Name="updateAuthor.avatarUrls.16x16" Label="UpdateAuthorAvatarUrls16x16" DataType="DT_WSTR" Length="500" />
<Column Name="updateAuthor.avatarUrls.32x32" Label="UpdateAuthorAvatarUrls32x32" DataType="DT_WSTR" Length="500" />
<Column Name="updateAuthor.displayName" Label="UpdateAuthorDisplayName" DataType="DT_WSTR" Length="100" />
<Column Name="updateAuthor.active" Label="UpdateAuthorActive" DataType="DT_BOOL" />
<Column Name="updateAuthor.timeZone" Label="UpdateAuthorTimeZone" DataType="DT_WSTR" Length="44" />
<Column Name="updateAuthor.accountType" Label="UpdateAuthorAccountType" DataType="DT_WSTR" Length="36" />
<Column Name="jsdPublic" DataType="DT_BOOL" />
</OutputColumns>
<EndPoint Name="get_issue_comments" Url="/issue/[$parent.key$]/comment" Method="GET" Filter="$.comments[*]">
</EndPoint>
</EndPoint>
<!-- =====================
Get changelogs
====================== -->
<EndPoint Name="get_changelogs"
Label="Read Changelogs"
Desc="Returns a paginated list of all changelogs for an issue sorted by date, starting from the oldest. OAuth scopes required: read:issue-meta:jira, read:avatar:jira, read:issue.changelog:jira"
Url="/issue/[$IssueIdOrKey$]/changelog?startAt=0&maxResults=50"
Method="GET"
Filter="$.values[*]">
<Params>
<Param Name="PagingMode" Type="Property" Value="1" />
<Param Name="PagingByUrlAttributeName" Type="Property" Value="startAt" />
<Param Name="PagingIncrementBy" Type="Property" Value="50" />
<Param Name="IssueIdOrKey" Label="IssueId Or Key" Type="Placeholder" Required="True" />
</Params>
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="200" />
<Column Name="author.self" Label="AuthorSelfURL" DataType="DT_WSTR" Length="1000" />
<Column Name="author.accountId" Label="AuthorAccountId" DataType="DT_WSTR" Length="200" />
<Column Name="author.emailAddress" Label="AuthorEmailAddress" DataType="DT_WSTR" Length="500" />
<Column Name="author.avatarUrls.48x48" Label="AvatarUrls48x48" DataType="DT_WSTR" Length="1000" />
<Column Name="author.avatarUrls.24x24" Label="AvatarUrls24x24" DataType="DT_WSTR" Length="1000" />
<Column Name="author.avatarUrls.16x16" Label="AvatarUrls16x16" DataType="DT_WSTR" Length="1000" />
<Column Name="author.avatarUrls.32x32" Label="AvatarUrls32x32" DataType="DT_WSTR" Length="1000" />
<Column Name="author.displayName" Label="AuthorDisplayName" DataType="DT_WSTR" Length="1000" />
<Column Name="author.active" Label="Is AuthorActive" DataType="DT_BOOL" />
<Column Name="author.timeZone" Label="AuthorTimeZone" DataType="DT_WSTR" Length="200" />
<Column Name="created" Label="CreatedDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="items" Label="Items" DataType="DT_WSTR" Length="4000" />
</OutputColumns>
</EndPoint>
<!-- =====================
Get changelog details
====================== -->
<EndPoint Name="get_changelog_details"
Label="Read Changelog Details"
Desc="Returns a paginated list of all changelogs and fields updated for an issue sorted by date, starting from the oldest. OAuth scopes required: read:issue-meta:jira, read:avatar:jira, read:issue.changelog:jira"
Url="/issue/[$IssueIdOrKey$]/changelog?startAt=0&maxResults=50"
Method="GET"
Filter="$.values[*].items[*]"
IncludeParentColumns="True" >
<Params>
<Param Name="PagingMode" Type="Property" Value="1" />
<Param Name="PagingByUrlAttributeName" Type="Property" Value="startAt" />
<Param Name="PagingIncrementBy" Type="Property" Value="50" />
<Param Name="IssueIdOrKey" Label="IssueId Or Key" Type="Placeholder" Required="True" />
</Params>
<OutputColumns>
<Column Name="P_values_id" Label="Id" DataType="DT_WSTR" Length="200" />
<Column Name="P_values_author_self" Label="AuthorSelfURL" DataType="DT_WSTR" Length="1000" />
<Column Name="P_values_author_accountId" Label="AuthorAccountId" DataType="DT_WSTR" Length="200" />
<Column Name="P_values_author_emailAddress" Label="AuthorEmailAddress" DataType="DT_WSTR" Length="500" />
<Column Name="P_values_author_avatarUrls_48x48" Label="AvatarUrls48x48" DataType="DT_WSTR" Length="1000" />
<Column Name="P_values_author_avatarUrls_24x24" Label="AvatarUrls24x24" DataType="DT_WSTR" Length="1000" />
<Column Name="P_values_author_avatarUrls_16x16" Label="AvatarUrls16x16" DataType="DT_WSTR" Length="1000" />
<Column Name="P_values_author_avatarUrls_32x32" Label="AvatarUrls32x32" DataType="DT_WSTR" Length="1000" />
<Column Name="P_values_author_displayName" Label="AuthorDisplayName" DataType="DT_WSTR" Length="1000" />
<Column Name="P_values_author_active" Label="IsAuthorActive" DataType="DT_BOOL" />
<Column Name="P_values_author_timeZone" Label="AuthorTimeZone" DataType="DT_WSTR" Length="200" />
<Column Name="P_values_created" Label="CreatedDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="field" Label="Field" DataType="DT_WSTR" Length="255" />
<Column Name="fieldId" Label="FieldId" DataType="DT_WSTR" Length="255" />
<Column Name="fieldtype" Label="FieldType" DataType="DT_WSTR" Length="255" />
<Column Name="from" Label="From" DataType="DT_WSTR" Length="4000" />
<Column Name="fromString" Label="FromString" DataType="DT_WSTR" Length="4000" />
<Column Name="to" Label="To" DataType="DT_WSTR" Length="4000" />
<Column Name="toString" Label="ToString" DataType="DT_WSTR" Length="4000" />
</OutputColumns>
</EndPoint>
<!-- =====================
Get changelogs by IDs
====================== -->
<EndPoint Name="get_changelogs_by_ids"
Label="Read Changelogs by IDs"
Desc="Returns changelogs for an issue specified by a list of changelog IDs. OAuth scopes required: read:issue-meta:jira, read:avatar:jira, read:issue.changelog:jira"
Url="/issue/[$IssueIdOrKey$]/changelog/list"
Method="POST"
Body="{"changelogIds":[[$ChangeLogIds$]]}"
Filter="$.histories[*]"
ContentType="application/json"
>
<Params>
<!--<Param Name="PagingMode" Type="Property" Value="1" />
<Param Name="PagingByUrlAttributeName" Type="Property" Value="startAt" />
<Param Name="PagingIncrementBy" Type="Property" Value="50" />-->
<Param Name="IssueIdOrKey" Label="IssueId Or Key" Type="Placeholder" Required="True" />
<Param Name="ChangeLogIds" Label="Comma Separated ChangeLog Ids" Type="Placeholder" Required="True" />
</Params>
<OutputColumns>
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="200" />
<Column Name="author.self" Label="AuthorSelfURL" DataType="DT_WSTR" Length="1000" />
<Column Name="author.accountId" Label="AuthorAccountId" DataType="DT_WSTR" Length="200" />
<Column Name="author.emailAddress" Label="AuthorEmailAddress" DataType="DT_WSTR" Length="500" />
<Column Name="author.avatarUrls.48x48" Label="AvatarUrls48x48" DataType="DT_WSTR" Length="1000" />
<Column Name="author.avatarUrls.24x24" Label="AvatarUrls24x24" DataType="DT_WSTR" Length="1000" />
<Column Name="author.avatarUrls.16x16" Label="AvatarUrls16x16" DataType="DT_WSTR" Length="1000" />
<Column Name="author.avatarUrls.32x32" Label="AvatarUrls32x32" DataType="DT_WSTR" Length="1000" />
<Column Name="author.displayName" Label="AuthorDisplayName" DataType="DT_WSTR" Length="1000" />
<Column Name="author.active" Label="IsAuthorActive" DataType="DT_BOOL" />
<Column Name="author.timeZone" Label="AuthorTimeZone" DataType="DT_WSTR" Length="200" />
<Column Name="created" Label="CreatedDateTime" DataType="DT_DBTIMESTAMP" />
<Column Name="items" Label="Items" DataType="DT_WSTR" Length="4000" />
</OutputColumns>
</EndPoint>
<!-- =====================
Get custom field contexts
====================== -->
<EndPoint Name="get_custom_field_contexts"
Label="Get custom field contexts"
Desc="Returns a paginated list list of contexts for a custom field. OAuth scopes required: read:field:jira, read:custom-field-contextual-configuration:jira"
Url="/field/[$FieldId$]/context?startAt=0&maxResults=50"
Method="GET"
Filter="$.values[*]"
IncludeParentColumns="True" >
<Params>
<Param Name="PagingMode" Type="Property" Value="1" />
<Param Name="PagingByUrlAttributeName" Type="Property" Value="startAt" />
<Param Name="PagingIncrementBy" Type="Property" Value="50" />
<Param Name="FieldId" Label="FieldId" DataType="DT_WSTR" Length="100" Required="True"/>
</Params>
<OutputColumns>
<Column Name="P_isLast" Label="IsLast" DataType="DT_BOOL" />
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="100" />
<Column Name="name" Label="Name" DataType="DT_WSTR" Length="1000" />
<Column Name="description" Label="Description" DataType="DT_WSTR" Length="4000" />
<Column Name="isGlobalContext" Label="IsGlobalContext" DataType="DT_BOOL" />
<Column Name="isAnyIssueType" Label="IsAnyIssueType" DataType="DT_BOOL" />
</OutputColumns>
</EndPoint>
<!-- =====================
Get custom field options (context)
====================== -->
<EndPoint Name="get_custom_field_option_for_context"
Label="Get custom field context options"
Desc="Returns a paginated list of all custom field option for a context. Options are returned first then cascading options, in the order they display in Jira. OAuth scopes required: read:field.option:jira"
Url="/field/[$FieldId$]/context/[$ContextId$]/option?startAt=0&maxResults=50"
Method="GET"
Filter="$.values[*]"
IncludeParentColumns="True" >
<Params>
<Param Name="PagingMode" Type="Property" Value="1" />
<Param Name="PagingByUrlAttributeName" Type="Property" Value="startAt" />
<Param Name="PagingIncrementBy" Type="Property" Value="50" />
<Param Name="FieldId" Label="FieldId" DataType="DT_WSTR" Length="100" Required="True"/>
<Param Name="ContextId" Label="ContextId" DataType="DT_WSTR" Length="100" Required="True"/>
</Params>
<OutputColumns>
<Column Name="P_isLast" Label="IsLast" DataType="DT_BOOL" />
<Column Name="id" Label="Id" DataType="DT_WSTR" Length="100" />
<Column Name="value" Label="Value" DataType="DT_WSTR" Length="1000" />
<Column Name="disabled" Label="Isdisabled" DataType="DT_BOOL" />
<Column Name="optionId" Label="OptionId" DataType="DT_WSTR" Length="1000" />
</OutputColumns>
</EndPoint>
</EndPoints>
<Tables>
<Table Name="Fields" SelectEndPoint="get_fields" />
<Table Name="Projects" SelectEndPoint="get_projects" InsertEndPoint="create_project"
UpdateEndPoint="upsert_project" UpsertEndPoint="upsert_project" DeleteEndPoint="delete_project"/>
<Table Name="Users" SelectEndPoint="get_users" InsertEndPoint="create_user" DeleteEndPoint="delete_user" />
<Table Name="IssueTypes" SelectEndPoint="get_types" />
<Table Name="Issues" SelectEndPoint="get_issues" LookupEndPoint="get_issues" InsertEndPoint="create_issues" UpdateEndPoint="update_issue" DeleteEndPoint="delete_issue" />
<Table Name="Worklogs" SelectEndPoint="get_worklogs" InsertEndPoint="create_worklog"
UpdateEndPoint="update_worklog" DeleteEndPoint="delete_worklogs" LookupEndPoint="get_worklogs"/>
<Table Name="Comments" SelectEndPoint="get_comments" LookupEndPoint="get_comments" />
</Tables>
<!-- FIELDS -->
<Examples>
<Example Group="ODBC" Label="List issues" Code="SELECT * FROM Issues" Desc="Lists all issues" ></Example>
<Example Group="ODBC" Label="List a single issue by Id" Code="select * from Issues Where Id=10001" ></Example>
<Example Group="ODBC" Label="List a single issue by Key" Code="SELECT * FROM Issues WITH(Key='CS-1')" Desc="List a single issue by Key or Numeric Id." ></Example>
<Example Group="ODBC" Label="List multiple issues by Id or Key"
Code="SELECT * FROM Issues WITH(Key='CS-1, CS-2, 10003, 10004')" Desc="List multiple issues by comma separated Key(s) or Numeric Id(s)." ></Example>
<Example Group="ODBC" Label="List all issues for a specific project"
Code="SELECT * FROM Issues WITH(Project='CS')" Desc="List all issues for a specified project code." ></Example>
<Example Group="ODBC" Label="Search issues using Advanced JQL query expression" Desc="List issues using JQL query expression">
<Code>
<![CDATA[SELECT * FROM Issues WITH (Jql='status IN (Done, Closed) AND created > -5d' )
/*
Useful links:
https://support.atlassian.com/jira-work-management/docs/jql-fields/
https://www.atlassian.com/software/jira/guides/jql/tutorials#advanced-search
https://www.atlassian.com/blog/jira/jql-the-most-flexible-way-to-search-jira-14
Other Possible JQL expressions:
Ids (IN): key IN(10001, 10002, 10003);
Keys (IN): key IN(CS-1, CS-2, CS-3);
Projects (IN): project IN(PROJ1, PROJ2, PROJ3);
Status (EQUAL): status='Done';
Date (Expression 1): created >=-5d;
Date (Expression 2): created >=startOfMonth() AND created <=now();
Date (Expression 3): created >=startOfYear() AND created <=startOfDay();
Date (Static): created >= 2008/12/31;
Date (Static with time): created >= 2008/12/31 23:59;
Project and Status (AND + IN): project=CS AND status NOT IN ('Done', 'Open', 'Closed');
Assignee and Created Date: assignee is NOT EMPTY and created < -1d;
Text (Contains - Fuzzy): Summary ~ 'some words' OR description ~ 'some words';
Text (Contains - Fuzzy Wildcard): Summary ~ 'some*' OR description ~ 'some*';
Text (Contains - Exact): Summary ~ '\"exact words\"' OR description ~ '\"exact words\"';
Text (Does Not Contain - Fuzzy): Summary !~ 'some words' OR description !~ 'some words';
Empty OR Null: fixVersion is empty OR fixVersion is null;
Is Not Empty OR Is Not Null: fixVersion is not empty OR fixVersion is not null;
WAS Operator (previous value): status WAS "Resolved" BY (jsmith,srogen) BEFORE "2019/02/02";
WAS IN Operator: status WAS IN ("Resolved", "In Progress");
WAS NOT IN Operator: status WAS NOT IN ("Resolved", "In Progress");
WAS + BY + DURING (date range): status WAS "Resolved" BY (jsmith,srogen) DURING("2019/02/02", "2020/02/02");
CHANGED operator: assignee CHANGED;
CHANGED operator (multiple): status CHANGED FROM "In Progress" TO "Open"
*/
]]>
</Code>
</Example>
<Example Group="ODBC" Label="INSERT Issue" Desc="Inserts a single issue to a particular project">
<Code>
<![CDATA[INSERT INTO Issues(ProjectKey, IssueTypeName, Summary, Description)
VALUES('SMP', 'Task', 'My ticket inserted through the API', 'A description about an issue')
WITH (Output=1)]]>
</Code>
</Example>
<Example Group="ODBC" Label="UPDATE Issue" Desc="Updates an issue">
<Code>
<![CDATA[UPDATE Issues
SET Summary = 'This is my summary'
,Description = 'Lot''s of stuff to describe'
,Labels = '[ "bugfix" ]'
,DueDate = '2029-10-10'
--WHERE Id=1234
--WHERE Id='ISSKEY'
WITH (
IssueIdOrKey='ISSKEY', --or use Id in where clause
Output=1,
NotifyUsers=0,
OverrideScreenSecurity=0,
OverrideEditableFlag=0,
ContinueOn404Error=0
)']]>
</Code>
</Example>
<Example Group="ODBC" Label="UPDATE / Insert Issue with Custom Fields" Desc="Updates or Insert an issue with custom fields of various types (dropdown, radio, textarea .. so on)">
<Code>
<![CDATA[UPDATE Issues
SET customfield_10050='[{"value":"AAA"},{"value":"BBB"}]' --CUSTOM Checkboxes (Must use Raw JSON)
,customfield_10051='2020-12-31' --CUSTOM Date
,customfield_10052='2020-12-31T23:59:59' --CUSTOM DateTime
--Custom Dropdown / Radio fields
,customfield_10048_value='BBB' --CUSTOM Dropdown (Using value - i.e. item label)
--OR--
--,customfield_10048_id='10022' --CUSTOM Dropdown (Using id - i.e. item id)
--OR
--,customfield_10048='{"value":"BBB"}' --CUSTOM Dropdown (Using Raw value)
,customfield_10053='["bugfix","test"]' --CUSTOM Labels (Must use Raw JSON)
,customfield_10057='[{"value":"AAA"},{"value":"BBB"}]' --CUSTOM Listbox Multiselect (Must use Raw JSON)
,customfield_10049=123455555.123 --CUSTOM Number
,customfield_10054_text='Long string...line-1
Long string... line-2
Long string... line-3' --CUSTOM Paragraph
,customfield_10055_value='BBB' --CUSTOM Radio
--OR
--,customfield_10055_id='10023' --CUSTOM Radio
,customfield_10058='https://zappysys.com' --CUSTOM Url
,customfield_10059='{"accountId":"5dd64082af96bc0efbe55103","displayName":"System"}' --CUSTOM User --accountId=5dd64082af96bc0efbe55103
,customfield_10060='Custom text single line' --CUSTOM text single line
--WHERE Id=10000
WITH (
IssueIdOrKey='ISSKEY', --or use Id in where clause
Output=1,
NotifyUsers=0,
OverrideScreenSecurity=0,
OverrideEditableFlag=0,
ContinueOn404Error=0
)
--For INSERT use same values with same fields like below. For Insert you do not have to specify Id or Key
--INSERT INTO Issues(ProjectKey, IssueTypeName, Summary, Description, customfield_10050,....., customfield_10060)
--VALUES ('SMP', 'Task', 'My ticket inserted through the API', 'A description about an issue', '[{"value":"AAA"},{"value":"BBB"}]' ,... 'Custom text single line')
']]>
</Code>
</Example>
<Example Group="ODBC" Label="UPDATE multiple Issues matching with JQL search query" Desc="Updates multiple issues which match with certain condition (JQL). Adding Where 1=1 or some other WHERE condition invokes Lookup endpoint (refer to other example to learn about JQL - see Search issues using Advanced JQL query expression)">
<Code>
<![CDATA[UPDATE Issues
SET Summary = 'This is done on <<fun_now>>'
Where 1=1
WITH (jql='status=done' , ContinueOn404Error=0)
--WITH (jql='key in(10001, 10002, 10003)' , ContinueOn404Error=0)
--WITH (jql='key in(CS-1, CS-2, CS-3)', ContinueOn404Error=0)
]]>
</Code>
</Example>
<Example Group="ODBC" Label="DELETE Issue" Desc="Deletes a single issue">
<Code>
<![CDATA[DELETE FROM Issues
WITH (IssueIdOrKey='10020', OUTPUT=1, ContinueOn404Error=0)]]>
</Code>
</Example>
<Example Group="ODBC" Label="DELETE multiple Issues matching with JQL search query" Desc="Delete multiple issues which match with certain condition (JQL). Adding Where 1=1 or some other WHERE condition invokes Lookup endpoint (refer to other example to learn about JQL - see Search issues using Advanced JQL query expression)">
<Code>
<![CDATA[DELETE FROM Issues
Where 1=1
WITH (jql='status=done' , ContinueOn404Error=0)
--WITH (jql='key in(10001, 10002, 10003)' , ContinueOn404Error=0)
--WITH (jql='key in(CS-1, CS-2, CS-3)', ContinueOn404Error=0)
]]>
</Code>
</Example>
<Example Group="ODBC" Label="List comments for all issues" Code="SELECT * FROM Comments"
Desc="List comments for all issues" ></Example>
<Example Group="ODBC" Label="List comments for a specific issue Issue Key or Id"
Code="SELECT * FROM Comments Where IssueId=10003 --OR WITH(Key='10003')"
Desc="List comments for a specific issue" ></Example>
<Example Group="ODBC" Label="List comments for a specific issue by Key"
Code="SELECT * FROM Comments WITH(Key='CS-1')"
Desc="List comments for a specific issue by key" ></Example>
<Example Group="ODBC" Label="List comments for issues (search by JQL)"
Code="SELECT * FROM Comments WITH (Jql='status IN (Done, Closed) AND created > -5d' )"
Desc="List all comments for issues returned from a JQL search expression (refer to previous example to learn about JQL - see Search issues using Advanced JQL query expression) " ></Example>
<Example Group="ODBC" Label="List fields" Code="SELECT * FROM Fields" Desc="Lists all fields that are used and available in issue entity" ></Example>
<!-- PROJECT -->
<Example Group="ODBC" Label="List projects" Code="SELECT * FROM Projects" Desc="Lists all available projects" ></Example>
<Example Group="ODBC" Label="List worklog entries for a specific issue by Id or Key"
Code="SELECT * FROM Worklogs Where IssueId=10003 --WITH(Key='10003')"
Desc="List all worklog entries for a specific issue" ></Example>
<Example Group="ODBC" Label="List worklogs for issues (search by JQL)"
Code="SELECT * FROM Worklogs WITH (Jql='status IN (Done, Closed) AND created > -5d' )"
Desc="List all worklogs (time entries) for issues returned from a JQL search expression (refer to previous example to learn about JQL - see Search issues using Advanced JQL query expression) " ></Example>
<Example Group="ODBC" Label="INSERT Project" Desc="Inserts a single project">
<Code>
<![CDATA[INSERT INTO Projects(ProjectKey, Name, ProjectTypeKey, LeadAccountId, AssigneeType)
VALUES ('TEST', 'Test Project', 'software', '70122:XXXXXXXX-XXXX-XXXX-XXXX-c5da8c98b9e2', 'PROJECT_LEAD)
WITH (Output=1)')]]>
</Code>
</Example>
<Example Group="ODBC" Label="UPDATE Project" Desc="Updates a single project">
<Code>
<![CDATA[UPDATE Projects
SET Name = 'My Test Kanban Project'
,ProjectCategoryId = 1
WITH (ProjectIdOrKey = 'MYPRJCT', Output=1, ContinueOn404Error=0)]]>
</Code>
</Example>
<Example Group="ODBC" Label="DELETE Project" Desc="Deletes a single project">
<Code>
<![CDATA[DELETE FROM Projects
WITH (ProjectIdOrKey = '10020', Output=1, ContinueOn404Error=0)]]>
</Code>
</Example>
<!-- USERS -->
<Example Group="ODBC" Label="List users" Code="SELECT * FROM Users" Desc="Lists all available users" ></Example>
<Example Group="ODBC" Label="INSERT User" Desc="Inserts a single user">
<Code>
<![CDATA[INSERT INTO Users(EmailAddress, DisplayName, Name, Password)
VALUES ('my@user.com', 'John Doe', 'John', 'xhedkspstdadaothoua')
WITH (OUTPUT=1)]]>
</Code>
</Example>
<Example Group="ODBC" Label="DELETE User" Desc="Deletes a single user">
<Code>
<![CDATA[DELETE FROM Users
WITH (OUTPUT=1, accountId = '547059:136095a0-XXXX-XXXX-XXXX-3e4c66f26551', ContinueOn404Error=0)]]>
</Code>
</Example>
<!-- ISSUES -->
<!-- WORKLOGS -->
<Example Group="ODBC" Label="List worklogs" Code="SELECT * FROM Worklogs" Desc="Lists all worklogs from all issues" ></Example>
<Example Group="ODBC" Label="INSERT Worklog" Desc="Inserts a single worklog to a particular issue">
<Code>
<![CDATA[INSERT INTO Worklogs(TimeSpentInSeconds, Comment, StartedAt)
VALUES(7200,'My Comment!','2020-02-23T16:20:30.123+0000')
WITH (IssueIdOrKey='ISSKEY-1', OUTPUT=1)]]>
</Code>
</Example>
<Example Group="ODBC" Label="UPDATE Worklog" Desc="Updates a worklog">
<Code>
<![CDATA[UPDATE Worklogs
SET TimeSpentInSeconds = 28800
,Comment='My Comment!'
,StartedAt='2020-01-23T16:20:30.123+0000'
WITH (IssueIdOrKey='MTK-1', WorklogId='123465', OUTPUT=1, ContinueOn404Error=0)]]>
</Code>
</Example>
<Example Group="ODBC" Label="DELETE Worklog" Desc="Deletes a single worklog of an issue">
<Code>
<![CDATA[DELETE FROM Worklogs
WITH (IssueIdOrKey='10020', WorklogId='123465', OUTPUT=1, ContinueOn404Error=0)]]>
</Code>
</Example>
<Example Group="ODBC" Label="Update Custom Option Field (Dropdown/Radio)" Desc="This example shows how to update a custom field of an issue by Option Value or Id (Of option entry item)">
<Code>
<![CDATA[
--(By value)
UPDATE Issues
SET customfield_10048_value ='BBB' --supply value (label) of dropdown/radio
WITH (IssueIdOrKey='10020')
--OR-- (By item ID)
UPDATE Issues
SET customfield_10048_id =10023 --supply id of dropdown/radio item
WITH (IssueIdOrKey='10020')
--OR-- (Raw id)
UPDATE Issues
SET customfield_10048='{"id":"10023"}' --supply raw json
WITH (IssueIdOrKey='10020')
--OR-- (Raw value)
UPDATE Issues
SET customfield_10048='{"value":"BBB"}' --supply raw json
WITH (IssueIdOrKey='10020')
--OR-- (set null)
UPDATE Issues
SET customfield_10048 =null
WITH (IssueIdOrKey='10020')
]]>
</Code>
</Example>
<Example Group="ODBC" Label="Update Custom Multi Select / User / Team fields (RAW Json Update)" Desc="This example shows how to update a custom field of type array or other complex fiels like user" >
<Code>
<![CDATA[
UPDATE Issues
SET customfield_10048='[{"value":"AAA"}, {"value":"CCC"}]' --supply raw json
WITH (IssueIdOrKey='10020')
--OR--
UPDATE Issues
SET customfield_10048 =null --set to null
WITH (IssueIdOrKey='10020')
]]>
</Code>
</Example>
</Examples>
</ApiConfig>
