12 Replies Latest reply on Nov 10, 2009 7:13 AM by ninja

    Auto-complete based on multiple fields (Newbie)

    Keystone

      Title

      Auto-complete based on multiple fields (Newbie)

      Post

      As a business owner who's using FMP to build a database for an upstart staffing company, this forum has been invaluable. I have the "Missing Manual", but don't typically find the specific solutions I'm looking for (or have the time). I appreciate your help/responses.

       

      My newest issue is that I have 100's of records to enter, and I'd like to use the Auto-complete function to fill out several fields in a layout.

       

      Example, when I enter a value in "Company Name" field, it automatically fills in Address, Main Number, Website, etc. based on previous entries.

       

      BTW, I'm using FMP 10. 

       

      Once again, thanks in advance for the help.

       

        • 1. Re: Auto-complete based on multiple fields (Newbie)
          ninja
            

          Howdy Keystone,

           

          Two thoughts to be taken in order:

           

          1. Why would you need to enter the same data multiple times in a relational database?  Your structure raises questions on this item alone.  Perhaps if the structure were improved this would become a non-issue?

           

          2. Using the duplicate record instead of new record function may help...it will duplicate the data in the first record with company, address, etc...and then you'll just adjust the details...but see #1 first.

           

          HTH

          • 2. Re: Auto-complete based on multiple fields (Newbie)
            philmodjunk
              

            To expand on Ninja's good advice:

             

            If you store the customer data in a separate related table, you can simply place the contact info fields from that table on your layout. When you select a customer, the related fields will then automatically show the data from the customer table. Should you ever need to update a customer's data, all the contact fields in all the records that link to that customer automatically update.

             

            Create a customer table with all needed contact fields.

            Add an auto-entered serial number field: CustomerID

            Add a CustomerID field to your Main table.

             

            Define a relationship linking the two tables by CustomerID

             

            Now, simply add the fields from the Customer table to any of your Main Table based layouts that need to show customer ID. If you add the new Main Table::CustomerID field to it and format it with a value list listing both customer ID and Customer name values, you can pick a customer from the list and see all the relevant contact info appear.

            • 3. Re: Auto-complete based on multiple fields (Newbie)
              Keystone
                

              Thanks for the feedback folks. It would seem that I'd need to re-design the DB I have now in order to develop a customer table.

              I know the table set up I have right now isn't as effective as it could be, but I was hoping that there's be a simple, ad-hoc way to auto-fill the fields. Maybe not.

               

              The layout that we have for "Clients" include multiple fields based on other criteria, specific to that individual client.

               

              If I use the 'duplicate record' function, it would take more time to adjust the details/fields than just having the Auto-complete function fill out the address details (if that makes sense)? 

              • 4. Re: Auto-complete based on multiple fields (Newbie)
                ninja
                  

                Howdy Keystone,

                 

                Yes, sometimes the duplicate record function is nice, sometimes it's the wrong tool.  In your case it would depend on the amount of "details" which I didn't know.

                 

                My guess is that you're logging contacts or transactions with each customer.  As Phil expanded, a customer table separate from the transactions table will ease your data entry considerably and still leave you with everything you need.  In the transaction, you would select a customer and then view the related data without moving or copying the related data.

                 

                If you describe briefly what you're trying to accomplish, perhaps we can help you get started with a more appropriate table structure...?...

                • 5. Re: Auto-complete based on multiple fields (Newbie)
                  Keystone
                    

                  Thanks Ninja.

                   

                  Basically I want my Client layout to do this:

                   

                  When I 'OnObjectExit' the "Company Name" field (i.e. tab to the next field), the related address, website, and main phone number fields will automatically be populated.

                   

                  I figured a simple script would do it? I just need a simple solution right now so I can more easily enter my client contact info.

                   

                  Thanks! 

                  • 6. Re: Auto-complete based on multiple fields (Newbie)
                    ninja
                      

                    Okay,

                    Let me try to be more clear:


                     

                    If you describe briefly the business task you're trying to accomplish by using this database, perhaps we can help you get started with a more appropriate table structure...?...


                    In this way perhaps we can help you enter the data more easily now (by only entering it only once rather than multiple times per company) and end you up with an easier-to-maintain-and-use Database as your company moves forward and thrives.

                    If you'd like, we can walk you through building a Dbase structured to fit your current and future use of it.

                     

                    Are you interested?

                    • 7. Re: Auto-complete based on multiple fields (Newbie)
                      philmodjunk
                        

                      Keystone,

                       

                      The reason we are reluctant to tell you how to do what you want (it can be done) is that it appears we would be helping you get entangled in a major mess that should be avoided.

                      • 8. Re: Auto-complete based on multiple fields (Newbie)
                        Keystone
                          

                        Ninja and Phil,

                         

                        Sorry for the delay in getting back to you, actual "work" got in the way.

                         

                        I really appreciate the feedback, I guess I'm reluctant to have to un-do everything I've done so far, as I have quite a few records already entered... but I'm certainly not going to turn down your advice/help if you're still willing to help me.

                         

                        As far as the Business Task: We have a technical recruiting company that focuses on two areas, Engineering and IT.

                         

                        We need FMP to help us organize 3 main areas/layouts of records:

                         

                        1) Clients (companies/managers we're selling our services to)

                        2) Engineering Resumes (layout has multiple tabs/fields)

                        3) IT Resumes (same)

                         

                        *Each layout has tabs for contact info, resumes, maps, contracts, notes, etc.

                         

                        Right now I just have 3 tables for the 3 layouts (plus one main page layout). You can negotiate between the layouts by buttons on each layout.

                         

                        So... I was clearly on the wrong track by setting up 3 separate tables? It's my first crack at any type of DB, so what I did was create three stand-alone tables, with the field names labelled with names such as:

                         

                        "last_name_IT" (in the IT table)

                        "last_name_eng" (in the Eng table)

                        "last_name_clients" (in the Clients table)

                         

                        ..and so on for all the fields.

                         

                        The IT and Engineering layouts/fields are essentially identical, however the Client layout is unique (a number of different fields)

                         

                        Probably the wrong way to go about building a relational DB, but like I said, it's my first crack at it.

                         

                        My thoughts were that the 3 layouts are fairly autonomous, with no real need to link the info in one to another. All I really needed was a way to auto-complete the client info, but apparently I'm going about it the wrong way?

                         

                        Any help would be appreciated, or am I beyond help?

                         

                        Thanks again guys. 

                         

                        • 9. Re: Auto-complete based on multiple fields (Newbie)
                          ninja
                            

                          Howdy Keystone,

                           

                          You're nowhere near beyond help.  There's certain trigger phrases that we hear over and over that tend to suggest a start in the wrong direction...and we heard one.  No big deal, let's figure out together what the best way forward is.  The goal is always a stable, secure database that does what you need it to do as easily as possible.

                           

                          Autocomplete of company info, presupposes that you enter it more than once:

                           

                          Is this more than one client at the same company?

                          More than one placement of an Engineering Resume at the same company?

                          A placement of a person at a client where you want the resume record to autocomplete with Client placement info?

                          Engineers and IT folks from the same company looking for another job?

                          All or some of the above?

                           

                          What is the situation you are in when you want to autocomplete the company name?

                          (think it through and figure out ALL or at least MOST situations you might be in)

                          • 10. Re: Auto-complete based on multiple fields (Newbie)
                            philmodjunk
                              

                            Until last post, you only described one table. That one table should probably represent more than one with relational links.

                             

                            Here's a couple of examples why the "duplicate all that data each time" can be a recipe for disaster:

                             

                            You create and enter 150 records that all contain

                             

                            "John Smith"

                             

                            Only to discover that John Smith is really "Jon Schmidt" and your original record was in error. You now have to find all instances of "John Smith" and fix all of them identically. If you had a single record containing John Smith's contact information,  you only have to change that one record and all the other records that link to it to show John's Jon's name will update automatically. Much simpler, right?

                             

                            Don't toss out your current data, it may be possible to make strategic use of Import Records to split that data up into several tables and eliminate duplicates at the same time.

                             

                            It's important to understand the difference between a table and a layout. A table is where filemaker stores the actual data. A layout is what you create to display data from one or more tables.

                             

                            There's still a lot left unsaid about your DB, but going from what you've described so far...

                             

                            Create one Contacts table.

                            Define text fields for contact information such as name, address, phone, email, etc.

                            Include a text field to identify the type of person, Client, Staff,...Etc. (possibly including IT Staff and Engineering Staff)

                            Create an auto-entered serial number field for a Primary Key, Name it ContactID or some such name.

                             

                            Enter the contact data for all people regardless of what they are in this one table.

                            Now create a Resume table.

                            Define a ContactID field in this table

                            Add all the fields you need for an individual's resume. If IT and Engineering staff need mostly the same fields, put them in the same table.

                             

                            You'll probably need some kind of ProjectID to document one Project created for a specific client.

                            Not knowing your business, I can't really give you all the fields here and leave that up to you but you'll definitely need a ContactID field here as well to identify the client.

                             

                            You'll then build relationships to link these tables:

                             

                            Contacts::ContactID = Resumes::ContactID

                            Projects::ContactID = Contacts::ContactID

                             

                            Before you're done, you'll need additional tables and relationships, but that'll get you started on what Ninja and I are suggesting.

                             

                            • 11. Re: Auto-complete based on multiple fields (Newbie)
                              Keystone
                                

                              Thanks Ninja,

                               

                              To answer your questions:

                               

                              Is this more than one client at the same company? YES, we would have multiple contacts/managers that we deal with at any given company

                               

                              More than one placement of an Engineering Resume at the same company? YES, we would place more than one person (ideally) with a client.

                               

                              A placement of a person at a client where you want the resume record to autocomplete with Client placement info? NO, this wouldn't be required.

                               

                              Engineers and IT folks from the same company looking for another job? PERHAPS, but we don't need this functionality. Each record in both our ENGINEERING and IT layouts/tables would have unique contact information with an independent resume file (I currently have a container field for a 'Word' or PDF version, plus a searchable TEXT field, where we cut and paste the resume text. That's what we use to run a search for candidates for our clients, by entering key words. 

                               

                              What is the situation you are in when you want to autocomplete the company name?

                              (think it through and figure out ALL or at least MOST situations you might be in) 

                               

                              OK, the user would be on the main CLIENT contact layout page, where they would enter the COMPANY NAME (I've already set this field to autocomplete based on previous records). 

                              I've thought it through, and this is the only area where I would need autocomplete to kick in. Once the CLIENT name is selected, I need an autocomplete on the following fields:

                               

                              Address, City, Postal Code, website, main phone, fax number.

                               

                               

                              • 12. Re: Auto-complete based on multiple fields (Newbie)
                                ninja
                                  

                                Howdy Keystone,

                                 

                                I agree with Phil regarding making a more functional Dbase for the task, but as I review your OP, that isn't what you asked for.

                                 

                                If you were to simply make a Company table, with one record per company, and list in it the pertinent company data (phone, add, fax, etc. ) then link it via Resume::CompanyID = Company::CompanyID you could then put the company data fields on your current layout.

                                 

                                Since the resume table would be linked to the company table, you would simply enter the companyID on your layout and the related fields would update.

                                 

                                For utility, you should then define a value list "Companies" based on Campany::CompanyID showing only values from a second field "Company Name".  Now you can choose the company name from a dropdown list, and your choice will actually input the proper CompanyID into the CompanyID field.

                                 

                                Not sure how much detail you need, but this approach directly addresses your OP and saves you a bunch of repetitive data entry and storage.