11 Replies Latest reply on Jan 31, 2013 1:16 PM by philmodjunk

    Edit Box With Auto-Suggest Functionality (Like Google).

    J.S._1

      Title

      Edit Box With Auto-Suggest Functionality (Like Google).

      Post


           I have a table called Accounts that holds company names and addresses, a table called Contacts that holds names and contact information for people at the companies listed in accounts, and a table called Create Proposal that is pretty self-explanitory.

           What I would like to do is this...

           On the layout for my 'Create Proposals' table I have fields called Customer_Name, Customer_Address_1, Customer_Address_2, Customer_City, Customer_State, Customer_Zip, Contact_Name, Contact_Phone, and Contact_Email. When a user begins to type in the Customer_Name edit box, I would like suggestions based on the field Company_Name in the 'Accounts' table and the field Contact_Name in the 'Contacts' table to pop up. If there is a company called XYZ Company in Accounts and there are two contacts (John Doe and John Smith) assoicated with that company in Contacts, I would like the pop up to suggest something like, "XYZ Company - John Doe," and "XYZ Company - John Smith". When a user selects one of these records I want the initially-mentioend fields to populate.

           Possible? If so, what's the most efficient route to achieving the goal?

        • 1. Re: Edit Box With Auto-Suggest Functionality (Like Google).
          philmodjunk

               Take a look at the Search portals at the bottom of the layout in this demo file: https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

               If you are using Filemaker 12, you can open this file from the file menu to get a copy converted to FileMaker 12 file format.

               While the portals list only a single name field, there is no reason why they could not list both a company and a contact name in each portal row.

          • 2. Re: Edit Box With Auto-Suggest Functionality (Like Google).
            J.S._1

                 Okay, so based on the info in the file (thanks for that), I have come up with:

                 Create Proposal::Customer_Name = ( Accounts::Company_Name & Contacts::Contact_First & " " & Contacts::Contact_Last ; Length (Create Proposal::Customer_Name ) )

                 However, when I try to click 'Ok' I get this:

                 There are more “)” than there are “(” or there are more “(” than there are “)”.

            • 3. Re: Edit Box With Auto-Suggest Functionality (Like Google).
              philmodjunk

                   That error means exactly what it says. Check the entire expression to find and correct for the unbalanced parenthesis pair.

                   The syntax you have pasted here, however, is not correct and does not match the example file. It should be:

                   Create Proposal::Customer_Name = Left ( Contacts::Contact_First & " " & Contacts::Contact_Last ; Length (Create Proposal::Customer_Name ) )

                   If you want to match the text in Proposal::Customer_Name to the company name field, you'll need to use:

                   Create Proposal::Customer_Name = Left ( Contacts::Contact_First & " " & Contacts::Contact_Last ; Length (Create Proposal::Customer_Name ) ) OR
                   Create Proposal::Customer_Name = Left ( Accounts::Company_Name ; Length (Create Proposal::Customer_Name ) )

                   But the fact that your two name fields (as I expected) are in two different tables will complicate this process. Assuming that your portal refers to Contacts, you may need to add in a new occurrence of Accounts and refer to fields from it in your portal filter expression if you find that your search text is successfully matching by contact name, but not by company name.

                   If that isn't enough to get it to work, you'll need to document the relationships you are using.

              • 4. Re: Edit Box With Auto-Suggest Functionality (Like Google).
                J.S._1

                     I obviously ran through that a bit too fast! Yikes!

                      

                     Anyhow, my relationships are:

                     Create Proposal::Customer_Name <-> Accounts::Company_Name

                     Contacts::Contact_ID <-> Accounts::Company_ID

                • 5. Re: Edit Box With Auto-Suggest Functionality (Like Google).
                  philmodjunk

                       So your layout is based on Create Proposal.

                       But on what table occurrence is your search portal based?

                       At least to start, you'll want a relationship from Create Proposal to AllContacts like this:

                       Create Proposal::Customer_Name X AllCONTACTS::Any Field

                       and then one more relationship:

                       AllCONTACTS::Contact_ID = AllContactsACCOUNTS::Company_ID

                       and your portal filter expression will need to refer to AllContacts and AllContactsACCOUNTS instead of Contacts and Accounts.

                       AllCONTACTS and AllContactsACCOUNTS are new occurences of your existing Contacts and Accounts tables.

                       Your field names, BTW, will be confusing, especially if another developer works with your file or when you return to this file after some time has passed.

                       Normally, you would use field names such as:

                       Contacts::Contact_ID <-> Accounts::Contact_ID

                       The ID number that identifies a contact would not normally be the same number that identifies a company, given that your examples show that you can have more than one contact for a given company.

                  • 6. Re: Edit Box With Auto-Suggest Functionality (Like Google).
                    J.S._1

                         I just downgraded from "following along" to "what?".

                         I don't have any special table for search functionality. I guess I haven't gotten that far yet.

                         Also, it seems I need to create another table called All Contacts. Correct? What is that supposed to contain?

                         When you say "Any Field" in the relationship, do you literally mean I can select any field, or that there is a way for the relationship to automatically look at any field?

                         What do you mean by portal?

                         How do I correct the field names issue? I definitely don't want to create issues for future developers!

                          

                    • 7. Re: Edit Box With Auto-Suggest Functionality (Like Google).
                      philmodjunk
                           

                                it seems I need to create another table called All Contacts. Correct?

                           This is not correct and you do not need to add any new tables to your file. You need new table occurrences and that is something different.

                           Table occurrences are the "boxes" found in Manage | database | Relationships. While a FileMaker file starts out with one Table Occurrence box for each table you create on the tables tab, these boxes are not tables, they are a way to refer to those tables and you can add more occurrences with different names that refer to the same tables so that you can define additional relationships.

                           In Manage | Database | relationships, make a new table occurrence of contacts by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.

                           We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                           To learn more about table occurrences and the critical role they play in FileMaker Database Systems: Tutorial: What are Table Occurrences?

                           

                                When you say "Any Field" in the relationship, do you literally mean I can select any field, or that there is a way for the relationship to automatically look at any field?

                           Yes I mean that you can select any field in the table.

                           

                                How do I correct the field names issue?

                           You can rename fields simply by selecting the field in Manage | Database | Fields, typing in a new name and clicking "Change". But please read my post very carefully.

                           You indicated that you were matching a field named ContactID to a field named CompanyID in a relationship. I can't tell from here which field is incorrectly named or if you might have the relationship set up incorrectly. You'll need to check the two fields and what data is being entered into them to determine what correction needs to be made.

                           

                                What do you mean by portal?

                           I'm not sure what you are asking here. You've already posted an expression to use as a portal filter, the demo file shows 3 search portals across the bottom of the layout...

                      • 8. Re: Edit Box With Auto-Suggest Functionality (Like Google).
                        J.S._1

                             Okay, so this is what I've got now...

                              

                             These are my relationships:

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                             Here are a look at my table configurations:

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                              

                        • 9. Re: Edit Box With Auto-Suggest Functionality (Like Google).
                          philmodjunk

                               Double click the line between AllContacts and Create Proposal and change the = operator to the X operator.

                               Also, examine the design of the demo file carefully. It uses a special combation of match fields and a script to ensure that the records listed in the search portal update automatically with each keystroke in the search field above the portal.

                          • 10. Re: Edit Box With Auto-Suggest Functionality (Like Google).
                            J.S._1

                                 Are you referring to the scripts named 'Update Search' and 'Description Search'?

                                 Update Search is pretty self-explanitory, but I am a little more confused with Description Search, especially with needing to search in two other tables.

                            • 11. Re: Edit Box With Auto-Suggest Functionality (Like Google).
                              philmodjunk

                                   "Update Search" is the scripted part of the update process. But notice how the portal's relationship specifies the global search field and "ProdRefresh" as match fields in this relationship. And ProdRefresh has this auto-enter calculation: Invoice::gSearchField.

                                   While you can use any pair of match fields for a X relationship to see all records in the portal's table, specifying these fields as match fields and the auto-enter calc plus the one line "update search" script are what make for a portal that updates with every keystroke in the search field without using Refresh Window [Flush Cached Join results] -- a step that can produce undesirable delays when it forces the entire window to refresh.

                                   "Description Search" is not used with the search portals. It's used with the auto-complete enabled drop down lists at the top of the screen. Those scripts have to handle the possibility that text entered/selected in the drop down might match to more than one record--something that's not an issue with the selection portals.