M2m based reference qualifier

[Community Alums]

I have two tables: „Thing" and „Set of Things".

I have set up an m2m relationship between the two: one „thing" can belong to many „sets of things" (and of course a „set of things" can contain many „things").

Say there are 3 things and 2 sets; things 1 and 2 belong to „set 1", things 1 and 3 belong to „set 2", etc.

At some point users choose a set and than based on that they pick (a) thing(s).

My question is how can one add a reference qualifier to a reference (or list editor or slush bucket) field so that it (only) shows the list of things based on the set of things chosen.

Of course as a last resource ne could just return a list of sys_ids, but contrary to the example, many thousands of thing could belong to a set - I'm not sure this would work performance wise.

Example:

1. Table „Thing":

- Thing 1

- Thing 2

- Thing 3

2. Table „Set of Things":

- Set 1

- Set 2

3. Table „Thing M2M":

- Set 1 - Thing 1

- Set 1 - Thing 2

- Set 2 - Thing 1

- Set 2 - Thing 3

- Set 3 - Thing 2

- Set 3 - Thing 3

Example UI:

- (Reference) Field „Set of Things": „Set 2"

- (Reference/List/Bucket) Field „Things": <Should allow picking „Thing 1" or „Thing 3" only>

[Goran WitchDoc]

It's a bit late for me to throw in a full solution.

Take a look here, where I filter a list collector based on another list collector and see if it can get you started. Otherwise I'll check in tomorrow and see where you're stuck.
Filter List Collector with List collector

//Göran

[Community Alums]

I has a quick look at the article, I'll give it a longer look in the morning (it is also very late here), but I don't think this is the answer to my problem.

You see the second (reference) field (that I want to filter based on the first field) is based on table „Things"; the problem is that the table has no reference fields at all. It is the M2M table that holds a (or several) references to it. In other words I need a join between the „Things" table and the M2M table and than actually filter the M2M table.

What I need is to „express" the snippet below as a query (just don't know how):

var x_gr = new GlideRecord ('<thing>');
x_gr.addJoinQuery ('<m2m>', '<thing sys id>', '<thing sys id in m2m>').addCondition ('<set of things sys id in m2m>', '18d9d51837b6be404769d2e843990e94');
gs.print (x_gr.getEncodedQuery ());
x_gr.query ();
while (x_gr.next ()) {
       gs.print (x_gr.getDisplayValue ());
}

The script works as expected, but that doesn't help.

The encoded query returned only shows the filtering on the „set_of_things" table (<set of things sys id in m2m>=<sys_id>), does not „express" the joining part.

[-O- · Accepted solution]

I have finally found the answer to this problem:

If we have tables u_set_of_things (table "Set of Things" from above), u_thing (table "Thing" from above) and u_m2m_things_set_of_things (table "Thing M2M" from above) than we can create a Catalog Item having variables:
- variable 1: {
    Type: Reference,
    Question: The set of things,
    Name: the_set_of_things,
    Reference: Set of Things [u_set_of_things]
}
- variable 2: {
    Type: List Collector,
    Question: The thing,
    Name: the_thing,
    List table: Thing [u_thing],
    Reference qualifier: javascript: 'SUBQUERYsys_id,u_thing,u_m2m_things_set_of_things^u_set_of_things=' + current.variables.the_set_of_things + '^ENDSUBQUERY^EQ',
    Variable attributes: no_filter=true,ref_qual_elements=the_set_of_things
}
If now the item is opened in the Platform UI (CMS) or the Portal, whenever question "The set of things" is updated, question (list collector) "The thing" is re-filtered to only show those things that are linked to the selected set by a record in the m2m table.

Given the data set above, when one selects "Set of Things" "Set 2" in the reference field/question, only "Things" "Thing 1" and "Thing 3" will be shown in the "Thing" field/list collector.