1 2 3 4 Previous Next 59 Replies Latest reply on Oct 26, 2012 2:31 PM by MikeProcopio

    Help with Value list Filtering

    MikeProcopio

      Title

      Help with Value list Filtering

      Post

           I have a database of basketball players. Here is a status field that they are either active or inactive. How do I create a value list of players that are ACTIVE only??

            

           Thank you 

        • 1. Re: Help with Value list Filtering
          schamblee

               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.

                

                

          • 2. Re: Help with Value list Filtering
            philmodjunk

                 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.)

            • 3. Re: Help with Value list Filtering
              MikeProcopio

                   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 )?

              • 4. Re: Help with Value list Filtering
                philmodjunk

                     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.

                • 5. Re: Help with Value list Filtering
                  MikeProcopio

                       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.

                  • 6. Re: Help with Value list Filtering
                    philmodjunk

                         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.

                    • 7. Re: Help with Value list Filtering
                      MikeProcopio

                           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.

                            

                           Mike

                      • 8. Re: Help with Value list Filtering
                        philmodjunk

                             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:

                        https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

                        https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12

                        https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

                        • 9. Re: Help with Value list Filtering
                          MikeProcopio

                               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 

                          • 10. Re: Help with Value list Filtering
                            MikeProcopio

                                 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??

                            • 11. Re: Help with Value list Filtering
                              MikeProcopio

                                   Sorry Phil still using FMP 11 

                                    

                              • 12. Re: Help with Value list Filtering
                                philmodjunk

                                     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...

                                • 13. Re: Help with Value list Filtering
                                  philmodjunk

                                       The file that I downloded did not include a file extension. Is this a .FMP12 file, .Fp7? or ???

                                  • 14. Re: Help with Value list Filtering
                                    MikeProcopio

                                         FP7 strange that it didnt show the ext

                                    1 2 3 4 Previous Next