I suggest adding a new table occurrence of shots and link it like this:
Shots::sParentProjectID = Shots|SameProject::sParentProjectID
List your values from Shots|SameProject, include only related values starting from Shots.
Thank you for the quick reply, Phil, just a couple of quick followup questions - the first being "do you ever sleep?", because it seems you are always helping people here. Having said that, don't sleep yet ;-)
I've implemented the TO addition you recommended and the list populates nicely with names. However, selecting one of the items in the dropdown no longer causes all of the other saucy fields on my layout to fill up with their shot-specific data. I am thinking this would be because they are coming from Shots and my dropdown is coming from Shots|SameProject? Do I reassign all of them if that is the case?
Also, a cosmetic question - when I select a new Project from that dropdown, the Shots list initially displays the shot's sID. Clicking it shows only sNames, which is all I ever really want to see. Is there a trick to tell my Shots dropdown to display its first value by sName instead of sID?
Many thanks again,
First, you have to explain how those fields are set to display data as there are multiple ways to do that. Off hand, I don't see why their behavior would change unless your value list is now entering data different from what they need to access related records.
Second there are two options:
Option 1: Use a pop up menu instead of drop down list.
Option 2: Place the hots|SameProject::sname field on top of your drop down list. Give it an opaque fill color so that you cannot see the drop down field. Use the behavior settings in the Inspector to deny browse mode access to this field. Now, when you click on the sname field, the drop down list pops to the front and deploys. When you select a value from the list, it disappears back behind sName--which now displays the sname of the selected record.
Thank you for the popup/dropdown suggestion, works a treat.
You know what else works really well? Checking to see if you didn't disable the ENTIRE script that populates your form based on what you select from the Shots dropdown list.
So I now have a popup of projects, from which selecting a project will populate my second popup with only that project's shots, and sets that second popup to the first shot in the list. Nicey-nice.
Thank you again, sir! I knew in my soul that the root problem lied in my incomplete understanding of Table Occurances, I don't know how long I would have stared at my screen before thinking of self-joining. Which sounds naughtier than it is, I think.