1 2 3 4 Previous Next 47 Replies Latest reply on Oct 16, 2012 9:48 AM by MikeProcopio

    Value Lists and Searches

    MikeProcopio

      Title

      Value Lists and Searches

      Post

      Ok so my question has two parts. FIrst if I have a list of names is there a way that I can filter the names in my value list? Each record has an ID number associated with it and it lists every name in the database on the value list on a drop down menu. If I wa charting graduating classes and say I had 2001-2010, is there a way to set it where the value list only shows people that have graduated between 2007-2010 instead of listing everything??

       

      Second part is there an easy way to put a search bar/engine in a portal?

        • 1. Re: Value Lists and Searches
          philmodjunk

          Brace yourself Mike, I'm going to toss a lot of links at you. Feel free to follow up here with any questions you have after digging through them. And I'm going to ask to you "take one for the team" here. That "NoFields" in the title is evidence of a bug we seem to be having trouble convincing the RightNow folks to believe that it exists--screen shots not withstanding. I've thus passed the link to this thread on to ModMan so that he can pass it on to them.

          If you edit your original post--which fixes the issue, it won't be there for them to see when they check it out.

          First request is for what we call a "Conditional Value List" where you can filter down the list of values to a subset of the total.

          Forum Tutorial: Custom Value List?

          Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

          Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

          Hierarchical Conditional Value lists: Conditional Value List Question

          and then the "search bar" request sounds like what I have several examples of in this demo file:  https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

          • 2. Re: Value Lists and Searches
            MikeProcopio

            I went through the links and in your forum tutroial in the first link it seems like the fitst option is th best for me to enter a calculation, but I didnt know where to eneter the calculation for the value list.

             

            I looked into conditional value list, the problem is it wants you to enter values in two seperate tables for values. I want the value list to come from tables that my records are already entered into. Unless I am mistaken it seems like you have to re enter data for it to appear in the conditional value list.

             

            So what I am trying to do is this. When I am entering scouting reports for players, I just want to have players in a 4 year period in the value list for them. I have a field named class. Class of 2012,2013,2014, and so  on. WHat I want is to set it up where I can set the perameters of the class so only players in certain classes show up in the value list. Today it's not a problem but 5 years from now I dont want players from 2011 showing up in it because there will be no reason. I dont want to delete these players as it is always good to look back on old data, but I just want them not to show up in vlue lists anymore.

            • 3. Re: Value Lists and Searches
              philmodjunk

              I don't think the calculation option is a good choice here. That would require adding a new calculation field and a new value list for each date interval. A relationship based value list will avoid those complications and be much more flexible.

              I looked into conditional value list, the problem is it wants you to enter values in two seperate tables for values.

              That is not a requirement of conditional value lists. You DO need a relationship between two tables, but the values are only entered in one table and the relationship can even be self join between two occurrences of the same table should that be necessary.

              it seems like you have to re enter data for it to appear in the conditional value list.

              Not really, though this is the most common way to use such a value list, it does not have to be set up that way. A calculation field or script can set up the filtering value used in the relationship for you.

              So what I am trying to do is this. When I am entering scouting reports for players, I just want to have players in a 4 year period in the value list for them

              What factors determine WHICH 4 year period is the one to use in the value list?

              I have a field named class. Class of 2012,2013,2014,

              Is this how the data ie entered? A series of 4 digit years separated by commas? (Not the best way to enter this data, but we can make that work if that's the way it's set up.)

              So far as I can tell, a conditional value list based on a relationship that filters out player records where there isn't a "year" in the desired range of values should work, but it's not clear how you want to specify the range of years. It could be set up as the range from this year to the previous three years or it could be something you specify by picking a range from a value list in one or two fields. The basic implementation is the same except for how you set up the values used to filter on your range of years.

               

              • 4. Re: Value Lists and Searches
                MikeProcopio

                Ok so the 2011, 2012, 2013 arent what you choose from. THose are the numbers entered in the original title. I have a drop down list of a value list of years 2011 through 2016.

                 

                Seems like a lot of work just to cut down the value list that I have set up. I though maybe there was an easier way to cut the list down. 

                 

                So basically if I am dealing with players in my database that I update on a regular basis that are in the classes of 2012-2016 I would like that range in my filter so plyaers in classes before 2012 and after 2016 are not in the list. If there is an easy way to do this great, if not I can live without it.

                 

                As far as the serach portal feature that you posted, can I set this up with my database now or do I have to start everything over and start again?

                • 5. Re: Value Lists and Searches
                  philmodjunk

                  This shouldn't take all that much work to do, but I still don't have a clear understanding of this key detail:

                  How is the 4 year "class" of a given player recorded? What is the format for that?

                  I think we can add a calculation field that uses that data to create a 4 value, return separated list of years and then we can use it in a relationship that very easily filters players.

                  But you still haven't indicated how you would specify the 4 year class that you want to use for limiting the value list. User data entry? Data derived from the current date? or ???

                  And it raises a new question. If you wanted all players in the 2009 - 2012 "class". would a player in the 2008 - 2011 class be included or excluded? I've assumed such a player record would be included but if not, the changes to this approach needed to exclude the player would not be very complicated to do.

                  As far as the serach portal feature...

                  I see no reason why you'd need to start over. Let's get your value list working first, then tackle that one--perhaps in a new thread without the NoFields title...

                  • 6. Re: Value Lists and Searches
                    MikeProcopio

                    Oh ok my fault. It's a graduating class so it is only 1 number. So players graduating this year their class would be 2012 and so on. The data in the field isnt a range it is one number. What I was asking is if I wanted a range could I do that so say I had 1,000 records in each class and I had 7 years of classes say from 2011-2017 If I wanted to set up a calculation that only used players from 2011-2012 to cut the numbers from 7,000 records to 2,000 records how would we do that.

                     

                    Sorry about that Phil

                     

                    Mike

                    • 7. Re: Value Lists and Searches
                      philmodjunk

                      That's even simpler, but still leaves an unanswered question:

                      How will you specify the range of years you want for your value list? You could easily define two global fields and enter a year in each, or you can set up calculation fields that work from the current date.

                      Let's assume that you want the user to select the range. Define two global fields in the table where you want to use the valuelist to enter data. Define this relationship:

                      TableWhereYouUseValueList::gYear1 < PlayerData::Class AND
                      TableWhereYouUseValueList::gYear2 > PlayerData::Class

                      Set up your value list with specify fields to list data from PlayerData, Include only related records starting from TableWhereYouUseValueList.

                      And don't forget that TableWhereYouUseValueList and PlayerData can be two occurrences of the same table, though this seems very unlikely here.

                      Note: this use of inequalities in a relationship can produce a slower than desirable response time when used with tables that have a lot of records. If you find the response time is too slow, we can set up a custom function that produces a return separated list of years from gYear1 to gyear2 and use it with the = operator in a simpler relationship to get the same list of values.

                      • 8. Re: Value Lists and Searches
                        MikeProcopio

                        I sort of understand what you are saying. Ok so I go in the table and create 2 global fields and enter years in both. So say I wanted a span of 2012-2014 I would enter 2012 in my first global field and then 2014 in the next?

                         

                        THe next steps are a little confusing to me. Where do I enter those calculations that you listed on the Value list menu?? I didnt see a place to enter this info.

                         

                        I know I am a lot slow so bare with me a lttle bit :-)

                        • 9. Re: Value Lists and Searches
                          philmodjunk

                          You started out on the right track. You'd enter or select years for your range in the two global fields.

                          What I posted next is not a calculation. It's a relationship.

                          Add an occurrence of the table containing your player data to your relationship graph. Drag from one of the global fields to the class field in this new occurence. Double click the line this produces and edit the relationship to match by both pairs of fields with the operators shown in my example.

                          • 10. Re: Value Lists and Searches
                            MikeProcopio

                            OK so I made the two global fields in the table.

                             

                            On the relationship that is where I get a little confused. Here is my graph listed below. My player base is the main table with all the information and is where I put the two global fields in. What other table will I make the relationship with "Player Base"?

                            • 11. Re: Value Lists and Searches
                              philmodjunk

                              You'll need to tell me.

                              In what table do you need to use this value list for entering data? That's the table where you need to define the global fields.

                              In what table do you have the "class" specified for each player?

                              I've assumed that you have "class" listed as a field in Player Base and that you intend to use this value list to enter a Player ID in some other table--using this list to enter data into player base doesn't make sense as you have the data already entered there and shouldn't enter the same player more than once in that table.

                              • 12. Re: Value Lists and Searches
                                MikeProcopio

                                I lost my head there dor a second. OK so the Event Participation table is where I'll ise it, so I will go ahead and put the global fields in there and yes the class is in the player base. So I wll go ahead and get that done

                                • 13. Re: Value Lists and Searches
                                  MikeProcopio

                                  Ok here is a question, with the global field it just sits there I put the range in on them one in the first and the other in the second? I know that it is one constant value. THat is all I need to do with that?

                                  • 14. Re: Value Lists and Searches
                                    philmodjunk

                                    It's all you have to do. This becomes a question of interface design. You can use edit boxes, drop down lists and you can also include field validations or script triggers to make entering a valid date range easier and prevent errors such as year 1 = 2012 and year 2 = 2009--but that will depend on what best works for your layout design.

                                    The only gotcha we'll need to watch for is whether the value list appears quickly enough. If you experience long delays waiting for the list of values to appear, we may need to use that custom function I mentioned earlier to get a relationship with faster peformance.

                                    1 2 3 4 Previous Next