Y
Yarik
Hello,
By "indirect interactive sorting" I mean a situation when a form's
field shows values of one DB column, but is sorted using values of
another column. I believe this is a typical requirement when dealing
with ordinal categorical concepts (such as named ranks, months, days
of week, etc.).
For example, here is the specific example of this problem in our
current MS Access 2003 project (ADP backed up by SQL Server 2005):
-- The system tracks products.
-- There are a few importance levels defined in the system
(ordinals named "low", "medium", "high", etc.)
-- Each product may have an importance assigned to it.
This fragment of the domain model is supported by the following two
tables in the database:
Table "Importances"
----------------------
ID Name Rank
--- ---------- -----
1 low -10
2 high 10
3 medium 0
4 highest 100
... ... ...
----------------------
NB: The "Rank" column is what defines the order of importances.
Table "Products"
-------------------------------
ID Importance_ID Title
--- ------------- ----------
1 1 Product A
2 2 Product B
3 4 Product X
4 2 Product Z
------------------------------
Now, let's say I have a form that displays products with their
importances. The question is:
How to enable interactive sorting and filtering of products
by their importances (i.e. numerically by ranks of their
importances) without exposing those numeric ranks to the user?
Proper sorting is relatively easy to achieve. The record source of
such form is more or less obvious (and may even include a default
sorting order):
select
Products.ID as ID -- invisible
Products.Title as Title -- visible
Products.Importance_ID as Importance_ID -- invisible
Importances.Name as Importance_Name -- visible
Importances.Rank as Importance_Rank -- invisible
from
Products
left join
Importances
on Importances.ID = Products.Importance_ID
order by
Importance_Rank desc
Now... If, instead of having a textbox bound to Importance_Name, I
have a combobox with the following settings:
RowSource......: select Rank, Name from Importances order by Rank
desc
Column Count...: 2
ColumnWidths...: 0"
BoundColumn....: 1
then I get what I want: user sees importance names only, but sorting
by that field actually uses importance ranks.
However, there are some serious problems with filtering. First,
filtering just does not work in some situations. Second, even when it
works properly, I cannot avoid exposing the ranks of importances.
For example:
-- When I select a cell with "high" importance
and fire "Filter by Selection" command,
instead of showing only products with "high"
importance, somehow Access' shows tasks with
"highest" importance.
When I invoke "Filter by Form" after that,
I see "100" (i.e. the rank of "highest"
importance) specified for the fild in question,
not "10" that I would expect there.
Indeed, the Filter property of the form is:
((Importance_Rank="100"))
-- When I invoke "Filter by Form" from scratch and
specify "high" for the field in question,
Access remembers this setting correctly (i.e.
I keep seing "high" in the "Filter by Form" mode),
but filtering still does not work as needed: it
still shows products with "highest" importance only.
The Filter property of the form is the same as in
previous case.
Interestingly enough, all these problems do not happen with all values
of importances. For example, when I try to show only products with
"low" importance (either using "Filter by Selection" or "Filter by
Form"), everything works fine.
Does anybody know how to solve all these issues?
And does anybody know any better approach to the initial problem -
better than having to use combobox?
Any feedback would be greatly appreciated!
By "indirect interactive sorting" I mean a situation when a form's
field shows values of one DB column, but is sorted using values of
another column. I believe this is a typical requirement when dealing
with ordinal categorical concepts (such as named ranks, months, days
of week, etc.).
For example, here is the specific example of this problem in our
current MS Access 2003 project (ADP backed up by SQL Server 2005):
-- The system tracks products.
-- There are a few importance levels defined in the system
(ordinals named "low", "medium", "high", etc.)
-- Each product may have an importance assigned to it.
This fragment of the domain model is supported by the following two
tables in the database:
Table "Importances"
----------------------
ID Name Rank
--- ---------- -----
1 low -10
2 high 10
3 medium 0
4 highest 100
... ... ...
----------------------
NB: The "Rank" column is what defines the order of importances.
Table "Products"
-------------------------------
ID Importance_ID Title
--- ------------- ----------
1 1 Product A
2 2 Product B
3 4 Product X
4 2 Product Z
------------------------------
Now, let's say I have a form that displays products with their
importances. The question is:
How to enable interactive sorting and filtering of products
by their importances (i.e. numerically by ranks of their
importances) without exposing those numeric ranks to the user?
Proper sorting is relatively easy to achieve. The record source of
such form is more or less obvious (and may even include a default
sorting order):
select
Products.ID as ID -- invisible
Products.Title as Title -- visible
Products.Importance_ID as Importance_ID -- invisible
Importances.Name as Importance_Name -- visible
Importances.Rank as Importance_Rank -- invisible
from
Products
left join
Importances
on Importances.ID = Products.Importance_ID
order by
Importance_Rank desc
Now... If, instead of having a textbox bound to Importance_Name, I
have a combobox with the following settings:
RowSource......: select Rank, Name from Importances order by Rank
desc
Column Count...: 2
ColumnWidths...: 0"
BoundColumn....: 1
then I get what I want: user sees importance names only, but sorting
by that field actually uses importance ranks.
However, there are some serious problems with filtering. First,
filtering just does not work in some situations. Second, even when it
works properly, I cannot avoid exposing the ranks of importances.
For example:
-- When I select a cell with "high" importance
and fire "Filter by Selection" command,
instead of showing only products with "high"
importance, somehow Access' shows tasks with
"highest" importance.
When I invoke "Filter by Form" after that,
I see "100" (i.e. the rank of "highest"
importance) specified for the fild in question,
not "10" that I would expect there.
Indeed, the Filter property of the form is:
((Importance_Rank="100"))
-- When I invoke "Filter by Form" from scratch and
specify "high" for the field in question,
Access remembers this setting correctly (i.e.
I keep seing "high" in the "Filter by Form" mode),
but filtering still does not work as needed: it
still shows products with "highest" importance only.
The Filter property of the form is the same as in
previous case.
Interestingly enough, all these problems do not happen with all values
of importances. For example, when I try to show only products with
"low" importance (either using "Filter by Selection" or "Filter by
Form"), everything works fine.
Does anybody know how to solve all these issues?
And does anybody know any better approach to the initial problem -
better than having to use combobox?
Any feedback would be greatly appreciated!