3 Replies Latest reply on Jan 31, 2013 4:18 PM by philmodjunk

    can't get my calculation field to change from unstored to indexed...

    hanstrager

      Title

      can't get my calculation field to change from unstored to indexed...

      Post

           I got a randome problem that has come up after touching up my data base.

           Basically I got a conditional value list in my Zip table, where you can choose different zip types, and based on that a zip size. In the same table I got a calculation field to give me a full name of any particular zip fx. YKK, M8, 36cm, Open ended, White. Not all these properties are always needed so I use the following in my calculation field:

           Substitute ( List ( Zip_Category  ;  Zips_spec 2::Size   ;  Zip_Length  ;  Zip_opening   ;  Zip_Color ) ; ¶ ; ", " ).

           This full name is then being used in my portal in my ‘technical sheet 2’  table for a BOM.

           The probem starts when I realized that the size field in my conditional value list was only sshowing the xZIPSPEC_ID so I made another field for showing the actual size from Zips_spec2::Size, and put on top of my field with the drop down and took off the option of field entry in browse mode. 'Zips_spec2::Size' was then put into my calculation field for showing the name properly, instead of an ID.

           But then my list of zips dosen’t show up in my portal in ‘technical sheet 2’ in my BOM and is giving me the following message:

           “This value list will not work because the field “Full Zip name” cannot be indexed.  Proceed anyway?”

           Then I go back to my calculation field which is ‘Full Zip name’, which have changed from indexed to unstored when trying to change it back to indexed it gives me the following error:

           “The calculation “Full Zip name” cannot be stored or indexed because it references a related field, a summary field, an unstored calculation field, or a field with global storage.”

           Which I guess is due to the newly added ‘Zips_spec 2::Size’. Does anyone know how to either get my calculation field stored again, but so I see the size from my conditional value list and also can use the full zip name in my BOM..?

      Picture_23.png

        • 1. Re: can't get my calculation field to change from unstored to indexed...
          philmodjunk

               There are at least two options that come to mind. One may or may not be possible.

               Can you describe the complete details of your value list setup? A screen shot of the appropriate dialog box may be a good way to do that.

               One option that I know will work, but which requires great care, is to change your calculation field into a text field with an auto-entered calculation. This field will be indexed, but will no longer update automatically when you modify data in the relatd Zips_spec table. The solution is to use script triggers on any layouts where this data can be modified that perform a script to update this field for any relatd records that match to it. But if you miss even one such spot in your database design, you risk this field not accurately reflecting the related spec data.

               The option that may work is to go ahead and specify this unstored calculation field and ignore the warning message. But that only works if specific options for the value list are NOT selected and the resulting value list may not look and function the way that you'd like.

          • 2. Re: can't get my calculation field to change from unstored to indexed...
            hanstrager

                 Here's how my value lists are build.

                 'Zip size' is my first value list which is my conditional valuelist, and 'zip' is my value list in my BOM.

            • 3. Re: can't get my calculation field to change from unstored to indexed...
              philmodjunk

                   is "size" the unstored calculation field?

                   If so, unless you are trying to use size as a match field in a relationship, you can ignore the warning message and your value list should still work.

                   Here's the little known fact on which I am basing this suggestion:

                   "use values from a field" based value lists must refer to at least one indexed field. But the other field, whether specified for column1 or column 2 does not have to be an indexed field as long as:

                   It's not the secondary field and "Show values only from second field" is selected.

                   The unindexed field is not specified in the "sort values using:" setting.