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.
Thank you for your reply. I tried your first step and here is what happens:
The fields are
- customer::ID (autonumber)
- 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.
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.
Itz's a test application to try out different techniques. This part is as follows:
- Last name
- First name
- (calculated) lastname_firstname
- some other details ...
- 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. :-)
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]
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.
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.
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