Within a straight table, restrict selections to a lower level of data hierarchy

[justin_morley]

Hi,

I have a table of synonym data for one of the concepts in the data model, component.

One component can have one to many synonyms as shown in the following table:

Component, Synonym

a, a

a, a1

a, adg

b, b

b, brt

c, c

I have a filter box that allows filtering by synonym, which behaves as required.

What I need in this scenario, is a straight table which displays all the synonyms for the components which are currently possible,

i.e. it ignores the current synonym, goes down the data hierarchy to the currently possible components, and displays these and all their synonyms.

e.g. If Synonym 'a1' is selected in the above table, this new table would show the following:

Component, Synonym

a, a

a, a1

a, adg

Alternatively if Synonyms 'a1' and 'brt' were selected, the new table would show the following:

Component, Synonym

a, a

a, a1

a, adg

b, b

b, brt

Is this possible?

Thanks,

Justin

[jonathandienst]

Hi

In your expression, override the selection of synonym (to ignore the selection), as in the following example:

     Sum({<Synonym=>} Amount)

Hope that helps

Jonathan

[justin_morley]

Hi Jonathan,

Thanks for the reply, but I can't get this working.

I thought Sum (and most aggregating functions) only worked with numeric data?

All these fields contain non numerical data as shown in my example, no numerics involved at all, and I don't want to do any aggregation, just restrict selections

[somenathroy · Accepted solution]

PFA..

Regards,

Som

[justin_morley]

Hi Som,

Thanks for this.

Your stand alone app works absolutely perfectly! I will mark the answer as correct for this.

It doesn't work when I transfer it into my app, which is strange but I'll investigate that seperately.

Thanks,

Justin

[somenathroy]

You may also use the below expression instead:

Only({<Component={'$(=Concat(Distinct Component,chr(39)&chr(44)&chr(39)))'},Synonym>}Synonym)

Regards,

Som

[jonathandienst]

Justin

I got this to work with a test data set:

Sum({<Component = P({<Synonym = P(Synonym)>} Component), Synonym= >} Quantity)

Jonathan

[justin_morley]

Thanks to Som and Jonathan for the replies.

Jonathan, you've inserted a field called Quantity into your answer. As stated earlier, there is no such numeric field in this instance.

Som, you have nailed the right answer, so thanks for that. Further investigation reveals why it wasn't working in my case - I didn't reveal the full picture - the Synonyms and Components are in different tables, linked by a common id field. It was necessary to reference the id rather than the actual component as follows:

Only ({<ID={$(=Concat(ID,','))},SYNONYM>}SYNONYM)

This now behaves as desired.