12 Replies Latest reply on Jul 14, 2013 2:12 AM by PeterMontague






           I want to relate my sales table to my contacts table. I have a relationship based on sales::contact_id->customers::contact_id.

           I set up a related value list which I can see as a pop up menu that shows me a list of customers' names. But I'd like to be able to start typing the customer's name and then to see a list of possible names show up as I type. I had set this up as auto complete. But the rest of the customers' details aren't showing up.

           Someone mentioned that I use portal filtering. But I don't use a portal for me customer as I only have one customer per sales record. What should I do?

        • 1. Re: Contacts

               #1. If the relationship is via the contactid field, you have to be setting the customers::contact_id field to have the relationship connect.  Simply typing or selecting the name will only work if the tables are related by the name field (a bad idea).

               Have your value list based on contactid, use a second field of customer name, and select the box saying "show only values from second field"

               In that way, you are selecting the name but actually setting the contact_id.

               Take care that you are not actually changing the contact_id on the record, whether you be in find mode, or using a global field...don't mess with your primary key field.

          • 2. Re: Contacts

                 Thanks for your suggestion Ninja. Where are you based?

                 I have my sales layout set up like that. It is nice the way I can open a pop up list and choose my customer from that list. But I would hope that I will have thousands of customers and contacts. So I would like to have a way of typing in the customer's name and then have a list of contact ids to choose from narrowing down the more letters I type.

            • 3. Re: Contacts

                   See this demo file for an auto-complete drop down list that results in the ID being entered and used to link records:

                   FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                   Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7


              • 4. Re: Contacts

                     Note the subtle change in terminology...it makes a big difference.

                     You are asking for a "Pop Up"

                     We are talking about a "Drop Down"

                     They are two different things and they behave differently. 

                     What you are asking for is done on a "Drop Down"...this may be the only thing you have left to change (& the "autocomplete" checkbox)

                • 5. Re: Contacts

                       Thanks for that Phil. I'll play with that database later.

                       This is the way I set up the list. I can't check the autocomplete checkbox. It is not working for me. Can you see any mistakes?


                  • 6. Re: Contacts


                         Check the value list in the demo file. It's not set up that way. It uses a text field and the value list solely references name. The ID number is copied over via a looked up value field option and a script is in place to handle instances where there are two individuals with the same name.

                    • 7. Re: Contacts

                           I've just implemented that method Phil. Works well. Genius as ever. Thanks also Ninja.

                      • 8. Re: Contacts

                             I thought it was working well. But I've put it into practice with a different project I'm working on. I want to monitor what students are doing in their research studies. Students often do similar topics of studies. I want to be able to monitor which ones come up the most. I had a drop down list of all of the topics. But I've found that when I choose a previously done topic from the drop down list it creates a new research study id. I want it to use the previous id. Is this not working for me because I don't have a join table?

                        • 9. Re: Contacts

                               Can't tell from what you have described so far about this specific attempt to use this method.

                          • 10. Re: Contacts

                                 I'm using the same pricinipal in my research study scenario as in my contacts. So I'll just explain my contacts scenario in more detail to avoid confusion.

                                 Well It works the exact same way as the contacts scenario. I have a related table of contacts. I have a dropdown list of contacts using only related values from the contacts table. I also have a fk_serial field in my invoices layout. When I type in a contact's name that I have entered before I am currently able to see the contact's name suggested as I type. If that is the contact I want to use I can select that contact by pressing the return key. But instead of seeing a previously used fk_serial number I see a new one being generated. 

                                 I've imported your scripts from your simple name lookup demo. When I type in the name of a previously used contact it doesn't take me to a new layout to choose which one I want. It simply gives the new contact a new fk_serial.

                            • 11. Re: Contacts

                                   Sounds like you need to take a closer look at the needed relationships. The Name LookUp demo uses two relationships to the table storing the names and generating IDs. One relationship matches by name fields and this is used to copy over the corresponding ID number. The script uses the name matching of this ancillary relationship to count the number of matching records, IF there are more than 1 match, it pulls them up in a list with additional info supplied and asks the user to select one.

                                   But if the relationship is not correctly set up, the script detect cases where an existing record exists and then it will automatically create a new record.

                              • 12. Re: Contacts

                                     Thanks Phil.

                                     Thats working now.