I hope I've followed all that correctly. It looks like you need a "switch" calculation that uses the contents of your radio button field to select the desired value.
Say you have three fields, cResult1, cResult2, cResult3 and a radio button formatted field, SelectValue. Give the radio button field a value list of simple numbers, 1, 2, 3 as this makes resizing the field as described below a little bit easier.
You can resize your radio button field so that the values are in a vertical column and the values are hidden. Then place each of the "cResult" calculation fields next to them to show what value will be selected when the user clicks that radio button.
Then this calculation field:
Case ( selectValue = 1 ; cResult1 ;
SelectValue = 2 ; cResult2 ;
Will return the value selected by the user. You can add as many fields as you need and they can even include a data field for simply entering a desired value if none of the calculations apply.
Thanks for the answer, Unfortunately I did not explain it very well
here is a simple senario ( not exactly what I am doing but it sort of fits)
If the following is my tree structure
Top Sub1 sub2
Breakfast Fruit Apple
Lunch Sandwich Cheese
Dinner Steak Beef
All of our products sit in the Sub2 tree level. you could think of them as all of the different brands / flavors / types that could fit under the apple heading So each Sub2 group has thousands of products in it
Now Management have decieded that Toast and Sandwitches are the same thing and should be in a new Top category called bakery but still have Sub 1 as toast or sandwich and the sub2 as the same
Some of the cheese manufacturers send me a list that tells me that their varieties should be in the Bakery, sandwich, cheese group, but not all of them send me a list.
So I can calculate that Edam should be in Bakery, sandwich, cheese from the list. Cheder is not on the list so I calculate that if it was in Lunch, sandwich, cheese it should now be in Bakery, sandwich, cheese.
However gorgonzola is not in the manufacturers list and it was previously in the wrong category Lunch, Soup, Vegitable. ( I can spot things that are potentially wrong by a combination of currency codes, supplier numbers etc but I cannot put them in the correct category from these pointers)
So now for each of the products that may be in the wrong category I want to be able to show what category the product is currently in, what category the calculations say it should be in and also allow the operator to select a new category.
Because there are so many categories I am only displaying the subset of the tree. ie on the Gorgonzola record (under the Currently in" area) you will see all of the top categories (with Lunch checked) but then only the Lunch sub categorys sandwich "and soup (soup selected) and then the soup sub categorys chicken and vegitable. The operator would select the sandwich category (overriding the calculated value) then be able to select the cheese category. This would then allow the calculation to put the gorgonzola in the correct new category.
We also need to be able to manually override the new category because some items don not follow the normal calculation.
I do not have the screen area to show what the results of calculations 1 and 2 are and also have a select value.
I hope this is a clearer idea of what I am trying to achieve. It is the overriding the calculation that is the problem.
I think I may have solved it by putting the calculation in to the auto-enter calculated value, enabling always validate and allow user to override.
If I enter a new record it will perfor the calculation, If I change the category the sub categorys change etc. To get round the problem of all the existing records I think I can save an empty copy of the database and import all the records from the first database into the empty one thus creating new records whci will do the auto-enter calculation.
Do you think this will work? is there a better way?
Did you know you can change a calculation field to a data field, then select the auto-entered calculation option and find the expression you orginally defined as the calculation now appears as the auto-enter calculation? And the value of the calculation at the time you converted its type is now entered as data in the field.
That might avoid the extra steps you've described above. Just to be safe, I'd duplicate the field before I tried this just to make sure I can go back to start if the results I get are not what I wanted.