11 Replies Latest reply on Jun 9, 2009 10:54 AM by philmodjunk

    Possible to filter a Value List from entries in another database?

    WorkingOnIt

      Title

      Possible to filter a Value List from entries in another database?

      Post

      Hi, I have two databases. One called "Titles" and one called "Contacts". My Contacts database will contain all company contacts for everything. Each Contact has one or more "contact type" deemed by check boxes.

       

      In my Titles database I want to create a drop-down using a value list created by all the names in the Contacts database. That is working really well. BUT I want to Filter or Sort (not sure if I'm using the right terminology) the names in the Contacts database based on their "contact type" check box. For example, in the Titles database I have a field called "Author". I need to ONLY bring up names that have the "Author" contact type checked.

       

      This is *like* a conditional value list, but I can't seem to get it to work like one.

       

      I am thinking that I may need to write a script that does something like performing the "find" function on the other database before providing the values for the value list... but I'm not sure how the hell to go about it. Thoughts on this greatly appreciated! (Or if this is already answered elsewhere in the forum please point away, I couldn't seem to find anything like it but maybe I just don't have the right search term).

       

      Cheers everyone!

       

        • 1. Re: Possible to filter a Value List from entries in another database?
          Orlando
            

          Hi WorkingOnIt and welcome to the forum.

           

          Sounds like the best way to do this is to setup a relationship between Titles and Contacts based on the Contact Type field on both sides, and then displaying the results in a portal on the Titles record so you can see the list when you flip through the records.

           

          To do this you first need to create a relationship between the two tables, go into Define Database and then under the Relationships tab create a link between your table occurrences for Titles and Contacts from 'contact type' to 'contact type'. and then close Define Database and go to your Titles layout.

           

          Go into Layout Mode and using the Portal Tool, draw an area on the layout you want the list of contact records to be displayed and the Portal setup dialog will display, now for the option of 'Show related records from:' select the Contacts table occurrence you linked to Titles and then tick the option for 'Show vertical scroll bar'

           

          And now go back into Browse Mode and the portal should list all the contacts that match the current Title.

           

          You can then add a button to the row and assign a script or Go To Related Record script step to navigate the user to the selected Contact record from the portal.

           

          I hope this helps, and let me know if anything needs further explanation. 

          • 2. Re: Possible to filter a Value List from entries in another database?
            philmodjunk
              

            Using a portal as a value list as Orlando suggests is often the best idea. It does take up more space on your layout, however.

             

            You can set up your value list just to list "author" contacts if you want.

             

            Define a calculation field in contacts, cAuthorKey.

             

            Define it's calculation to return text with this expression: "Author"

             

            Include the quotes.

            Now create a relationship linking Titles to Contacts that match Titles::cAuthorKey to Contacts::Author. Name this new Table Occurrence "AuthorContacts".

             

            Define your value list to specify the field: Contacts::Name and Click the "Include only related values from" radio button and select AuthorContacts from the drop down menu.

             

            Note, if you use a text field in Titles in place of the calculation field, you can format it with a value list and dynamically filter your value list to pull up a variety of different name groups from contacts.

            • 3. Re: Possible to filter a Value List from entries in another database?
              WorkingOnIt
                

              Hi PhilModJunk and Orlando, it's great to be a member of the forum.

               

              Thanks so much for your answers. Phil, I gave your method a go first. I ran into the problem that my "Author" field is embedded into a Checkbox Set so I couldn't seem to make a direct relationship to "Contacts::Author" as the field is actually called "Contact Type"... unless I am missing something of course.

               

              Also, if I am making a relationship between "Titles::cAuthorKey to Contacts::Author" does the calculation field need to be in the "Titles" database?

               

              Any thoughts on this? Thank you!

               

               

              • 4. Re: Possible to filter a Value List from entries in another database?
                philmodjunk
                  
                WorkingOnIt wrote:

                Hi PhilModJunk and Orlando, it's great to be a member of the forum.

                 

                Thanks so much for your answers. Phil, I gave your method a go first. I ran into the problem that my "Author" field is embedded into a Checkbox Set so I couldn't seem to make a direct relationship to "Contacts::Author" as the field is actually called "Contact Type"... unless I am missing something of course.

                Use contacts::Contact Type instead of Contacts::Author. I didn't know the name of the field you used or missed and substituted my own name for the field.

                 

                Also, if I am making a relationship between "Titles::cAuthorKey to Contacts::Author" does the calculation field need to be in the "Titles" database?

                Yes, just make it to Contacts::Contact Type instead.

                 

                 

                • 5. Re: Possible to filter a Value List from entries in another database?
                  WorkingOnIt
                     Thanks, this worked. Brilliant!
                  • 6. Re: Possible to filter a Value List from entries in another database?
                    WorkingOnIt
                      

                    Hi Orlando,

                     

                    Thanks for your reply also - I am going to use this for another piece of my database - really appreciate your assistance! :)

                    • 7. Re: Possible to filter a Value List from entries in another database?
                      WorkingOnIt
                        

                      I am having just one issue :(

                       

                      My dropdown in the "Titles" database that draws from the value list in "Contacts" database will only displaying a maximum of 6 entries. The dropdown scroll is working on all my custom value lists, but not my value lists that point to the alternative database. Any reason why the scroll bar would not display? There are definitely more than 6 items in the value list.

                       

                      Thanks for your continued assistance.

                      • 8. Re: Possible to filter a Value List from entries in another database?
                        WorkingOnIt
                           Hi, I wanted to report that I resolved the problem that I was having by making "contacts" into another table within the same database - rather than a separate database. This seemed to clear up the problem of not all of the entries displaying... if anyone can work out why then they'll get an extra-special kudos from me, but I am happy with this fix for the moment.
                        • 9. Re: Possible to filter a Value List from entries in another database?
                          philmodjunk
                            

                          WorkingOnIt wrote:
                          Hi, I wanted to report that I resolved the problem that I was having by making "contacts" into another table within the same database - rather than a separate database. This seemed to clear up the problem of not all of the entries displaying... if anyone can work out why then they'll get an extra-special kudos from me, but I am happy with this fix for the moment.

                           

                          I'd take a very careful look at all the details of the relationships and field definitions involved. Tables from External files and tables from the same file should behave exactly the same if their data, field definitions and relationships are set up exactly the same.

                           

                          Keeping all your tables in one file is usually a very good idea.


                          • 10. Re: Possible to filter a Value List from entries in another database?
                            anuragdeshpande
                              

                             

                            can u tell me that how can i filter the drop down list

                            • 11. Re: Possible to filter a Value List from entries in another database?
                              philmodjunk
                                

                              There are two ways to filter a drop down list. One method is simple to set up but rigid and only useful for limited situations. The other approach requires a properly defined relationship and table occurrence references in order to work.

                               

                              Method 1:

                               

                              If you just need to select a subset of all the values in a specific table and it's always the same subset, you can set up a calculation field as the value source for your relationship. Let's call your value field "Values" and you have a second field called "FirstName". You want all the data in "Values" to appear in your value list except values from records where FirstName is "George". Define a calculation field, FilteredValues, as If(FirstName = "George", Values, ""). Now define your value list to specify FilteredValues as the field from which your value list gets its values.

                               

                              Method 2:

                               

                              You need two tables, Table1 and Table2. Table1 will be the table where we want to place our value list formatted field and Table2 will be the source of the filtered values. The relationship between the two tables will control how the values are filtered. In our example, we'll show a value list that filters a list of plant names based on the specified color.

                               

                              Define the following fields in each table:

                              Table 1:

                              Color (Text)

                              Plant (Text)

                               

                              Table 2:

                              Color (Text)

                              Plant (Text)

                               

                              Define a relationship: Table1::Color--=--Table2::Color

                               

                              Enter data into table2:

                              Color      Plant

                              Red        Strawberry

                              Red        Raspberry

                              Blue       BlueBerry

                               

                              Specify a value list:

                              In Manage | Value Lists, create a new value list, PlantsByColor.

                              Specify Table2:: Plant as the field from which to draw the values.

                              Click the "only include related values starting from..." radio button and select Table1 from the drop down list.

                               

                              Now switch to your Table 1 layout, and Format Table1:: Plant to use the PlantsByColor value list.

                              Switch to browse mode and test your value list by first entering either "red" or "blue" in Table1::color and watch how the values in the value list for Table1:: Plant change accordingly.