I was just reading “filtering in a pivot grid”, and remembered that I had this Idea.
If you have a list and a detail, you can select multiple items in the list, but the detail is only linked to the main focus item.
I have a use case in which it would be great if you could expand this to, this key or … that key, or …, etc.
With a filter it is possible to have a filter to select multiple options, but this could be less clear to the user, and the fact that it is not possible to make the filter mandatory makes that we can’t use this option.
We have a large “fact table”, and I’m looking for a clear way in which a user can select some options to query, without ever selecting everything. The user should not start with everything, but with nothing.
If you have a graph, and 1000 categories, you don’t want to start with “show everything”.
If the graph would be a detail of a list with categories, the user could select one category and see the graph, or use the control key to select more.
Hi Daan,
I feel this idea can better be resolved using checkboxes in the Parent screen to indicate the user selection. After saving the checkboxes, that then can influence the records you see in the detail. Would that be a better solution to this idea?
Thank you for the idea with the checkboxes.
I have created a functionality like this before, outside Thinkwise, and it might actually be (almost) possible to duplicate this inside Thinkwise, but I’m afraid that it is not completely possible.
The only “big” problem I see is that I can’t change the fields and the checkboxes on the parent screen dynamically, while they do.
What I want is an interface like the interface on Tweakers. When the user adds a category, the category becomes a checkbox. It could actually be possible, and it could look better then in this idea, but it should not require the intervention of a developer to change the parent screen.
We have something like this in the Thinkstore screen in the Software Factory:
This is a dynamic list filled with all unique categories and industries that come from the available solutions.
You can create a similar screen by using a Formlist. The label is a field, and the checkbox another. When a user add a category, the view can show it. When the checkbox is mutated, wrtie or delete the set filter. The filter should influnce a Prefilter that is on the subject you want to filter.
Here is the Table(View) definition:
With Layout logic, you can influence which field is shown at what occasion
Hope this is a fitting solution!
It seems like a solution.
But what I don’t get is how this could “Influence” a prefilter. As far as I know or have seen, prefilters are static. The only route I see (based on what I know) is by creating a temporary table and that would give a lot of problems. ( People doing different queries in different tabs, etc. ).
I would like to know how people made this connection in the software factory. I don’t want to program the possible categories in the prefilter, as they can be changed by users.
As I kind of mentioned before I see a workaround, but that would require the page to create a new record ( invisible to the user ), and have this formlist, as detail, set settings based on the id of this record. This work around would also make other screen compositions possible.
But, as I rater don’t have workarounds, or “inventions” in our application, it would be great if you could tell me how this has been done in the SF.
Hi Daan,
I'll try my best to explain. The filter list is a View that gathers a list of all categories and industries and shows them in a Formlist.
/* Trimmed down version of View "thinkstore_filter" */
/* Create or alter view thinkstore_filter. */
create or alter view "thinkstore_filter"
(
"no",
"thinkstore_logo",
"grp_label",
"solution_category_id",
"solution_industry_id",
"item_label",
"enabled"
)
as
--control_proc_id: view_thinkstore_filter
--template_id: view_thinkstore_filter
--prog_object_item_id: view_thinkstore_filter
--template_description:
select
cast(100 as int),
null,
'Filter on specific categories:',
null,
null,
null,
cast(0 as bit)
union all
select
cast(100 + row_number() over (order by c.solution_category_id) as int),
null,
null,
c.solution_category_id,
null,
c.solution_category_id,
cast(iif(f.solution_category_id is null, 0, 1) as bit)
from (
select solution_category_id
from thinkstore_category
group by solution_category_id
) c
left join thinkstore_category_filter f
on f.rdbms_user_id = dbo.tsf_user()
and f.solution_category_id = c.solution_category_id
union all
select
cast(1000 as int),
null,
'Filter on specific industries:',
null,
null,
null,
cast(0 as bit)
union all
select
cast(1000 + row_number() over (order by c.solution_industry_id) as int),
null,
null,
null,
c.solution_industry_id,
c.solution_industry_id,
cast(iif(f.solution_industry_id is null, 0, 1) as bit)
from (
select solution_industry_id
from thinkstore_industry
group by solution_industry_id
) c
left join thinkstore_industry_filter f
on f.rdbms_user_id = dbo.tsf_user()
and f.solution_industry_id = c.solution_industry_id
go
The checkbox influence the state of said filter. Interacting with the checkbox will either create a record in an invisible filter table or remove the record if already present. (Instead of trigger on View)
/* Create or alter trigger thinkstore_filter_iu to table 'thinkstore_filter'. */
create or alter trigger "thinkstore_filter_iu"
on "thinkstore_filter"
instead of update
as
begin
/* If no rows were modified exit trigger */
if @@rowcount = 0
return
if not exists(select 1 from Inserted)
return
-- Do not count affected rows for performance
set nocount on;
--control_proc_id: instead_of_trigger_thinkstore_filter
--template_id: insert_or_delete
--prog_object_item_id: category
--template_description:
-- When unchecking filter options, delete them for the user
delete f
from thinkstore_category_filter f
join inserted i
on i.solution_category_id = f.solution_category_id
where f.rdbms_user_id = dbo.tsf_user()
and i.solution_category_id is not null
and i.enabled = 0
-- When checking filter options, add them for the user
insert into thinkstore_category_filter
(
rdbms_user_id,
solution_category_id
)
select
dbo.tsf_user(),
i.solution_category_id
from inserted i
where i.solution_category_id is not null
and i.enabled = 1
and not exists(
select 1
from thinkstore_category_filter f
where f.rdbms_user_id = dbo.tsf_user()
and f.solution_category_id = i.solution_category_id)
--control_proc_id: instead_of_trigger_thinkstore_filter
--template_id: insert_or_delete
--prog_object_item_id: industry
--template_description:
-- When unchecking filter options, delete them for the user
delete f
from thinkstore_industry_filter f
join inserted i
on i.solution_industry_id = f.solution_industry_id
where f.rdbms_user_id = dbo.tsf_user()
and i.solution_industry_id is not null
and i.enabled = 0
-- When checking filter options, add them for the user
insert into thinkstore_industry_filter
(
rdbms_user_id,
solution_industry_id
)
select
dbo.tsf_user(),
i.solution_industry_id
from inserted i
where i.solution_industry_id is not null
and i.enabled = 1
and not exists(
select 1
from thinkstore_industry_filter f
where f.rdbms_user_id = dbo.tsf_user()
and f.solution_industry_id = i.solution_industry_id)
end
go
A prefilter in the main screen reads which filter has been set based on the records in the invisible filter table. The Thinkstore uses this prefilter query for example:
( /* Check Category filter */
not exists(
select 1
from thinkstore_category_filter f
where f.rdbms_user_id = dbo.tsf_user())
or exists(
select 1
from thinkstore_category_filter f
join thinkstore_category c
on c.solution_category_id = f.solution_category_id
where f.rdbms_user_id = dbo.tsf_user()
and c.solution_id = t1.solution_id)
)
and
( /* Check Industry filter */
not exists(
select 1
from thinkstore_industry_filter f
where f.rdbms_user_id = dbo.tsf_user())
or exists(
select 1
from thinkstore_industry_filter f
join thinkstore_industry c
on c.solution_industry_id = f.solution_industry_id
where f.rdbms_user_id = dbo.tsf_user()
and c.solution_id = t1.solution_id)
)
The code checks if any filter has been set and if so, which filters have been applied.
Using auto-save and auto-commit (in Universal GUI), a change can immediately influence the list of items based on the user's selection.
Hope this helps!
Yes, Thank you,
I understand the solution like this.
There is one small bug; if the user opens the application in 2 browser tabs and makes different selections they interfere. I was breaking my mind over a way to work around this little problem.
I think that the solution would be to create a new unique key in the selection table each time the selection changes, and this would only work completely if Thinkwise would keep the current record id in the url and navigate to it during a refresh. ( It would also allow for url sharing of a selection ).
I just noticed that opening this url in another browser redirects to the same topic.
How do I do this?
I mean, the record Id is in the url here, but not in our application, and I think that this is a difference between the web interface and the universal interface.
As for this particular Idea, for these kind of specific cases I believe the solution as elaborately explained by
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.