Create a global table with a global field StatusView. Make a relationship between StatusView and PlayerStatus.
In the Global Layout enter "Active" into the StatusView field. If you want to view inactive records enter "Inactive."
Create a new value list.
Use value from first field Player
Check the radio button "Include only related values starting from", and select global click ok.
A simpler way is to define a calculation field such as:
If ( PlayerStatus = "Active" ; PlayerID )
This field will be empty if the player's status is not empty.
You can then define your value list to list PlayerID's from this calculation field instead of the PlayerID field. (And the player name field can still be used as the secondary field for your value list.)
Ok so just create a new calculation field that reads If ( PlayerStatus = "Active" ; PlayerID )
Doesit matter if the PlayerStatus Field has active and inactive for choices in each record?
In another solution I have another situaiton similiar to this. I have a high school class field. So I have 2012,2013,2014,2015,2016. SInce 2012 isn't in use, but want to keep the records if I want a dropdown list to only have players from 2013-2016 Can I use the same calculation that reads If ( Playerclass > "2012" ; PlayerID )?
As written, the calculation only works if "Active" is the only text in that field. Add any additional text and that expression will not evaluate as true and you'll need a more sophisticated calculation.
Your class field might be better off if replaced with a related record with a record in it for each year, but working with what you have, I'd use this calculation:
Substitute ( Class ; "," ; ¶ )
With text as the return type to get a return separated list of values. Then I'd use the relationship base method of getting a conditional value list such as S Chamblee suggested, but with this relationship:
YourTable::gMinClass < PlayersByClass::cClassList
Base your Value list on PlayersByClass with YourTable as the "starting from" table and the value list will return all Players that have at least one class year that is greater than or equal to the value in gMinClass. (So you'd enter 2013, not 2012 in this example.)
If you need to see some links on how to set up conditional value lists (I think you've already seen them), let me know.
Phil, I tried it, and it worked well sort of. It cut down the names like I thought it would, but it sorted the dropdown list by the number. Is there away where it can sort by the full name?? I tried this and then the whole list went out of wack. When I sorted it back to the number it cut the name s down but its too hard to look for the player by the numnber. Is there something that I did wrong? I made sure the calculation was set to a field that only had one answer to it.
If PlayerID if the first field and PlayerName, the second, you should be able to sort your value list by the PlayerName field.
You may also want to consider setting up a more sophisticated selection method where you can type in a partial name and "prune" the list by the partial name. Such methods do require scripting, and sometimes a portal to pull off, but can make working with long lists of names, much easier than the standard value list.
I'd consider it for sure. Can you help me with that?
As far as the value list it still wouldn't work the way that I needed it to as far as the sorting. I created the calculation field set it to a drop down list the two fields that I wanted it to show is that field and the Full Name of the player. When I sorted by the calc field it gave me only NBA players which was great but sorted by number. When I sorted by the Full Name it gave my all the players in the database.
If you think the portal route is the way to go please let me know how to get that started.
When I sorted by the Full Name it gave my all the players in the database.
That shouldn't be the case. What version of FileMaker are you using? Care to let me take a look at the file?
Here are a pair of demo files you can examine. Let me know if any of the methods demonstrated appeal to you:
Phil I sent you the link although let me close out of my file and send it to you so it can log the changes
Ok the link I sent you should work. It's not a huge deal, but in a couple of years the names will be tough to sort through. I'm looking at the demo file that you sent me now. Is this better than the calculation field??
Sorry Phil still using FMP 11
You don't have to choose between. You can use both.
The advantage to the various methods used in the two demo files is that you can progressively narrow the list of values by typing in text that partially matches a player's name. The list that is narrowed down can start with the list of active players and then the methods used in the demo files can further narrow the list as you enter a portion of the player's name. Once you have selected the specific player, the Player's ID number--not their name is entered into the appropriate field to establish a link to that player's record.
The "contains" selection portal is one that you might take the closest look at. With it, you can enter "ris" to search for a player named "Chris" whose name might actually spelled as: Chris, Kris, Christopher, Kristopher, Christofferson, etc...
The file that I downloded did not include a file extension. Is this a .FMP12 file, .Fp7? or ???
FP7 strange that it didnt show the ext