3 Replies Latest reply on Apr 3, 2014 8:12 AM by philmodjunk

    How to auto populate a company and contact on an order ticket.

    Barry_1

      Title

      How to auto populate a company and contact on an order ticket.

      Post

           I realize there are multiple issues below. If someone could help me identify where to start it would be greatly appreciated.

            

           I am trying to build a database to handle the orders for a company that makes signs.

            

           Each order may only have one company and one contact within that company.

           A company may have multiple orders.

           A company may have multiple contacts.

            

           I am using a pop-over window on the main order ticket layout so that all of the company/contact info can be entered but doesn't take up a lot of the screen space.

           I have the company name and contact name fields set up as drop-down lists that auto complete. However, I can't get the other information (address, phone, email etc.) to auto populate based on what is selected from those lists. I suspect it is because whenever I select "Company A" (from the field within the pop-over on the main order layout) it is creating a new record in my customers (companies) table with the same name. I don't know how to fix this.

           I should say that I need to be able to create new records in the company table with this field when we have a completely new customer.

      TableRelationship2.jpg

        • 1. Re: How to auto populate a company and contact on an order ticket.
          philmodjunk

               Because your relationship matches by company ID, you can't use a drop down list on the company name field to look up data from the Customers table. You would need to set up a drop down list of company ID's and names on the companyID field.

               I see fields defined in Orders for Company Name, Company Phone, etc. that correspond to fields of the same name in Customers. These can be set up with looked up value field options to copy over the data from Customers if you want to preserve the info that was current at the time you created the order. If you only want to see the most up to date info for a given customer, you can just add those fields from Customers and not use these fields in Orders at all. (Many businesses find it good practice to preserve the info that was present at the time the order was created.)

               To select a contact record will require a new relationship from orders to Contacts and a conditional value list of contacts so that selecting a company results in a value list of just the contacts linked to the selected company.

               

                    I should say that I need to be able to create new records in the company table with this field when we have a completely new customer.

               That won't really be practical from the current setup as I have described it to this point, but there are a number of ways to add new customer and new contact data and one possible variation of the above method does allow entering at least a new name under most circumstances.

                

          • 2. Re: How to auto populate a company and contact on an order ticket.
            Barry_1

                 Thank you. I'm sorry for the late reply.

                 

            To select a contact record will require a new relationship from orders to Contacts and a conditional value list of contacts so that selecting a company results in a value list of just the contacts linked to the selected company.

                 Could someone point me to example of a setup like this?

            • 3. Re: How to auto populate a company and contact on an order ticket.
              philmodjunk

                   Here's some links on conditional value lists. There's a lot here but you probably only need click one or two of the first three links.

                   There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

                   The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

                   Forum Tutorial: Custom Value List?

                   Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                   Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                   Hierarchical Conditional Value lists: Conditional Value List Question

                   Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

                   Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.