7 Replies Latest reply on Dec 22, 2011 4:41 AM by johannes

    Select customer records via dropdown box

    johannes

      Title

      Select customer records via dropdown box

      Post

      Hi all,

      I have a probably typical beginner question. This is the situation:

      I have a customer table and a corresponding customer layout which shows all details for a customer. All records have a unique ID.

      I need a drop down box that shows all customer names, narrowed down by what I enter. If I select an entry from the drop down list the corresponding customer details should be displayed on the layout.

      I have experimented with all sorts of combinations of relation, second table occurence, value list etc., to no avail.

      Can someone please point me to the correct solution for this problem?

      Any help will be greatly appreciated.

      Thanks in advance

        • 1. Re: Select customer records via dropdown box
          philmodjunk

          First some basics--these won't get you all the way to what you want but they lay the foundation for a method that does work.

          Define a value list with the "use values from field" option. Specify your ID field for the first field in the value list and the cutomer's name field as the second field.

          With this value list, you can see the customer's name, but selecting them enter's their ID. Problem here is that it won't "auto-complete" as you type in the first letters of their name. A further complication is that more than one customer might have the same name and this value list doesn't allow you to tell which "John Smith", for example, is the one you want to select. You can replace the name field in the value list setup with a calculation field that combines more info such as an address, but you still can't auto-complete.

          Let's start with this approach though to set up the rest of what you want: "If I select an entry from the drop down list the corresponding customer details should be displayed on the layout." We'll come back to that auto-complete issue with a scripted approach that can be implemented in place of this simpler approach after you get everything else working.

          To do the next part, I need more info. On what kind of layout do you want this to happen?

          Is this something, such as an invoice layout where you are selecting a customer for a new invoice?

          You can copy data from the customers table when you make such a selection or you can just link to the info when you make the selection. With a link, any future changes to the customer's record automatically update on this layout. If you copy, the data shown on this layout will not update so you need to determine which method is best for your business procedures. (Many businesses prefer to copy customer data to an invoice so that they have a record of the contact info that was current at the time the invoice was created. ("I see why you didn't get your order sir, It was shipped to your previous address as we had not received any change of address info from you...")

          Either approach is based on the following relationship:

          Customers::CustomerID = OtherTable::CustomerID

          OtherTable::CustomerID is the field you set up with this value list.

          • 2. Re: Select customer records via dropdown box
            johannes

            Hi PhilModjunk,

            Thank you for your reply. I tried your first step and here is what happens:

            The fields are

            - customer::ID (autonumber)

            - customer::Anrede

            - customer::Titel

            - customer::Name

            - customer::Vorname

            - customer::name_vorname (a calculated field concatenating name and vorname)

            1. Make customer::ID a dropdown box that uses a script scr_Kundenauswahl which uses the values customer::ID and customer::name_vorname and includes all values. This displays a list of the existing customers. However, if I select one the ID of the customer record currently being displaed changes to the effect that I now have two records with the same ID.

            2. create another occurence of the customer table: customer2. Link customer2::ID to customer::ID. Create a field labelled ID2 wich uses customer2::ID and is a dropdown box which uses the value list ddb_Kundenauswahl2. This value list uses values customer2::ID and customer2::name_vorname and includes all values. The result is the same as above.

            I have attached a PDF with the sequence of events. I'm not sure whether the PDF will show up. If not I can email it to you.

            I'm sure I have misinterpreted your proposal but I don't see where.

            Thanks for dealing with my problem.

            Best regards

            Hans

             

            • 3. Re: Select customer records via dropdown box
              philmodjunk

              PDF's cannot be attached. Use a Jpg, gif or PNG file for upload to your post.

              And you would not use the ID field for your drop down or you will be changing the existing record instead of looking up data for that customer.

              You'll need to use a different field. NOte that I mentioned a different table entirely (not a different occurrence of the same table) such as a table of invoices.

              Please describe how you want to use this information and I'll respond in more detail.

              • 4. Re: Select customer records via dropdown box
                johannes

                Itz's a test application to try out different techniques. This part is as follows:

                Tables:

                customer

                - ID

                - ID_address

                - Last name

                - First name

                - (calculated) lastname_firstname

                addresses

                - ID

                - ID_customer

                - street

                - city

                - zip

                cars

                - ID

                - ID_customer

                - make

                - model

                - some other details ...

                 

                Layout customer:

                - custpmer ID

                - customer last name

                - customer first name

                 

                Idea is to have a dropdown box or meniu show all customers so I can pick one, the details of which are then displayed on the layout. The addresses and cars part I have working. Only the customer selection is a problem. I could use find mode to pic a customer but as I said I am exploring different techniques. Hence this maybe strange setup.

                Attached is an image of the layout. Don't laugh, it's just trying.  :-)

                 

                Best regards

                Hans

                 

                • 5. Re: Select customer records via dropdown box
                  philmodjunk

                  I gather then that you want to use the drop down to FIND the customer record, not copy or refer to the customer data on a table in another record...

                  Add a global text field to your database, gFindCustomer. In Manage | Database | Fields you can click Options for a selected field to open the Field Options dialog where you can specify global storage for it.

                  Format this field with a drop down list. Since this is for a find, we can use a value list that specifies the Cutomer name field (LastName_FirstName) to get a value list of full names. We'll not use Customer ID's for this value list so that we can make it auto-complete when you type in part of the customer's name. When setting up gFindCustomer as a value list on your layout. Click the "autocomplete using value list" option.

                  Now write this script:

                  Enter Find Mode [] //clear the pause check box
                  Set Field [Customer::Lastname_Firstname ; Customer::gFindCustomer ]
                  Set Error Capture [on]
                  Perform Find[]

                  Set a button next to your drop down set to perform this script and you can select a customer, then click the button to find them. You can also use the OnObjectSave script trigger to perform the find automatically when you exit the field after entering/selecting a customer.

                  • 6. Re: Select customer records via dropdown box
                    johannes

                    I did as you suggested and the result is an empty find screen and the layout is in Find mode. That's it.

                    It seems to have found the record but it remains in find mode. Maybe I need to do something to get it back into browse mode?

                    I have attached the script.

                    I tried both versions, the script trigger on exit and the button. The result is the same.

                     

                     

                     

                     

                    • 7. Re: Select customer records via dropdown box
                      johannes

                      Sorry. When I checked the ppst once again I found the mistake. It's the 'Pause' in the first step.

                      Thank you very much for your helo. It works like a charm.

                      Merry Christmas and a happy New Year and best regards

                      Hans