[Bug]: TF51005: The query references a field that does not exist. (Wrong Macro name)

[RSwarnkar]

Version

• I confirm that I am using the latest version

Source Version

Azure DevOps Service

Target Version

Azure DevOps Service

Relevant configuration

{
  "ChangeSetMappingFile": null,
  "Source": {
    "$type": "TfsTeamProjectConfig",
    "Collection": "https://dev.azure.com/rs-atos/",
    "Project": "SourceProject",
    "ReflectedWorkItemIDFieldName": "Agile.ReflectedWorkItemId",
    "AllowCrossProjectLinking": false,
    "AuthenticationMode": "Prompt",
    "PersonalAccessToken": "",
    "PersonalAccessTokenVariableName": "",
    "LanguageMaps": {
      "AreaPath": "Area",
      "IterationPath": "Iteration"
    }
  },
  "Target": {
    "$type": "TfsTeamProjectConfig",
    "Collection": "https://dev.azure.com/rs-atos/",
    "Project": "DestinationProject",
    "ReflectedWorkItemIDFieldName": "Agile.ReflectedWorkItemId",
    "AllowCrossProjectLinking": false,
    "AuthenticationMode": "Prompt",
    "PersonalAccessToken": "",
    "PersonalAccessTokenVariableName": "",
    "LanguageMaps": {
      "AreaPath": "Area",
      "IterationPath": "Iteration"
    }
  },
  "FieldMaps": [
    {
      "$type": "MultiValueConditionalMapConfig",
      "WorkItemTypeName": "*",
      "sourceFieldsAndValues": {
        "Field1": "Value1",
        "Field2": "Value2"
      },
      "targetFieldsAndValues": {
        "Field1": "Value1",
        "Field2": "Value2"
      }
    },
    {
      "$type": "FieldSkipMapConfig",
      "WorkItemTypeName": "*",
      "targetField": "TfsMigrationTool.ReflectedWorkItemId"
    },
    {
      "$type": "FieldValueMapConfig",
      "WorkItemTypeName": "*",
      "sourceField": "System.State",
      "targetField": "System.State",
      "defaultValue": "New",
      "valueMapping": {
        "Approved": "New",
        "New": "New",
        "Committed": "Active",
        "In Progress": "Active",
        "To Do": "New",
        "Done": "Closed",
        "Removed": "Removed"
      }
    },
    {
      "$type": "FieldtoFieldMapConfig",
      "WorkItemTypeName": "*",
      "sourceField": "Microsoft.VSTS.Common.BacklogPriority",
      "targetField": "Microsoft.VSTS.Common.StackRank",
      "defaultValue": null
    },
    {
      "$type": "FieldtoFieldMultiMapConfig",
      "WorkItemTypeName": "*",
      "SourceToTargetMappings": {
        "SourceField1": "TargetField1",
        "SourceField2": "TargetField2"
      }
    },
    {
      "$type": "FieldtoTagMapConfig",
      "WorkItemTypeName": "*",
      "sourceField": "System.State",
      "formatExpression": "ScrumState:{0}"
    },
    {
      "$type": "FieldMergeMapConfig",
      "WorkItemTypeName": "*",
      "sourceFields": [
        "System.Description",
        "Microsoft.VSTS.Common.AcceptanceCriteria"
      ],
      "targetField": "System.Description",
      "formatExpression": "{0} <br/><br/><h3>Acceptance Criteria</h3>{1}"
    },
    {
      "$type": "RegexFieldMapConfig",
      "WorkItemTypeName": "*",
      "sourceField": "COMPANY.PRODUCT.Release",
      "targetField": "COMPANY.DEVISION.MinorReleaseVersion",
      "pattern": "PRODUCT \\d{4}.(\\d{1})",
      "replacement": "$1"
    },
    {
      "$type": "FieldValuetoTagMapConfig",
      "WorkItemTypeName": "*",
      "sourceField": "Microsoft.VSTS.CMMI.Blocked",
      "pattern": "Yes",
      "formatExpression": "{0}"
    },
    {
      "$type": "TreeToTagMapConfig",
      "WorkItemTypeName": "*",
      "toSkip": 3,
      "timeTravel": 1
    }
  ],
  "GitRepoMapping": null,
  "LogLevel": "Information",
  "CommonEnrichersConfig": null,
  "Processors": [
    {
      "$type": "WorkItemMigrationConfig",
      "Enabled": true,
      "ReplayRevisions": true,
      "PrefixProjectToNodes": false,
      "UpdateCreatedDate": true,
      "UpdateCreatedBy": true,
      "WIQLQueryBit": "AND  [Microsoft.VSTS.Common.ClosedDate] = '' AND [System.WorkItemType] NOT IN ('Test Suite', 'Test Plan','Shared Steps','Shared Parameter','Feedback Request')",
      "WIQLOrderBit": "[System.ChangedDate] desc",
      "LinkMigration": true,
      "AttachmentMigration": true,
      "AttachmentWorkingPath": "c:\\temp\\WorkItemAttachmentWorkingFolder\\",
      "FixHtmlAttachmentLinks": false,
      "SkipToFinalRevisedWorkItemType": true,
      "WorkItemCreateRetryLimit": 5,
      "FilterWorkItemsThatAlreadyExistInTarget": true,
      "PauseAfterEachWorkItem": false,
      "AttachmentMaxSize": 480000000,
      "AttachRevisionHistory": false,
      "LinkMigrationSaveEachAsAdded": false,
      "GenerateMigrationComment": true,
      "WorkItemIDs": null,
      "MaxRevisions": 0,
      "UseCommonNodeStructureEnricherConfig": false,
      "StopMigrationOnMissingAreaIterationNodes": true,
      "NodeBasePaths": [],
      "AreaMaps": {},
      "IterationMaps": {},
      "MaxGracefulFailures": 0,
      "SkipRevisionWithInvalidIterationPath": false,
      "SkipRevisionWithInvalidAreaPath": false
    }
  ],
  "Version": "13.1",
  "workaroundForQuerySOAPBugEnabled": false,
  "WorkItemTypeDefinition": {
    "sourceWorkItemTypeName": "targetWorkItemTypeName"
  },
  "Endpoints": {
    "InMemoryWorkItemEndpoints": [
      {
        "Name": "Source",
        "EndpointEnrichers": null
      },
      {
        "Name": "Target",
        "EndpointEnrichers": null
      }
    ]
  }
}

Relevant log output

[19:27:35 INF] Start Time: 09/27/2023 19:27:35
[19:27:35 INF] Running with args: execute
[19:27:35 INF] OSVersion: Microsoft Windows NT 6.2.9200.0
[19:27:35 INF] Version: 13.1.1.0
[19:27:37 INF] Latest version detected as 13.1.1.0
[19:27:37 INF] Application started. Press Ctrl+C to shut down.
[19:27:37 INF] Hosting environment: Production
[19:27:37 INF] Content root path: C:\tools\MigrationTools
[19:27:37 INF] Press Ctrl+C to shut down.
[19:27:37 INF] Config Found, creating engine host
[19:27:37 INF] Creating Migration Engine 1ee69102-0c50-47bf-93da-ea2aeb8ee5cc
[19:27:37 INF] ProcessorContainer: Of 1 configured Processors only 1 are enabled
[19:27:40 INF] ProcessorContainer: Adding Processor WorkItemMigrationContext
[19:27:40 INF] Logging has been configured and is set to: Information.
[19:27:40 INF]                               Max Logfile: Verbose.
[19:27:40 INF]                               Max Console: Debug.
[19:27:40 INF]                  Max Application Insights: Error.
[19:27:40 INF] The Max log levels above show where to go look for extra info. e.g. Even if you set the log level to Verbose you will only see that info in the Log File, however everything up to Debug will be in the Console.
[19:27:40 INF] FieldMapContainer: Adding FieldMap MultiValueConditionalMap for *
[19:27:40 INF] FieldMapContainer: Adding FieldMap FieldSkipMap for *
[19:27:40 INF] FieldMapContainer: Adding FieldMap FieldValueMap for *
[19:27:40 INF] FieldMapContainer: Adding FieldMap FieldToFieldMap for *
[19:27:40 INF] FieldMapContainer: Adding FieldMap FieldtoFieldMultiMap for *
[19:27:40 INF] FieldMapContainer: Adding FieldMap FieldToTagFieldMap for *
[19:27:40 INF] FieldMapContainer: Adding FieldMap FieldMergeMap for *
[19:27:40 INF] FieldMapContainer: Adding FieldMap RegexFieldMap for *
[19:27:40 INF] FieldMapContainer: Adding FieldMap FieldValuetoTagMap for *
[19:27:40 INF] FieldMapContainer: Adding FieldMap TreeToTagFieldMap for *
[19:27:40 INF] Beginning run of 1 processors
[19:27:40 INF] Processor: WorkItemMigration
[19:27:40 INF] Migration Context Start: WorkItemMigration
[19:27:40 INF] WorkItemMigrationContext::InternalExecute
[19:27:40 INF] TfsMigrationClient::GetDependantTfsCollection:AuthenticationMode(Prompt)
[19:27:40 INF] TfsMigrationClient::GetDependantTfsCollection: Prompting for credentials
[19:27:40 INF] MigrationClient: Connecting to https://dev.azure.com/rs-atos/

[19:27:44 INF] MigrationClient: Access granted to https://dev.azure.com/rs-atos/ for YYYYYYYYY (XXXXXX)
[19:27:44 INF] TfsMigrationClient::GetDependantTfsCollection:AuthenticationMode(Prompt)
[19:27:44 INF] TfsMigrationClient::GetDependantTfsCollection: Prompting for credentials
[19:27:44 INF] MigrationClient: Connecting to https://dev.azure.com/rs-atos/
[19:27:45 INF] MigrationClient: validating security for {"IsContainer": false, "UniqueName": "XXXXXX", "Descriptor": {"Data": null, "Identifier": "0003BFFD3104CFCC@Live.com", "IdentityType": "Microsoft.IdentityModel.Claims.ClaimsIdentity", "$type": "IdentityDescriptor"}, "DisplayName": "YYYYYYYYY", "IsActive": true, "MemberOf": [], "Members": [], "TeamFoundationId": "cce3b0bf-48b7-6ad1-a528-ba76440ad075", "UniqueUserId": 0, "$type": "TeamFoundationIdentity"}
[19:27:45 INF] MigrationClient: Access granted to https://dev.azure.com/rs-atos/ for YYYYYYYYY (XXXXXX)
[19:27:50 INF] Migrating all Nodes before the Processor run.
[19:27:51 INF]  Processing Node: \DestinationProject\Area\Website Team, start date: null, finish date: null
[19:27:53 INF]  Processing Node: \DestinationProject\Iteration\Iteration 1, start date: 09/05/2023 00:00:00, finish date: 09/26/2023 00:00:00
[19:27:53 INF]  Processing Node: \DestinationProject\Iteration\Iteration 2, start date: 09/27/2023 00:00:00, finish date: 10/18/2023 00:00:00
[19:27:54 INF]  Processing Node: \DestinationProject\Iteration\Iteration 3, start date: 10/19/2023 00:00:00, finish date: 11/09/2023 00:00:00
[19:27:55 INF] Querying items to be migrated: SELECT [System.Id], [System.Tags] FROM WorkItems WHERE [System.TeamProject] = @TeamProject AND  [Microsoft.VSTS.Common.ClosedDate] = '' AND [System.WorkItemType] NOT IN ('Test Suite', 'Test Plan','Shared Steps','Shared Parameter','Feedback Request') ORDER BY [System.ChangedDate] desc ...
[19:27:56 INF] 63 Work items received, verifying
[19:27:57 INF] Loading 63 Work Items
[19:28:27 INF] 38/63 60.32%
[19:28:38 INF] Replay all revisions of 63 work items?
[19:28:38 INF] ValidateTargetNodesExist::Checking all Nodes on Work items
[19:28:39 INF] Found target project as DestinationProject
[19:28:39 INF] [FilterWorkItemsThatAlreadyExistInTarget] is enabled. Searching for work items that have already been migrated to the target...
[19:28:39 INF] [FilterWorkItemsThatAlreadyExistInTarget] is enabled. Source project SourceProject is replaced with target project DestinationProject on the WIQLQueryBit which resulted into this target WIQLQueryBit "AND  [Microsoft.VSTS.Common.ClosedDate] = '' AND [System.WorkItemType] NOT IN ('Test Suite', 'Test Plan','Shared Steps','Shared Parameter','Feedback Request')" .
[19:28:39 ERR]  Error running query
Microsoft.TeamFoundation.WorkItemTracking.Client.ValidationException: TF51005: The query references a field that does not exist. The error is caused by «[Agile.ReflectedWorkItemId]».
   at Microsoft.TeamFoundation.WorkItemTracking.Client.Query.Initialize(WorkItemStore store, String wiql, IDictionary context, Int32[] ids, Int32[] revs, Boolean dayPrecision)
   at Microsoft.TeamFoundation.WorkItemTracking.Client.WorkItemStore.Query(String wiql, IDictionary context)
   at MigrationTools._EngineV1.Clients.TfsWorkItemQuery.GetWorkItemsFromQuery(TfsWorkItemMigrationClient wiClient) in D:\a\1\s\src\MigrationTools.Clients.AzureDevops.ObjectModel\_EngineV1\Clients\TfsWorkItemQuery.cs:line 51
[19:28:39 INF] DONE in 00:00:43.7217002
[19:28:39 FTL] Error while running WorkItemMigration
Microsoft.TeamFoundation.WorkItemTracking.Client.ValidationException: TF51005: The query references a field that does not exist. The error is caused by «[Agile.ReflectedWorkItemId]».
   at Microsoft.TeamFoundation.WorkItemTracking.Client.Query.Initialize(WorkItemStore store, String wiql, IDictionary context, Int32[] ids, Int32[] revs, Boolean dayPrecision)
   at Microsoft.TeamFoundation.WorkItemTracking.Client.WorkItemStore.Query(String wiql, IDictionary context)
   at MigrationTools._EngineV1.Clients.TfsWorkItemQuery.GetWorkItemsFromQuery(TfsWorkItemMigrationClient wiClient) in D:\a\1\s\src\MigrationTools.Clients.AzureDevops.ObjectModel\_EngineV1\Clients\TfsWorkItemQuery.cs:line 84
   at MigrationTools._EngineV1.Clients.TfsWorkItemQuery.GetInternalWorkItems() in D:\a\1\s\src\MigrationTools.Clients.AzureDevops.ObjectModel\_EngineV1\Clients\TfsWorkItemQuery.cs:line 41
   at MigrationTools._EngineV1.Clients.TfsWorkItemQuery.GetWorkItems() in D:\a\1\s\src\MigrationTools.Clients.AzureDevops.ObjectModel\_EngineV1\Clients\TfsWorkItemQuery.cs:line 26
   at MigrationTools._EngineV1.Clients.TfsWorkItemMigrationClient.FilterExistingWorkItems(List`1 sourceWorkItems, TfsWiqlDefinition wiqlDefinition, TfsWorkItemMigrationClient sourceWorkItemMigrationClient) in D:\a\1\s\src\MigrationTools.Clients.AzureDevops.ObjectModel\_EngineV1\Clients\TfsWorkItemMigrationClient.cs:line 48
   at VstsSyncMigrator.Engine.WorkItemMigrationContext.InternalExecute() in D:\a\1\s\src\VstsSyncMigrator.Core\Execution\MigrationContext\WorkItemMigrationContext.cs:line 190
   at MigrationTools._EngineV1.Processors.MigrationProcessorBase.Execute() in D:\a\1\s\src\MigrationTools\_EngineV1\Processors\MigrationProcessorBase.cs:line 47
[19:28:39 ERR] WorkItemMigration The Processor MigrationEngine entered the failed state...stopping run
[19:28:39 INF] Application is shutting down...
[19:28:39 INF] Application Ending
[19:28:39 INF] The application ran in 00:01:03.8130529 and finished at 09/27/2023 19:28:39

What happened?

I believe with WIQL, the macro @TeamProject is invalid. The Query works well with macro @project.

Working:

Can you please look into this?

Debug in Visual Studio

• Visual Studio Debug

[RSwarnkar]

Hello,

I followed below articles to fix the issue related to this:

1. https://stackoverflow.com/a/61770964/1176573
2. https://learn.microsoft.com/en-us/azure/devops/organizations/settings/work/manage-process?view=azure-devops
3. https://learn.microsoft.com/en-us/azure/devops/organizations/settings/work/customize-process-field?view=azure-devops
4. https://stackoverflow.com/questions/71229978/tf51005-the-query-references-a-field-that-does-not-exist-the-error-is-caused-b

Summary:

1. Create an inherited custom process
2. In each WorkItem type of Interited process you need to add ReflectedWorkItemId field.

You should kow basics how to do it and @MrHinsh has described that at link-1 above.
Hence please close this issue.

[MrHinsh]

Since its the same query that is run against both the Source and the Target and both are Azure DevOps Service, this does not make sense.

More likely is that you have not added the "ReflectedWorkItemId" field as described in:

• Getting Styarted
• Server Configuration