6 Replies Latest reply on Nov 2, 2015 8:06 AM by siplus

    What is the most efficient way to find 1 name out of thousands for a record?

    weedonpaul

      I have a layout called contacts that links to a table called contacts there is an other table called companies that has a relationship with contacts. When entering a new contact into the layout, I currently search through the list of companies to find on that exists or create a new one. I do this with a drop down list that lists company ID's but displays their names. However this is a pain in the arse as there are thousands of company names.

       

      I did try auto complete but that just tries to create a duplicate record in companies

       

      Is there a better way to do this?

        • 1. Re: What is the most efficient way to find 1 name out of thousands for a record?
          wimdecorte

          It's a little confusing how you describe looking for a company when entering a contact... are you at the place in the workflow where you assign that new contact to a existing company or create a new one?

           

          (In that case you probably also need the same type of search to avoid duplicate people, besides companies?)

           

          There's any number of ways to do this.  Instead of showing a value list of companies, you could pop up a new window (list view on companies, no records in the found set) and let the user type in the name, which would automatically trigger a "quick find" type of search.  The user can then pick a resulting company or decide to create a new one.

           

          An alternative is to use ExecuteSQL() to search companies after the user enters a name...

          1 of 1 people found this helpful
          • 2. Re: What is the most efficient way to find 1 name out of thousands for a record?
            weedonpaul

            I like that idea, didn't think to do that. I will create a pop up window

             

            you are a star

            • 3. Re: What is the most efficient way to find 1 name out of thousands for a record?
              siplus

              First of all, you must consider whether it's possible for the same person to work in more than one company.

               

              Next, I don't know if you store the contact's birthday or SSN; usually a FirstName & LastName & GetAsNumber(BirthDate) is a decent candidate for duplicate checking.

               

              On new contact you could store the First and Last Names in 2 globals, hit a "CHECK" button and have a popover pop up if there's related data in the contacts table. (build a relationship and show a portal).

              1 of 1 people found this helpful
              • 4. Re: What is the most efficient way to find 1 name out of thousands for a record?
                weedonpaul

                I avoid duplications with contact via email addresses, It's not perfect but most of the database is populated from press lists down loaded from event web sites, the manual entered names are from business cards, etc so information is not that personal.

                 

                we do get multiple contacts from a company so for that reason we kept them separate ( so that a change in company details would filter down to all contacts in that company).

                 

                It's not really possible for a person to work for more than one company in this line of work, unless they freelance in that case they would be their own company.

                 

                All that said you have given me lots of food for thought so may come back and visit this one again

                 

                Thanks

                • 5. Re: What is the most efficient way to find 1 name out of thousands for a record?
                  weedonpaul

                  I'm probably being dumb here, but struggling with pop up window.

                   

                  I have created a layout to pop up and a button on the contacts layout that runs a script that opens new window , resizes it and goes to the find company layout and enter's find mode. this is where I get stuck

                   

                  I want the user to enter the company name and press a button called find (or press enter) at this point I want one of 2 things to happen

                  1)If finds a company it user has the option of adding it to the contact.

                  2)if nothing found user has the option to create new

                  any ideas?

                  • 6. Re: What is the most efficient way to find 1 name out of thousands for a record?
                    siplus

                    you can move to a Find layout which contains only first and last name plus company , in list format. You do your search there and if Get(foundCount) > 0 you stay on the layout, otherwise you come back and create the new record.

                     

                    The list layout has the names + company defined as a button, if you click on it you do whatever you want as functionality.