Some tables throw ‘The multi-part identifier could not be bound.’ when filtering by specific fields. Below is an example for Invoice filtering by BillToContactOverride. Attached is the SQL query executed by the API as retrieved from the request profiler. It looks like the generated SQL is incorrect. This is happening across multiple versions (tested v23 and v24). No customizations have been applied to the instance.
DECLARE @P1 AS INT = -2147483648, @P2 AS INT = -2147483648, @P4 AS BIT = 1;
SELECT TOP (1000) [Document_ARInvoice_ARRegister].[DocType] AS [DocType],
[Document_ARInvoice_ARRegister].[RefNbr] AS [RefNbr],
[Document_ARInvoice_ARRegister].[NoteID] AS [NoteID],
NULL AS [NoteText],
NULL AS [NoteFiles],
(SELECT TOP (1) [DocumentNoteExt].[NoteText]
FROM [Note] AS [DocumentNoteExt]
WHERE ([DocumentNoteExt].[CompanyID] IN (1, 2)
AND 8 = SUBSTRING([DocumentNoteExt].[CompanyMask], 1, 1) & 8)
AND ([DocumentNoteExt].[CompanyID] IN (1, 2)
AND 8 = SUBSTRING([DocumentNoteExt].[CompanyMask], 1, 1) & 8)
AND [DocumentNoteExt].[NoteID] = [Document_ARInvoice_ARRegister].[NoteID]) AS [Document_NoteText],
NULL,
NULL,
NULL,
[Billing_Contact_ARContact_ARContact].[ContactID] AS [ContactID],
[Billing_Contact_ARContact_ARContact].[IsDefaultContact] AS [IsDefaultContact],
[Billing_Contact_ARContact_ARContact].[NoteID] AS [NoteID]
FROM ([ARInvoice] AS [Document_ARInvoice_ARInvoice]
INNER JOIN
[ARRegister] AS [Document_ARInvoice_ARRegister]
ON ([Document_ARInvoice_ARRegister].[CompanyID] = 2)
AND [Document_ARInvoice_ARRegister].[DeletedDatabaseRecord] = 0
AND ([Document_ARInvoice_ARRegister].[BranchID] IS NULL
OR ([Document_ARInvoice_ARRegister].[BranchID] IN (22, 16, 20, 17, 21)))
AND [Document_ARInvoice_ARInvoice].[DocType] = [Document_ARInvoice_ARRegister].[DocType]
AND [Document_ARInvoice_ARInvoice].[RefNbr] = [Document_ARInvoice_ARRegister].[RefNbr])
LEFT OUTER JOIN
[Customer] AS [Document_Customer]
ON ([Document_Customer].[CompanyID] = 2)
AND [Document_Customer].[DeletedDatabaseRecord] = 0
AND [Document_Customer].[BAccountID] = [Document_ARInvoice_ARRegister].[CustomerID]
LEFT OUTER JOIN
[ARContact] AS [Billing_Contact_ARContact_ARContact]
ON ([Billing_Contact_ARContact_ARContact].[ContactID] = [Document_ARInvoice_ARInvoice].[BillContactID])
AND ([Billing_Contact_ARContact_ARContact].[CompanyID] = 2)
WHERE (([Document_ARInvoice_ARRegister].[DocType] IN ('INV', 'DRM', 'CRM', 'FCH', 'SMC')
AND ([Document_ARInvoice_ARRegister].[OrigModule] = 'AR'
OR ([Document_ARInvoice_ARRegister].[OrigModule] = 'EP'
OR [Document_ARInvoice_ARRegister].[Released] = CONVERT (BIT, 1)))
AND ([Document_Customer].[BAccountID] IS NULL
OR (0 = SUBSTRING([Document_Customer].[GroupMask], 1, 4) & @P1
OR 0 <> SUBSTRING([Document_Customer].[GroupMask], 1, 4) & @P2)))
AND ([Billing_Contact_ARContact].[OverrideContact] <> @P4
OR [Billing_Contact_ARContact].[OverrideContact] IS NULL))
AND ([Document_ARInvoice_ARInvoice].[CompanyID] = 2)
ORDER BY [Document_ARInvoice_ARRegister].[DocType], [Document_ARInvoice_ARRegister].[RefNbr]
OPTION (OPTIMIZE FOR UNKNOWN);
/* AR.30.10.00 */
/* Stack Trace:
at PX.Common.PXStackTrace.GetStackTrace(Int32 skipFrames, Boolean needFileInfo)
at PX.SM.PXPerformanceInfo.AddSqlSample(String cmdText, String parameterDeclaration, Boolean queryCache)
at PX.Data.PXSqlDatabaseProvider.<ExecuteReaderInternalAsync>d__24.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at PX.Data.PXSqlDatabaseProvider.ExecuteReaderInternalAsync(DbCommand command, CommandBehavior behavior, CancellationToken token)
at PX.Data.PXDatabaseProviderBase.ExecuteReaderAsync(DbCommand command, CommandBehavior behavior, CancellationToken token)
at PX.Data.PXDatabaseProviderBase.ExecuteReaderAsync(DbCommand command, CancellationToken token)
at PX.Data.PXSelectAsyncResult.PXSelectResultAsyncEnumerator.<MoveNextAsync>d__6.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at PX.Data.PXSelectAsyncResult.PXSelectResultAsyncEnumerator.MoveNextAsync()
at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.Enumerator.MoveNextAsync()
at PX.Common.Extensions.AsyncEnumerableExtensions.<OnCurrentSynchronizationContext>d__0`1.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at PX.Common.Extensions.AsyncEnumerableExtensions.<OnCurrentSynchronizationContext>d__0`1.System.Collections.Generic.IAsyncEnumerator<T>.MoveNextAsync()
at System.Linq.AsyncEnumerable.SelectEnumerableAsyncIterator`2.<MoveNextCore>d__7.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at System.Linq.AsyncEnumerable.SelectEnumerableAsyncIterator`2.MoveNextCore()
at System.Linq.AsyncIteratorBase`1.<MoveNextAsync>d__8.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at System.Linq.AsyncIteratorBase`1.MoveNextAsync()
at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.Enumerator.MoveNextAsync()
at System.Linq.AsyncEnumerableEx.<<Using>g__Core|121_0>d`2.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at System.Linq.AsyncEnumerableEx.<<Using>g__Core|121_0>d`2.System.Collections.Generic.IAsyncEnumerator<TSource>.MoveNextAsync()
at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.Enumerator.MoveNextAsync()
at PX.Common.Extensions.AsyncEnumerableExtensions.<OnCurrentSynchronizationContext>d__0`1.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at PX.Common.Extensions.AsyncEnumerableExtensions.<OnCurrentSynchronizationContext>d__0`1.System.Collections.Generic.IAsyncEnumerator<T>.MoveNextAsync()
at System.Linq.AsyncEnumerable.WhereSelectEnumerableAsyncIterator`2.<MoveNextCore>d__8.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at System.Linq.AsyncEnumerable.WhereSelectEnumerableAsyncIterator`2.MoveNextCore()
at System.Linq.AsyncIteratorBase`1.<MoveNextAsync>d__8.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at System.Linq.AsyncIteratorBase`1.MoveNextAsync()
at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.Enumerator.MoveNextAsync()
at PX.Common.Extensions.AsyncEnumerableExtensions.<OnCurrentSynchronizationContext>d__0`1.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at PX.Common.Extensions.AsyncEnumerableExtensions.<OnCurrentSynchronizationContext>d__0`1.System.Collections.Generic.IAsyncEnumerator<T>.MoveNextAsync()
at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.Enumerator.MoveNextAsync()
at System.Linq.AsyncEnumerable.<<ToListAsync>g__Core|620_0>d`1.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at System.Linq.AsyncEnumerable.<ToListAsync>g__Core|620_0[TSource](IAsyncEnumerable`1 source, CancellationToken cancellationToken)
at System.Linq.AsyncEnumerable.ToListAsync[TSource](IAsyncEnumerable`1 source, CancellationToken cancellationToken)
at PX.Api.ContractBased.EntityService.<GetListOptimizedAsyncFromEnumerable>d__12.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at PX.Api.ContractBased.EntityService.GetListOptimizedAsyncFromEnumerable(CancellationToken token, EntityImpl entity, EntityExportContextBuilder entityExportContextBuilder, IOptimizedExportProviderForContractBased provider, String version, String name, String objectName, ISystemContract systemContract)
at PX.Api.ContractBased.EntityService.<GetListAsyncFromEnumerable>d__8.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at PX.Api.ContractBased.EntityService.GetListAsyncFromEnumerable(CancellationToken token, ISystemContract systemContract, String version, String name, EntityImpl entity, CbOperationContext operationContext, Boolean ignoreValueFields, PXGraph graph)
at PX.Api.ContractBased.SystemContracts.V2.Facade.<PX-Api-ContractBased-IRestGate-GetListAsync>d__2.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at PX.Api.ContractBased.SystemContracts.V2.Facade.PX.Api.ContractBased.IRestGate.GetListAsync(EntityImpl entity, CancellationToken cancellationToken)
at PX.Api.ContractBased.SystemContracts.V2.RestController.GetList(String objectName, CancellationToken cancellationToken, String select, String filter, String expand, String custom, Nullable`1 skip, Nullable`1 top)
at lambda_method(Closure , Object , Object[] )
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass6_3.<GetExecutor>b__3(Object instance, Object[] methodParameters)
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)
at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__1.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at System.Web.Http.Controllers.ApiControllerActionInvoker.InvokeActionAsyncCore(HttpActionContext actionContext, CancellationToken cancellationToken)
at System.Web.Http.Controllers.ApiControllerActionInvoker.InvokeActionAsync(HttpActionContext actionContext, CancellationToken cancellationToken)
at System.Web.Http.Controllers.ActionFilterResult.<>c.<ExecuteAsync>b__5_0(ActionInvoker innerInvoker)
at System.Web.Http.Controllers.ActionFilterResult.<>c__DisplayClass7_0`1.<InvokeActionWithActionFilters>b__0()
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at System.Web.Http.Filters.ActionFilterAttribute.CallOnActionExecutedAsync(HttpActionContext actionContext, CancellationToken cancellationToken, Func`1 continuation)
at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at System.Web.Http.Filters.ActionFilterAttribute.ExecuteActionFilterAsyncCore(HttpActionContext actionContext, CancellationToken cancellationToken, Func`1 continuation)
at System.Web.Http.Filters.ActionFilterAttribute.System.Web.Http.Filters.IActionFilter.ExecuteActionFilterAsync(HttpActionContext actionContext, CancellationToken cancellationToken, Func`1 continuation)
at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__5.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at System.Web.Http.Controllers.ActionFilterResult.ExecuteAsync(CancellationToken cancellationToken)
at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__3.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at System.Web.Http.Filters.AuthorizationFilterAttribute.ExecuteAuthorizationFilterAsyncCore(HttpActionContext actionContext, CancellationToken cancellationToken, Func`1 continuation)
at System.Web.Http.Filters.AuthorizationFilterAttribute.System.Web.Http.Filters.IAuthorizationFilter.ExecuteAuthorizationFilterAsync(HttpActionContext actionContext, CancellationToken cancellationToken, Func`1 continuation)
at System.Web.Http.Controllers.AuthorizationFilterResult.ExecuteAsync(CancellationToken cancellationToken)
at System.Web.Http.Controllers.ExceptionFilterResult.<ExecuteAsync>d__6.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at System.Web.Http.Controllers.ExceptionFilterResult.ExecuteAsync(CancellationToken cancellationToken)
at System.Web.Http.ApiController.ExecuteAsync(HttpControllerContext controllerContext, CancellationToken cancellationToken)
at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__15.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at System.Web.Http.Dispatcher.HttpControllerDispatcher.SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
at System.Net.Http.HttpMessageInvoker.SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
at System.Web.Http.Dispatcher.HttpRoutingDispatcher.SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
at System.Net.Http.DelegatingHandler.SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
at Autofac.Integration.WebApi.Owin.DependencyScopeHandler.SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
at System.Net.Http.DelegatingHandler.SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
at System.Web.Http.HttpServer.<SendAsync>d__24.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at System.Web.Http.HttpServer.SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
at System.Net.Http.HttpMessageInvoker.SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
at System.Web.Http.Owin.HttpMessageHandlerAdapter.<InvokeCore>d__20.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at System.Web.Http.Owin.HttpMessageHandlerAdapter.InvokeCore(IOwinContext context, IOwinRequest owinRequest, IOwinResponse owinResponse)
at PX.Logging.Enrichers.OwinExtensions.<>c.<<DisableAspNetCallbackEnricher>b__0_0>d.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at PX.Logging.Enrichers.OwinExtensions.<>c.<DisableAspNetCallbackEnricher>b__0_0(IOwinContext ctx, Func`1 next)
at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>d__3.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.AspNetCore.Routing.EndpointMiddleware.Invoke(HttpContext httpContext)
at Microsoft.AspNetCore.Builder.Extensions.MapMiddleware.<Invoke>d__3.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.AspNetCore.Builder.Extensions.MapMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Builder.Extensions.MapWhenMiddleware.<Invoke>d__3.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.AspNetCore.Builder.Extensions.MapWhenMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Builder.StackOverflowGuardApplicationBuilderExtensions.<>c.<<UseAsyncStackOverflowGuard>b__0_0>d.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.AspNetCore.Builder.StackOverflowGuardApplicationBuilderExtensions.<>c.<UseAsyncStackOverflowGuard>b__0_0(HttpContext httpContext, Func`1 next)
at PX.AspNetCore.Owin.ApplicationBuilderExtensions.<>c.<<UseScreenIdFromRequest>b__2_0>d.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at PX.AspNetCore.Owin.ApplicationBuilderExtensions.<>c.<UseScreenIdFromRequest>b__2_0(HttpContext context, Func`1 next)
at PX.AspNetCore.Owin.ApplicationBuilderExtensions.<>c.<<UseRouteConstraintValidation>b__1_0>d.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at PX.AspNetCore.Owin.ApplicationBuilderExtensions.<>c.<UseRouteConstraintValidation>b__1_0(HttpContext context, Func`1 next)
at Microsoft.AspNetCore.Builder.ApplicationBuilderExtensions.<>c.<<UseSessionContextFactory>b__6_0>d.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.AspNetCore.Builder.ApplicationBuilderExtensions.<>c.<UseSessionContextFactory>b__6_0(ISessionContextFactory sessionContextFactory, HttpContext httpContext, Func`1 next)
at PX.Telemetry.TelemetryOwinRegistration.<PreRequestHandlerExecute>d__6.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at PX.Telemetry.TelemetryOwinRegistration.PreRequestHandlerExecute(HttpContext context, Func`1 next)
at Microsoft.Owin.Host.SystemWeb.IntegratedPipeline.IntegratedPipelineContextStage.<RunApp>d__7.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.Owin.Host.SystemWeb.IntegratedPipeline.IntegratedPipelineContextStage.RunApp(Func`2 entryPoint, IDictionary`2 environment, TaskCompletionSource`1 tcs, StageAsyncResult result)
at Microsoft.Owin.Host.SystemWeb.IntegratedPipeline.IntegratedPipelineContextStage.BeginEvent(Object sender, EventArgs e, AsyncCallback cb, Object extradata)
*/