TFS4JIRA Self-Hosted | How to add the SPRINT field and value mappings directly in the database

Owned by Gabriela Costa
Last updated: Jan 24, 2024
4 min read

SPRINT field mapping is stored in FieldMappings

Value mappings for the mapped SPRINT field are stored in the table: ValueMappings

PYTHON script auto-generating below explained queries.

Add field mapping for Sprint field.

  1. Run request: https://<JIRA URL>/rest/api/3/field/search?query=Sprint

  2. Example JSON result

     

  3. Prepare and run INSERT query

    INSERT INTO [dbo].[FieldMappings] ([JiraField] ,[TfsField] ,[JiraDefaultValue] ,[TfsDefaultValue] ,[JiraType] ,[TfsType] ,[MappingMode] ,[MappingDirection] ,[IgnoreIllegalStateTransitions] ,[SyncProfile_Guid] ,[JiraCustomFieldName] ,[TfsListSeparator] ,[IsDefault]) VALUES (<JiraField> ,<TfsField> ,<JiraDefaultValue> ,<TfsDefaultValue> ,'array' ,<TfsType> ,2 ,<MappingDirection> ,0 ,<SyncProfile_Guid> ,<JiraCustomFieldName> ,',' ,<IsDefault>)

     

  4. VALUES to provide

    1. <JiraField> - "id" from REQUEST response, in this example: customfield_10020

    2. <TfsField> - System.AreaPath or System.IterationPath or any other field from ADO/TFS

    3. <JiraDefaultValue>, <TfsDefaultValue>

      1. Raise Error - value should be: NULL

      2. Empty Value - value should be: -empty-

      3. Default Value - value should show either SPRINT ID or ADO FIELD value

    4. <TfsType> - TreePath for fields System.AreaPath or System.IterationPath

    5. <MappingDirection>

      1. 1- TFS to Jira

      2. 2- Jira to TFS

      3. 3 - Both directions

    6. <SyncProfile_Guid> - guid of your sync profile visible by running this query

      SELECT guid,name FROM SyncProfiles

    7. <JiraCustomFieldName> - "custom" value from request JSON, in this example: com.pyxis.greenhopper.jira:gh-sprint

    8. <IsDefault> - (0 or 1), 1 for PRIMARY mapping

Update field mapping for Sprint field.

  1. in FieldMappings tablle, a row should be present with mapping for SPRINT and ADO field, please find the ID of that mapping (in the example case its ID: 250)

  2. update that mapping to change Mapping Mode to MAP VALUES by running:

    UPDATE FieldMappings SET MappingMode = 2 WHERE Id=<your mapping ID>

  3. update ANYTHING ELSE functionality for Jira and ADO/TFS. Columns storing the values: JiraDefaultValue, TfsDefaultValue

    1. Raise Error - value should be: NULL

    2. Empty Value - value should be: -empty-

    3. Default Value - value should show either SPRINT ID or ADO FIELD value

Add mapping for SPRINT values.

  1. Adding value mappings for SPRINT fields mapping

    1. Run a request to fetch all the BOARDS in the Jira PROJECT

      1. https://<JIRA URL>/rest/agile/1.0/board?projectKeyOrId=<project key>

      2. example result

      Run a request listing all the SPRINTS in each BOARD

      1. https://<JIRA URL>/rest/agile/1.0/board/<BOARD ID>/sprint

      2. example result

         

  2. Insert mapping in to the ValueMappings table

    2. example query: