10 Replies Latest reply on Feb 9, 2011 7:36 PM by ericjlindholm

    Filter Values in a Value List

    AAgraphics

      Title

      Filter Values in a Value List

      Post

      Using v10

       

      I have a value list in which the values are pulled from a field on a related table, all of the values are email addresses.  Is there a way to filter what is shown in the value list so that the value list only shows email addresses ending with "@thisname.com"?

        • 1. Re: Filter Values in a Value List
          philmodjunk
            

          In your related table, define a calculation field:

           

          Right ( emailfield ; length ( emailfield ) - Position ( emailfield , "@" , 1 , 1 ))  //tweak this expression depending on whether you want to include or exclude the @

           

          Now include this field as part of your relationship your value list definition uses to filter the value list. You might, for example, add a global field to the parent table and link it to the above calculation. You could then select different domain names for this field to select different groups of email addresses.

          • 2. Re: Filter Values in a Value List
            AAgraphics
              

            I created the field and I entered in the formula for that field:

             

            Right ( Email Address ; Length ( Email Address ) - Position ( Email Address ; "@" ; 1 ; 1 ))

             

            and it is not working as intended.  The result I am looking for is for the value list to only include email addresses ending with "@aaglobalind.com"  Any thoughts?

            • 3. Re: Filter Values in a Value List
              philmodjunk
                

              At what point does it not work? If you enter "aaglobalind.com" in the global field I mention, you should be able to see only email addresses that list with that domain. If you want to enter @aaglobalind.com into the global field, use Right ( Email Address ; Length ( Email Address ) - Position ( Email Address ; "@" ; 1 ; 1 ) + 1 )

               

              The details, however, can trip you up if you're not careful.

               

              Can you set up the above calculation (let's call it cDomain), so that you can see the domain portion of each email address in a table or list view of the related table? (that confirms that the field is calculating correctly.)

               

              Can you set up this relationship?

               

              YourMaintable::gGlobaltextfield = YourRelatedTable::cDomain

               

              Do you know how to set up a value list that uses a relationship to filter values?

              • 4. Re: Filter Values in a Value List
                AAgraphics
                  

                I have the global field and I have the relationship established as you directed

                 

                I have the following in the global field:

                 

                @aaglobalind.com

                 

                I have the formula in the field on the related table:

                 

                Right ( Email Address ; Length ( Email Address ) - Position ( Email Address ; "@" ; 1 ; 1 ) + 1 )

                 

                And what I get in the value list is all of the email addresses shown only as "@domain.com"

                 

                With "domain" as all of the different domain names.  I need the complete email addresses ending with "@aaglobalind.com" and all of the others to be filtered off of the list.  Any suggestions or did I mess up somewhere? 

                • 5. Re: Filter Values in a Value List
                  AAgraphics
                     Oh, and here is an additional monkey wrench in the works.  They want it to show the complete email address for any email address ending in "@aaglobalind.com" or "@kokos.com".  Does this completely change everything?
                  • 6. Re: Filter Values in a Value List
                    philmodjunk
                      

                    No problem on your last post. The method I'm suggesting allows you to select for any specific domain name. With a check format on the global field, you can even select multiple domains.

                     

                    You're almost there. The new calculation serves as a filtering key on the relationship. It's not the field you specify in the value list.

                     

                    Open Manage | Value Lists...

                    Create a new value list or edit an existing one.

                    Specify the email field from your related table as the source of values

                    down at the bottom of the dialog, select the "Include only related values starting from" option. Specify whatever table your relationship links to your table of email addresses. Make sure that your layout where you use this value list refers to this table.

                     

                    Enter the desired domain name into your global field and your menu will show only emails with this domain. To include two different domains, enter both values separated by a carriage return. To make this easy, format your global field with check boxes listing each domain. Click the check box of each domain you want to include in your value list.

                    • 7. Re: Filter Values in a Value List
                      AAgraphics
                         It is showing only "@domainname.com" and it is showing all of the domain names in the value list.  It is basically filtering out everything before the "@" symbol.  Any thoughts?
                      • 8. Re: Filter Values in a Value List
                        philmodjunk
                          

                        This tells me you haven't set up the value list correctly. Check over the followin steps and see where you've done something differently:

                         

                        You have two fields here. I'll call them Addresses::Email ( a text field listing email addresses ) and Addresses::cDomain (a calculation field extracting the @domain name portion of email.)

                         

                        Addresses is the name I'm giving the table occurrence in your relationship graph where you link your main table to your table of email addresses via the global field and cDomain.

                         

                        Use Manage | Value Lists... to edit the value list, click Specify Field and select Addressses as the table and click Email--NOT cDomain--to select it as the source of values for your value list. Now your value list will show email addresses instead of just the domains.

                         

                        You have a layout where you are trying to set this up. This layout refers to a table other than Addresses in your database. Let's call it "MainTable".

                         

                        Down at the bottom of this dialog, make sure you've selected "include only related values starting from MainTable".

                         

                        Now put your global text field on a layout somewheres.

                        Format it with two check box values: @aaglobalind.com and @kokos.com

                        Enter browse mode and click both check boxes.

                        Test your value list. It should now list emails with the above two domain names.

                        • 9. Re: Filter Values in a Value List
                          AAgraphics
                             Now it works.  Thank you very much!  Your summary helped me catch a couple of minor errors.  Thanks again.
                          • 10. Re: Filter Values in a Value List
                            ericjlindholm

                            I have a similar situation but its slightly different.

                            I have a Sound Equipment Table.  It has fields Name, Category, Description.

                            I want to create a values list that contains Names that are in a particular category.

                            For example, I was a values list that only lists Names that are marked as amp racks.

                            I know I could make a separate table for every category of equipment but i would like to be able to easily add new categories.  

                            Thank you.