10 Replies Latest reply on Sep 21, 2009 8:34 AM by philmodjunk

    Autocomplete from another table?

    dgtldan

      Title

      Autocomplete from another table?

      Post

      Hello,

       

      I am trying to use data from another table in the same database to autocomplete a field and pull data into the related fields.  Is this possible?

       

      I have a layout called ALL with a field called 'Vendor'.

      I have another layout called Vendor_new with a field called 'Vendor'

      I have a relationship defined between the two 'Vendor' fields and the associated fields that I want to pull data from are getting their respective data from the Vendor-new field transfered to the ALL records.

      What I am unable to do is on the ALL layout begin typing in the 'Vendor' field and have the matched records from the Vendor_new 'Vendor' to auto fill the data.

      I have checked the checkbox for Autocomplete in both 'Vendor' fields in both tables.

       

      I have tried to have this relation on two separate databases as well and I was unable to do it.  What am I doing wrong!? 

       

      TIA.

      --

      Dan 

        • 1. Re: Autocomplete from another table?
          philmodjunk
            

          Minor correction: You can have any number of layouts that all refer to the same table. Thus refering to layouts in as though they are tables in your original post could result in confusion. I get it that in this case each of the two layouts refers to different related tables.

           

          You have two basic options for your auto-complete:

           

          1) Simply place the other fields from the related table on your layout. When you select a value a field that is part of the defined relationship, data in these fields will automatically appear--this will look like the auto-complete effect you want.

           

          2) Sometimes you need physical copies of the data in your table where you are inputting data. (A unit price looked up from a price-list table is one example.) When you physically copy the data, after the fact changes in the related table will not automatically modify the data you see in your current table--that could be a desired or undesired effect. To physically copy the data, open the field options for each such field and specify either a lookup or an calculation on the auto-enter tab for that field.

          • 2. Re: Autocomplete from another table?
            dgtldan
              
            Hello Phil,
             
            I had made the fields that I want the data from become a part of the ALL layout as you can see.  That part works and I have also made their behavior such that they are not modifiable in Browse mode.  This way I can ensure the user will not be able to accidentally change these fields since the objective is to pull them from the other table.  Is this what you were mentioning? 
             
             
             
             
            I had tried the filed options as well:
             
             
            What else am I doing wrong!?
             
            Thanks.
            -d 
            • 3. Re: Autocomplete from another table?
              dgtldan
                

              The first one looks like: 

               

               Vendor VENDOR

              ::Vendor Address

              ::Vendor City 

               

               

              The second one for Lookup for field VENDOR is:

              Starting with table Vendor

              Lookup from related table Vendor_new

              Copy value from field ::VENDOR

               

              Hope this helps.

               

              TIA 

              • 4. Re: Autocomplete from another table?
                philmodjunk
                  

                Unfortunately, I can't see your posted images to tell. :smileysad:

                 

                 

                • 5. Re: Autocomplete from another table?
                  dgtldan
                    

                  The first one looks like: 

                   

                   Vendor VENDOR

                  ::Vendor Address

                  ::Vendor City 

                   

                   

                  The second one for Lookup for field VENDOR is:

                  Starting with table Vendor

                  Lookup from related table Vendor_new

                  Copy value from field ::VENDOR

                   

                  Hope this helps.

                   

                  TIA

                  • 6. Re: Autocomplete from another table?
                    philmodjunk
                      

                    Not really.  You said the "first part works". Don't know what that "first part" is...  Vendor?

                     

                    You don't indicate what relationship links the two tables. That's the part that often keeps this from working. You also need to make sure the right Table Occurrence reference was specified (it should match your Relationship graph) when you added the address and city fields to the layout.

                     

                    Relationship issues will also make the lookups fail. I wouldn't use a lookup in any case for vendor information. I would think you would want changes to the vendor information table to automatically be reflected on all your records.

                     

                     

                    • 7. Re: Autocomplete from another table?
                      dgtldan
                        

                      Sorry if I was not clear about what works.

                       

                      If I enter a name in the Vendor filed that exists in the related table, then the rest of the fields which are associated with the relation will become reflected in the ALL layout.  The link relationship between the tables is the 'VENDOR' field.

                       

                      The ALL layout has the fields as such:

                      Vendor VENDOR --this is the vendor field in the ALL layout and the link relationship between Vendor and Vendor_New table.  

                      ::Vendor Address --this is the Vendor Address from Field/Control Setup section under Display Data from Vendor_New table related field of the same name

                      ::Vendor City --this is the Vendor Address from Field/Control Setup section under Display Data from Vendor_New table related field of the same name

                       

                      Thanks.

                      -d 

                      • 8. Re: Autocomplete from another table?
                        philmodjunk
                          

                        Then what doesn't work? This seems to be a description of everything working the way it should.

                         

                        Note that my original description based the relationships on a Vendor ID number, not the vendor name. Though using the vendor name for your relationships will work, it can set you up for trouble should one of your vendors change their name.

                        • 9. Re: Autocomplete from another table?
                          dgtldan
                            

                          What doesn't work is when I start to type in the name of the vendor in the ALL layout, I don't get the rest of the vendor names which allow me to pick thru them even thou I have clicked the Auto-complete check box in the Field/Control setup.

                           

                          If I switch the the Vendor_New layout, make a new record and start to type, I get the list.  I want to be able to get the same list in the ALL layout via the relationship.  Is this possible or am I asking too much of it?

                           

                          I know once the data is entered in the ALL layout, then the Auto-complete will take over for new records based on previous data, but I don't seem to be able to get the Auto-complete records from the Vendor_New layout in the ALL layout.  I thought since I had a relationship between them then that data will be available.

                           

                          Thanks.

                          -d 

                          • 10. Re: Autocomplete from another table?
                            philmodjunk
                              

                            OK, that clears things up. There's auto-complete--a specific option you select for a specfic field when you format it as a drop down menu and there's what happens to other fields that reference related tables when you select/enter data in a key field on which the relationship is based. We've got the second item working but it's the settings for the drop down that aren't working for you.

                             

                            There are unfortunate limits to when auto-complete will work.

                             

                            Specifically, auto-complete will not work when the field is a number field instead of text. Is this a number field or a text field? (You should check the actual field definition in Manage | Database | Fields to be sure.

                             

                            What is often frustrating is that if your field is a number field that you've set up with a a 2 column value list--with the first, number column hidden, auto-complete should work but it doesn't. This is a limitation built in to filemaker that (in my opinion) makes no sense.