I have a product grid, in the product, there is a field called status to identify whether a product is active or inactive. I used manual lookup 0 => Inactive and 1 => Active, where in the database, status is stored as 0 and 1.
I got error message when trying to sort according to this field.
In addition, I also have category field which lookup to the value in category table. I also have problem to sort according to this field.
Anyone can help?
Cheers,
Daniel
Talked with one of the support person,
11:37 Your Question: Sorting on field with lookup value
11:37 Please wait and one of our operators will be with you shortly.
11:38 You are now chatting with xxx (Suporte Scriptcase)
11:38 Daniel Adinugroho: Hi xxx
11:38 Daniel Adinugroho: me again
11:39 Daniel Adinugroho: It was not work
11:39 xxx: Hello, Daniel
11:39 Daniel Adinugroho: below is the sql for lookup
11:39 Daniel Adinugroho: SELECT name
FROM category
WHERE id = {categoryFk}
ORDER BY name
11:39 Daniel Adinugroho: everytime I sort it always sort based on the categoryFk value
11:39 Daniel Adinugroho: when I tried to use JOIN, I got error message
11:40 Daniel Adinugroho: I mean join item and category
11:40 Daniel Adinugroho: SELECT i.id, i.code, i,name, c.name FROM item i INNER JOIN category c ON c.id = i.categoryFk
11:44 xxx: this SELECT is in which field?
11:48 Daniel Adinugroho: categoryFkc==>SELECT name
FROM category
WHERE id = {categoryFk}
ORDER BY name
11:48 Daniel Adinugroho: While this select is in SQL of the grid application ==> SELECT i.id, i.code, i,name, c.name FROM item i INNER JOIN category c ON c.id = i.categoryFk
11:49 xxx: ok, i’m making some tests
11:59 xxx: truth… the ordering is still made by id because it is the value that is registered in the field in the database
12:00 xxx: this ordering is done by the database
No solution yet, while this is I think the most simple and important thing to have.
Using this as basis SELECT i.id, i.code, i,name, c.name FROM item i INNER JOIN category c ON c.id = i.categoryFk, or for complete sql I used,
SELECT
i.id,
i.code,
i.name,
i.description,
baseUnit,
basePrice,
itemClass,
categoryFk,
i.status,
c.name
FROM
item i
JOIN
category c
ON c.id = i.categoryFk
I got this error message when sorting
Error while access the database
The other solution that I can think of is to create a new field, then use lookup based on the categoryFk but then this new created lookup does not appear in sorting.
Any ideas?
It should be no error when you sort using manual lookup. For category, please re-check the query, maybe something miss in your query.
www.LiviApps.com (Scriptcase Specialist - English language)
www.OwenSolution.com (Scriptcase Specialist - Indonesian language)
Somehow, my scriptcase is mixed up, I did create a clean grid application using the sql qbove and boom, suddenly the sorting is working, it just when I used grouping, the sorting is not work as expected not error but the sort on category will always give ASC when the grouping as active, even when I sorted the grid first using category.name DESC, when the grouping is activate, the sorting will back to category.name ASC.
I think the problem with sql that has join clause (in my previous post), is when we set the Group By -> Setting -> Keep Group by Order to “NO”. I don’t know if anyone can give more explanation on this…
Cheers,
Daniel