3 Replies Latest reply on Oct 14, 2010 10:09 AM by philmodjunk

    How can I override a calculation field



      How can I override a calculation field


      I have thousands of Product records many of which have incorrect information in them.

      They all have to have a new set of data added to them which is in a tree style ie top, sub1, sub2

      The new information can come from 3 sources. First is a complex calculation bassed upon thier current data.

      second is a complex calculation bassed upon an external set of data.

      Third is manually enter the data.

      I am using a number of conditional lists to present the operator with a sub set of radio buttons bassed upon their previous selection in the tree.

      I would like to be able to show any new data that has been calculated in the same radio buttons but still allow the operator to change the currently calculated value and select a new value where the calculation has failed/ not done because of insufficient data etc.

      For example the top level should always be able to be calculated from existing data but this is wrong on many cases. This will be corrected by the external data in most cases. The operator needs to be able to see what the information is and still be able to change it.

      In many cases the Top and Sub1 level will be correct but sub 2 is missing etc

      The problem I am having is that if I set the Top , Sub1 & Sub2 as calculations I cannot change them manually.

      I could run the calculations then change the fields back to text but the problem I have is that multiple people are going to be working on this data correction. The original data needs to be corrected and the new data needs to be corrected If someone corrects the original data then the new data should be calculated correctly so I would like to be able to keep the calculation and be able to change the results.

      Is there a way I can do this?

      For new records I could use an auto enter calculation but the records already exist.

      Thanks for your assistance.






        • 1. Re: How can I override a calculation field

          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 ;
                     Result3 )

          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.

          • 2. Re: How can I override a calculation field

            Hi Phil

            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


                                    Cereal             Weetabix


                                    Toast               Brown



            Lunch               Sandwich         Cheese


                                    Soup                Chicken



            Dinner              Steak               Beef


                                    Pasta                Ravioli



            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?




            • 3. Re: How can I override a calculation field

              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.