Let's say, hypothetically, that I have a database of people. And for each of those people I have records in related tables - addresses, companies they work with, roles they've played, etc.
And say I decided I wanted to divide those people up into two, broad categories - people I knew to be aliens in disguise, and those for whom their planet of origin is probably earth. (And just to be clear, I'm using this as an example - I couldn't give a tinker's cuss about extraterrestrial life.) So I have a table called Person and for each record there is a field called IsActuallyAnAlien that is either empty or contains the word Yes.
I want to be able to populate value lists with either just aliens, just non-aliens, or everyone in the database. I'd like to be able to have layouts based on those collections, and/or display a pop up menu of aliens, non-aliens, or everyone.
The Person record includes a pk_PersonID as the key. It also has the IsActuallyAnAlien field which is either Yes or empty. And it has a global field called IsYes which is the word Yes.
In my relationship diagram I have the Person table connected to various other tables, using the pk_PersonID field as the key.
If I create layouts and use "Go To Related Record" this all works fine. It shows all the Person records and everybody is happy.
But I wanted to be able to work with just one type of Person or the other, and things got difficult.
I created three instances of the Person table - PersonHuman, PersonAlien, and PersonAll.
PersonHuman does a match on the pk_PersonID field using a cartesian join (X) and then matches records where IsActuallyAnAlien = ""
PersonAlien does a match on the pk_PersonID field using a cartesian join (X) and then matches records where IsActuallyAnAlien = "Yes"
PersonAll just does a match on the pk_PersonID field using a cartesian join (X)
I would have assumed that I could work with the PersonHuman table and only see the humans, PersonAlien and only see the aliens, etc. What I get is less easy to understand.
If I create a layout using the original Person table and put a portal on it linked to one of the three instances, it displays correctly. A portal linked to the records in PersonAlien shows just the aliens, PersonHuman shows just the humans, and PersonAll shows everyone. This led me to the clearly foolish assumption that those filtered tables contained just the records I want.
But if I create a value list using one of those filtered tables, I get ALL the records. And if I create a layout using one of those filtered tables, I get ALL the records.
I can't find a way to show only the aliens, only the humans, or all the persons in a pop up menu. Or work with only those records in a layout. I know I can do a "find" to narrow down a layout, but the popup has me stymied. I've spent two days and tried at least a dozen different approaches.
So the question is twofold - what am I misunderstanding about filtered tables, and how do I make a pop up list that shows just one of those groups of records?
(Oh, and I'm using the pop up to select the index field while displaying the person's name, in case that's relevant)