Welcome to the forum.
First, your dropdown is based on a value list. in the Field Control/Setup (in Layout mode) you can check "Autocomplete using value list" and have the list shorten as you type. That can make it more manageble.
Second, you can have your value list be based on your ID#, but show a different field on screen. In you value list setup, check "also use values from second field", choose the field, and choose "show values only from second field".
By doing this, you can choose the customer by name, but have the ID# put into the field upon your selection thereby fulfilling your relationship.
Is this what you are trying to do?
One approach to managing long value lists to make it a conditional value list. You select a category in drop down #1 and only the serial numbers that are a member of that category then appear in the serial number drop down.
Here's a thread on setting up conditional value lists:
Custom Value List?
Another option is what you have described. You can enter text in a search field and a script searches the database to find the relevant record and then uses data from that found record to populate fields in another table/record. If you precede that with a New Record/Request step, you'd have what you describe.
Here's a thread on such a scripted method. It searches by name, which you might want to consider, but it could also be adapted to search by a serial number.
tried doing the conditional value list and I tried using the search script, could not get the script to work at all. maybe i created the db wrong.
this is how i have it set up.
i have three tables
Field Report, Equipment and Customer
This is how I have the relationship connected.
Field Report Table - equipIDfk [number]
Equipment Table - EquipID [primary key]
Equipment Table - InvestIDfk [number]
Customer InvestID [primary key]
I use the serial number field from the Equipment table in a drop down value list to import data from the Customer table and Equipment Table.
Everything worked fine until I ran across the problem trying to find a serial number because the drop down list was too long. I wanted to be able to either type the serial number into a new record and have it populate the related information about the customer and equipment or have only the related machines show up in the value list so the drop list wont be as long.
I am guessing I might have designed my database wrong.
Do I need three tables should I have just one or two tables and put everything in the two table.
The customer table has the normal information [name, address, phone, email etc]. Equipment table has everything that relates to the equipment [model, serial, expiration date etc.]
The field report pulls information from customer and equipment table and then it has it's own fields
[problem with machine, resolution, parts needed, price etc.]
I guess the biggest question I have is should the 3 tables be 1 or 2 is the database designed right or do I just have to fine a script to work to search for serial numbers in the field report.
thanks see my comments above
I don't see anything wrong with your basic table structure and relationships. You have the following relationships, if I read your last posts correctly:
Equipment::equipID = Field Report::equipIDfk
Equipment::InvestIDfk = Customer::InvestID
"I wanted to be able to either type the serial number into a new record and have it populate the related information about the customer and equipment or have only the related machines show up in the value list so the drop list wont be as long."
These aren't mutually exclusive options, you can have both.
I assume that you are trying to create a new Field Report record and need to select the correct Equipment record by either entering it's ID or selecting it from a drop down. To have the info from the Equipment and customer tables automatically populate, you have two options. You can define matching fields in Field Report and use the Looked up value auto-enter option to copy the data from these two tables automatically each time you enter an equipment ID value in Field Report::equipIDfk. This records the current data at the time you create the Field Report record and is likely what you want here. Another approach is to dispense with the matching fields in the Field Report table and just place the fields from Equipment and Customer directly on your Field Report layout. When you select or enter a serial number in the EquipIDfk field, the matching data automatically appears. This is easier to set up, but if changes are made to the equipment or customer tables (say a piece of equipment changes ownership) these changes automatically appear in your field report and I don't think you want that.
If you want to shorten your list, you can add a relationship and then set up your relationship so that you first select a customer, and then this value is used to "filter" your equipment list down to just those serial numbers that belong to that customer.
Make a new table occurrence of Equipment, EquipmentByCustomer and link it in to your current table occurrences this way:
Field Report::InvestIDfk = EquipmentByCustomer::InvestIDfk
Set up your value list to draw values from EquipmentByCustomer::EquipID, Specify Field Report as your "starting from" table occurrence.
Now you can format FIeld Report::InvestIDfk as a drop down listing all your customers (You can make this a two column value list with InvestID as the first column and the customer's name as the second.) You select a value in this field first and then your equipment ID list should be limited to just that equipment that is owned by that customer.
Thanks For your help, I think I got it i had to make additional relationships for the customer names and serial numbers to show up on the form. But it does now, going to do some testing over the weekend.
Thanks again for your help.