Multiple keys to select from detail in relationship

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?

New→Needs feedback

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:

Categories and Industries

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!

Terms & ConditionsCookie settings