Is it possible for the system to have a different filter based on the selection made by the user

[#1]

Lets say I have two tables, "first name" and "family name".

If I select "Brian" in the first table, I should get a list of all the family names that correspond.  However, if in the first table I also had a column for a uniqueidentifer and so each "Brian" would be shown on separate rows.  In this scenario I would like to select "Brian" and get the family name that corresponds to the uniqueidentifier.  I tried concatenating the firstname with the uniqueidentifer but clicking on the selection again does not deselect the choice, which isnt very satisfactory for me.

In essence, I would like to either:

1. make a selection but the system subsitutes the filter for another field

or

2. make a selection and the whole row is used as a filter

What are my options?

Thanks,

[#2]

why not just concatenate Firstname with Surname and select based on the whole name?

[aveeeeeee7en]

What you said is working.

Try this script:

LOAD
ID,

Name
FROM
[\SAMPLEDATA.xlsx]
(ooxml, embedded labels, table is Sheet1);
Left Join
LOAD
ID,

[Family Members]
FROM
[\SAMPLEDATA.xlsx]
(ooxml, embedded labels, table is Sheet2);

FINAL:
Load
ID&'/'&Name AS KEY,
Name,
[Family Members]
RESIDENT MAIN;
DROP TABLE MAIN;

[#4]

I was giving a simplified example but to answer your question I'm going to add some more conditions:

• there are other columns linked to the family name that I do not want to show in the first table
• It is possible that there will be multiple people with the same first and family name combo and the uniqueidentifer is the only guaranteed way of getting an exact match

I have tried concatenating the uniqueidentifier with the "first name" and this does the trick but clicking again on this field does not remove this selection, so it isnt a satisfactory solution for me.

[#5]

Hi Nag,

I am not following this solution and cant see how this addresses the question that I raised.  For starters there is only one table in your QV document and you have used family members when I said I wanted to match the first name to the surname

[#6]

Can you post a diagram of what you are trying to achieve

[#7]

Ok, I'll flesh out a scenario:

Data:

ID    Name    Surname     Mother's Maiden Name

1     Brian     Smith          Mary

2     Brian     Jones          Carol

3     Brian     Smith          Beatrice

4     Colin     Fleming       Freda

5     Colin     Ducky          Doris

Table1 shows:

Brian

Colin

Table2 shows

Smith, Mary

Jones, Carol

Smith, Beatrice

Fleming, Freda

Ducky, Doris

What I ideally want is the first listbox to show all the firstnames (5 entries) and be able to click on one of them and the second listbox to only show the surname, mother's maiden name that corresponds with the ID.  I don't want to click on the ID as it would be more natural for me to select on the name.

Does this make sense, and how would I tackle this?

Many thanks