Hide values in List Box when no output

[pvanderkolk]

Hi all,

I have a List Box with a field that is connected to my Straight Table. However, some of the values in the List Box don't have any outcome in the Straight Table. That's why I want to hide these values without selected output. But since the data in the Straight Table changes a lot, it can happen that one of the hidden values gets an output in the Straight Table. Obviously I want this value to be shown in the List Box again. Unchecking 'Hide Exclude Values' in the Properties menu of the List Box won't do as, next to the fact that the database changes, we can also make a selection in other List Boxes. Do you know a formula to use in the script/List Box that shows only the values in the Box that have a result in the Straight Table?

Thanks a lot!

Pim

[Anil_Babu_Samineni]

Would you share screenshots please

[CarlosAMonroy]

Hi Pim,

You can use the 'Where exists' clause in the script.

The definition is the following:

Table1:

Load KeySourceTable

From Table1.qvd (qvd);

Table2:

Load KeyReferenceKey, Fields

From Table2.qvd (qvd)

where exists (KeySourceTable, KeyReferenceField);

Hope that helps,

Carlos M

[MarcoWedel · Accepted solution]

Aggr(YourField, YourField)

[pvanderkolk]

Hi Carlos,

Some of the values are hidden now, but not all. This is the situation I have:

My ListBox is connected to a table, which is connected to the table with my dimension from the straight table in it (as shown below)

...                                        ....                                         ....

A   ------------------------------        A                                          ....

...                                         B ----------------------------------        B                   

...                                        ...                                           C

Value A is stated in a Resident tab on a script tab after the database table of column B and C. Originally his was my line in the script: Resident ..... Where Exists (C,A). But my list box was empty when using these two values, I've changed it to Resident ..... Where Exists (B,A).

FYI I'm using the resident tab because I got a syntax error when putting the 'Where Exists' clause after the 'From' statement.

[CarlosAMonroy]

Where exist works after From or Resident statement. Would be easy if you could share your qv app.

[pvanderkolk]

I'm afraid I'm not allowed to do this. I preferred it would have worked in the script, but I'll just change the expression of the list box. Thanks for your help.

[pvanderkolk]

Thanks Marco. Sometimes the answer is easier than you think The sorting got messed up, but by adding an expression in the sort tab of the list box this got rectified too.