Can't list values for a filter based on an enumeration column (refXXXId) on a field with UseLookupValue set

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

rendered sql

		TOP 1 [Product].[refProductCategoryId] AS [refProductCategoryId]
	FROM [Product] AS [Product]
	 ([Inventory].[refProductId] = [Product].[Id])
	  AND  ([Product].[Id] <> '32ba1fe9-bcf9-42b1-a370-f1dcf5d8421b'))
) AS [Product_refProductCategoryId],
COUNT_BIG(*) AS groupCount 
FROM [Inventory] AS [Inventory]
	((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]

This seems to be anyway a workaround (asking for top 999999 and grouping) to get a DISTINCT with the tools we have.

I suggest to implement asking for DISTINCT so this would not happen at all in this case (getting a list for a filter dropdown).

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.