Create a List View to show family members from one table

[Frisco]

Hello!
This issue has me completely stumped!

I have a table with the following columns

Column1:  contact_id (unique)
Column2: group_id
Column3: first_name
Column4: last_name
Column5: relation

I want to create a a list for the group of contacts that are related to another.  The group_id has the same value when they are related.
Ideally, I would like the view to show a given contact, with a list of family members below.
the goal is to not only show the list of relatives, but eventually create an action to, lets say, email the entire family at once.  The actual data looks something like the list below.  The example contains 3 sets,  or groupings of families.

contact_id, group_id, first_name, last_name, relation,
1234, 4422, John, Doe, Father,
1235, 4422, Jane, Doe, Mother,
1236, 4422, Kim, Doe, Daughter,
1237, 4555, Jess, Rowe, Mother,
1238, 4555, Jack, Rowe, Son,
1239, 6443, James, Dylan, Father,
1240, 6443, Janet, Dylan, Daughter,
1241, 6443, Jill, Dylan, Daughter

Help!

[dbaum · Accepted solution]

SELECT(Contacts[contact_id], [group_id] = [_THISROW].[group_id])

Consider instead creating a dedicated Groups table and then making your Contacts table a child of the group table.

[dbaum · Accepted solution]

SELECT(Contacts[contact_id], [group_id] = [_THISROW].[group_id])

Consider instead creating a dedicated Groups table and then making your Contacts table a child of the group table.

[Frisco]

I do have a table called contact_group and it does contain two columns:

column1:  contact_id
column2: group_id

I have tried bringing this table in and under the contact_group table make the reference back to the contacts table, selecting the contact_id.
I tried creating a virtual column to get the First and Last name and relation but I cannot get them as a list.
I hope this makes sense.

[dbaum · Accepted solution]

No, I'm not following. It's unclear to me whether you still have a question.

In case it's helpful, here's what I meant by "creating a dedicated Groups table and then making your Contacts table a child of the group table".

• Table: Groups
  • Column: group_id
• Table: Contacts
  • Columns: contact_id, group_id, first_name, last_name, relation

In the Contacts table, make the group_id column a Ref type that references the Groups table. That will automatically generate a column in the Groups table that for each row contains a list of the group's contacts.

[Frisco]

Thanks for your responses @dbaum .  The SQL statement with the dedicated groups table did the trick!