If you have a related table, related by your value-list-field, the "component value" would reside on the related table.
You could then put the "component value" field on your record and it would show up when you made your drop-down selection of field1. No scripts, no calculations...
Try it this way:
Create a new table (Table2) with 2 fields "Component" and "Component value"
Create 10 records in this table, one for each component on your value list.
Fill in the corresponding value on each record.
Go to File>Manage>Database>relationships tab
drag the "component" field from one table to the matching "component" field in the other table
Go to your layout and put the field Table2::componentvalue on the layout.
Go into browse mode and select a component from your dropdown...the componentvalue field from Table2 should show the corresponding value. Play with it by changing the dropdown choice...
Note: In field behavior, you should make the Table2::componentvalue field not enterable in Browse mode to avoid changes to the value. If you change it on one record, it will change on them all.
A variation on Ninja's suggestion:
You can also use a "Looked up value" field option to physically copy the value from the related table. This is a good idea if you will need to change the values in your components table from time to time and don't want the changes to affect data in your existing records--only new records.
Thanks you two!
A little summary here.
I have a main table, which will be used for the data entry layout. It has 68 fields, 58 of which will EACH have a value list. I don't see any way of getting around that.
Each value list has 10 values.
I am hoping for a "title", "field" (from a drop down list), "field" (with a numerical value according to the item chosen from the drop down list). The input from the last field will then be used in a seperate table for another calculation.
I assume from your replies that I need to have a seperate table for each of the 58 fields.
A DB redesign that simplifies your life may be possible.
Please describe the "58 separate fields" (examples are really helpful) and we'll see if there's a way to make life easier for you.
I need to come back to this. I have a rush job to do for next week.
Would sure love to get this one sorted out.
Thanks very much for the input.
I am building a database for an ongoing survey. Each completed survey set is recorded.
There are 58 "questions". Each question has 10 choices.
I have build a main table, called "survey".
The answer to each "question" is a set text answer.
Each answer has a numerical value on the master list.
I need to see the text, which works via drop-down value lists.
The next bit is where I am stuck. I need the numerical value for use in further calculations.
I am now trying as suggested, by making a table for each question, with all 10 possible results.
The relationship graph will be a mess, as there will then be 58 relationships.
I will plug away at this over the weekend.
Here's a thread on creating a survey results database that maps out a method (See post by comment) that will eliminate having a different field for each question and will make tabulating your results much easier to create: