Same field twice in one table?

[#1]

I need some modeling help.

I have a table of Messages with the following structure:

• MessageID
• From
• To
• Message

I also have a table that has a list of names in a Names field. It contains all the names of everyone who has sent or received a Message.

How would I make it so I can select a name in the Names field and have it show all/only the messages that are From OR To that Name?

I've attached an example for reference.

Thanks!

[#2 · Accepted solution]

Here's a quick way. You need a chart instead of a Table Box. Add Message Idea, From and To as dimensions. For the expressions, use:

If(To=Name or From=Name, Contents)

That will work if one name is selected.

There are other options including Triggers when a selection is made on name.

EDIT: To show everyone when no name is selected and to work with multiple selections, change it to:

If(GetSelectedCount(Name)>0,
 If(Index(GetFieldSelections(Name,';'),To)>0 or
 Index(GetFieldSelections(Name,';'),From)>0, Contents),
Contents)

[#3]

*sigh* I answered my own question. Why does that only happen AFTER i post?

I guess it is so that I can share the answer:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
Messages:
LOAD * INLINE [
 MessageID, From, To, Contents
 1, Jim, Joe, Hi joe
 2, Joe, Jim, Hi jim
 3, Fran, Ed, Bye Ed
 4, James, Fran, Can we do the meeting at 3?
];
People_tmp:
LOAD
 [From] AS Name,
 [From],
 To
RESIDENT Messages;
OUTER JOIN
LOAD
 To AS Name,
 [From],
 To
RESIDENT Messages;
People:
LOAD
 Name,
 [From] & '$$' & To AS %name_key
RESIDENT People_tmp;
LEFT JOIN(Messages)
LOAD
 *,
 [From] & '$$' & To AS %name_key
RESIDENT Messages;
DROP TABLES People_tmp;