Choosing text value from list to put calculation in another field

[JohnnyG]

I am working on a fairly complicated database that uses many parameters to set a fee structure. Most of it is straight forward and I have completed all but two input screens out of 50 or so. However, I am new to scripting and I need some help. Here is what I would like to do:

I have a value list with 8 different text parameters. I would like the user to be able to pick one of the 8 and have that choice trigger a calculation (a fee related to the choice) that is put into another field which is then used in another calculation. So, right now I have two fields, one requiring a choice from the value list and one to which I need to point to a calculation that will provide the dollar amount for whichever type of choice is made.

The script will need to run until it recognizes the choice, either 1 thru 8 or "None" (which will result in a "0.00" being placed into the field). Once the script is satisfied, then the script needs to take me or the client to the next field requiring a different input.

Any ideas?

[comment]

I believe this could be done quite simply by creating a table for the text values and their associated amounts. Once you select a text value, you can immediately "see" the associated amount through a relationship matching your selection to the 'text' field in the new table.

No script is required for this, and the amounts can be changed in time by simple user action.

[JohnnyG]

Thank you for your reply. How then is the amount placed into a field to be used in further calculations? Do I use "look-up" with two fields, putting the first field as the text and the second as the value? I have several value lists which I am currently using and would like to associate text with the actual value for explanatory reasons. However, I can't put both text and values in the same value list and have it work. Would I do the same thing as the above or is there a work around?

[comment]

How then is the amount placed into a field to be used in further calculations?

It's not necessary to place it into a field (although it's possible by doing a lookup). It already IS in a field of the related record, and you can refer to it directly in a calculation formula.

[JohnnyG]

Again, thank you. I am really a neophyte at this design process and am just beginning the learning process. As I mentioned at the start, this is a fee calculating solution. Many calculations are based on other calculations. My problem here involves about 8 different items which fall into three categories (technical, semi technical and non technical for lack of better descriptions). Those three categories have several different parameters used to calculate the fees associated with them. If I attach a number to each of the eight items and the fees change, then I have to go back and change them manually. The problem remains the same even with a new table. I still need to be able to pick an item and then associate one of the fees to it. By the way, the three fee categories are global because they are used in several other areas. I would prefer to trigger a script that I can attach to the entry field (a text value list) that would then pick the associated fee and place that fee in another field to be used for further calculations. Sorry if I can't see the solution from your standpoint.

[comment]

I am not sure I follow this fully, but this item caught my attention:

If I attach a number to each of the eight items and the fees change, then I have to go back and change them manually.

What exactly do you WANT to happen when the input amounts change? Do you want all existing records to reflect the changes automatically, or do you want them to retain the original values?

[JohnnyG]

Thanks! I want all of them to change. I am working with different companies and each has its own fee structure based on expenses as well as other parameters. As the expenses change, I will be able to reflect that based on one table which holds the expense figures and from which all the other fee parameters are calculated. Thusly, when the expenses change (typically this will only be done once yearly) it is a relatively simple operation to update the expenses for the new year which then updates the fees automatically. I have several value lists which are used throughout the solution. One problem I have is that I want to attach text explaining each value. I can't do that with the current value list situation. Any suggestions on that as well?

[comment]

See if this makes it clearer.

RelatedValues.fp7.zip

[JohnnyG]

Yes, it definitely does. Thank you so much.