Bug: Forms imposes its own sorting rules on lookup selections

asked on March 8, 2017 • Show version history

We have the following SQL table:

When used in a Forms 10.2 table, the items are sorted:

I'm not sure why Forms decides to sort items like this (or at all, really), as opposed to displaying values in the order they are listed in the database. Regardless, this is an issue for us because it results in the most expensive item size being listed first.

I know I can use JavaScript to re-sort the items after they have been looked up, but this form is already full of JavaScript that we wrote to work around various bugs and issues and I'm wary of adding even more. Some users still have older mobile devices and running too much JS causes performance problems.

I feel like there should be an option in the Lookup Rules page that lets you pick whether you want items sorted when populating a table field from a lookup. If that is too much work, we would be OK with no sorting.

0 0

SELECTED ANSWER

replied on March 8, 2017 • Show version history

Databases don't have an inherent "order": see this stack overflow post. Having a primary key will make a sort order more likely, but not guarantee it.

Specifying a sort order when performing a lookup is already supported: follow the example outlined in the help files for lookup rules that uses a stored procedure to include an "Order By" in the call to the database.

3 0

replied on March 8, 2017 • Show version history

To build off of this, note that if the values in the column are text, then the order shown is actually in ascending order because the character '1' precedes the character '3'. It would probably be a better idea to convert this column into a unitless column of ints (or some other numeric data type) where all values are converted to some standard unit (e.g. grams); then ordering by the column will sort the numerical values instead of the character strings.

Edit: Also, as of Forms 10.1 (I can't remember off the top of my head when it was added) in lookup rule configuration, you actually can specify per rule a column from the data source for sorting, and whether to sort ascending or descending.

2 0

replied on March 8, 2017 • Show version history

Thanks guys. We ended up using the following stored procedure:

CREATE PROCEDURE [dbo].[SortEnhancementItemsByUnitPrice]
   @Description nvarchar(500)
AS
BEGIN
SET NOCOUNT ON;
SELECT [Description], [Size/Unit], [Unit Cost $] FROM dbo.EnhancementItems
WHERE Description = @Description
ORDER BY [Unit Cost $] ASC
END

We had to change the Unit Cost column to decimal, but the overall solution works.

0 0

Question

Question

Bug: Forms imposes its own sorting rules on lookup selections

Answer

Replies

Sign in to reply to this post.