Value list from multiple fields to display related records in a portal
I've looked around for related discussions on this topic, but haven't quite found what I'm looking for. Thanks in advance for any troubleshooting you're able to provide...
My database has a table (People) that has two dates associated with each (Date1 and Date2). On a separate table (Memos), I display a portal of my People that uses a field (DateX) to filter which People are displayed in my portal. I'm having problems with my DateX field - it hasn't ever really worked. What I need it to be is a drop-down or pop-up of each of the dates available in Date1 and Date2 on the People table.
For example, on my People table I would have 3 records with 5/1/2013 in Date1 and a different 3 records with 5/1/2013 in Date2. On my layout for the Memo table, I would like to then click on DateX and find 5/1/2013, select that, and then see the list of 6 People in the portal.
I realize that over time, I might have too many dates populating here, so I would like to also have DateX only show dates that fall within the past year. But that is just icing on the cake.
What I've done is try to use the List function to create a field containing all the dates from Date1 and Date2. This hasn't worked for me (only Date1 dates appear). Perhaps my relationships aren't set up correctly - currently I have a field on the People table (ListDates) that is the list function for the two date fields and that is related to DateX. Maybe I don't even need the separate table for Memo. Not quite sure.