It would probably help if you posted a screen shot of your Relationships Graph.
I agree with Mike that a screenshot could help. I might have this, though:
I'm assuming that there's DESIGN SERVICES::BuildingID that is a multi-line key. If it's not, you could calculate one with List ( UnnamedJoinTable::BuildingID ).
Create a new TO for DS_rooms_vl, where
DESIGN SERVICES::CompanyID= DS_rooms_vl::CompanyID
DESIGN SERVICES::BuildingID= DS_rooms_vl::BuildingID
Make a value list using DS_rooms_vl, showing only related from DESIGN SERVICES.
Does that do what you want?
Apologies for being vague in the original post. I put together a section of database the database Im working on as a separate file. Essentially, it is for a design company to make proposals. A proposal will always only have one company. A company can have many buildings. But the proposal may only focus on, say, 2 out of 3 of its buildings. (Those same buildings can have other companies as tenants.) Rooms are assigned to buildings, as well as companies.
I have attached screen shots, plus the actual database.
So if for proposal-1000, the user chooses ACME as the company. And then selects only the Brown Building and Red Building as subjects for the proposal. (A value list only provides buildings assigned to ACME as candidates.)
How do I compile a list of rooms, rented only by ACME in the Brown Building and Red Building?
The correct list should have:
You're a lot closer than you think.
As you noticed, the join for the related records is a little too loose for this purpose going through the join table. So you have to go directly to the target TO. See attached modified file.
prototype.fmp12.zip 71.3 K
Thanks, Mike. That's so helpful. It's my style to avoid a lot of multi-criteria joins, but rather use more inherent logic to organize my related records. So this is perfect for my task. (See attached screen shot.)