I was trying to expand filter on a field by clicking on ‘+’ button in order to get all possible values for a filter.
I was expecting to see the list of all possible values for the filter.
Instead I’ve got the following error: Invalid column name ‘<name of column field>’
Additional information:
- The field is a foreign key and is actually a lookup field - it’s added in the screen’s datastructure as a datastructure field with UseLookupValue=true
- bug was spotted at 2022.4, html client
- It works well in the desktop client at version 2023.3
- the error comes from SQL - tsql can’t group by labels from SELECT clause.
UI API
System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'Product_refProductCategoryId'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at Origam.DA.Service.AbstractSqlDataService.ExecuteDataReader(DataStructureQuery query, IPrincipal principal, String transactionId) in D:\a\1\s\backend\Origam.DA.Service\AbstractSqlDataService.cs:line 1682
at Origam.DA.Service.AbstractSqlDataService.ExecuteDataReaderInternal(DataStructureQuery query)+MoveNext() in D:\a\1\s\backend\Origam.DA.Service\AbstractSqlDataService.cs:line 1821
at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
at Origam.Server.Controller.UIServiceController.StreamlineFilterListValues(IEnumerable`1 fullReaderResult) in D:\a\1\s\backend\Origam.Server\Controller\UIServiceController.cs:line 756
at Origam.Server.Controller.UIServiceController.<>c__DisplayClass61_0.<GetFilterListValues>b__0() in D:\a\1\s\backend\Origam.Server\Controller\UIServiceController.cs:line 691
at Origam.Server.Controller.AbstractController.RunWithErrorHandler(Func`1 func) in D:\a\1\s\backend\Origam.Server\Controller\AbstractController.cs:line 120
ClientConnectionId:875140f8-3651-43e5-807e-d121f7f3c69b
rendered sql
SELECT TOP 999999
(
SELECT
TOP 1 [Product].[refProductCategoryId] AS [refProductCategoryId]
FROM [Product] AS [Product]
WHERE (
([Inventory].[refProductId] = [Product].[Id])
AND ([Product].[Id] <> '32ba1fe9-bcf9-42b1-a370-f1dcf5d8421b'))
) AS [Product_refProductCategoryId],
COUNT_BIG(*) AS groupCount
FROM [Inventory] AS [Inventory]
WHERE
(
((SELECT (SELECT TOP 1 [Product].[IsActive] AS [IsActive]
FROM [Product] AS [Product] WHERE
( ([lookupInventory].[refProductId] = [Product].[Id])
AND ([Product].[Id] <> '32ba1fe9-bcf9-42b1-a370-f1dcf5d8421b')) )
AS [Product_IsActive] FROM [Inventory] AS [lookupInventory]
WHERE ( ([Inventory].[Id] = [lookupInventory].[Id]))
) = 1)
AND ([Inventory].[refInventoryTypeId] = 'c7b57e2e-058f-45e8-80ca-fc6681871dfb')
)
GROUP BY [Inventory].[refProductId], [Product_refProductCategoryId]