This type of conditional value list is sometimes called a diminishing value list.
One method is illustrated in this demo file: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7
If you have FileMaker 12, there are ways to use the Execute SQL function to support a diminishing value list: Diminishing Value Lists and ExecuteSQL
Thanks Phil it worked perfectly! I have another question with the same project. I am using the diminishing value list on my "Asset" layout as well. This is just like the situation above except I will be adding projects to an asset instead of an asset to a project). The dropdown value list is listing all projects and as I add a project to the portal it disappears from the list - perfect. Now, I have a field in the projects table named "Status" where a project can be "active" or "complete". I would also like this value list to omit all projects with a status of "Complete" as well as the diminishing list. Any suggestions?
A diminishing value list has this basic relationship:
ParentTable::ExclusionList ≠ ChildTable::IDField
If you define a calculation field, constActive as returning "Active" with Text as the return type, you can modify this relationship to be:
ParentTable::ExclusionList ≠ ChildTable::IDField AND
ParentTable::constActive = ChildTable::Status
I'm new to calculations- what would that calculation look like? Also, what is a good source (book, online) for me to gain an understanding on how calculations are constructed?
This is as simple as calculations get.
Select the Parent Table table from the tables drop down. (Use your table and field names in place of mine.)
Enter constActive as the new field name.
Select "calculation" in the drop down of field types on the right.
The specify calculation dialog will appear.
Enter "Active" in quotes. (I am assuming that records that are "active" have this exact text in the ChildTable::Status field.)
Select Text from the return type drop down in the lower left corner of the specify calculation dialog.
Click OK and you are done.
Now click over to the Relationshps tab and update your relationship to include constActive and Status as additional match fields in the relationship.
I don't know of a good resource on calculations to recommend though there are a number of books on FileMaker that you can research to see if any are a good match for what you need.
Again, thanks so much Phil! I found a book that may be helpful and comes with exercise files. It's called "Filemaker Pro 12 the missing manual" seems to be pretty good.