9 Replies Latest reply on May 5, 2011 11:58 AM by mfl666

    Parsing part of one field to another

    mfl666

      Title

      Parsing part of one field to another

      Post

      I'm stuck.
      I am trying to parse part of a field into another field via calculation.
      I used Position() which gave me the position of first blank which is  correct, but now I want to place everything after that into my second  field.
      First field "Product_CodeDesc" contains - "MCD01234 some description"
      The first 7 chars will never change in length.
      Second field is my calc field that should hold the description part.
      What function do I put the Position() statement in to retrieve the description section.

      Thanks
      Michael

        • 1. Re: Parsing part of one field to another
          raybaudi

          RightWords ( Product_CodeDesc ; WordCount ( Product_CodeDesc ) - 1 )

          • 2. Re: Parsing part of one field to another
            mfl666

            Thanks raybaudi

            So simple, that worked perfectly.

            So now I have a different problem, the new field was to be used in a value list but guess because its a calc field I get the message field cannot be indexed.

            So I created a text field and added as auto enter calc your formula but the field ends up being empty.

            Auto enter fields only gets values when data is entered?

            • 3. Re: Parsing part of one field to another
              philmodjunk

              Is Product_CodeDesc a field that is defined in another related table?

              • 4. Re: Parsing part of one field to another
                mfl666

                Its pulling data from a related TO based on another DB.

                • 5. Re: Parsing part of one field to another
                  mfl666

                  A little clarification.

                  Product_CodeDesc is a field in table A in DB #1.

                  The value list is based on a TO in DB #2 from DB #1.

                  Did I confuse you cause I think I am.

                  • 6. Re: Parsing part of one field to another
                    philmodjunk

                    That does identify the problem. When you specify that a field be used as the source of values for a value list, the field must be indexed and stored. But your calculation field cannot be stored and indexed if it refers to a field from another table occurrence. Your auto-entered calculation field can be stored/indexed but it will not update each time you modify the Product_CodeDesc field as auto-enter calculation fields can only update when a field used in the calculation that is from the same record is updated.

                    Also, such auto-enter calculations do not automatically compute a new value when you edit the defined calculation nor do they assign values to the field for existing records when you first add the field. When you add such a field to an existing table, you often need to copy that expression to the clipboard and then use it with a Replace Field Contents operation to update all the existing records with the correct values.

                    You may have to rethink this to find a way to get a stored, indexed value into a field where your value list can use it. You may, for example, need to define this calculation field in the same table as where Product_CodeDesc is defined and then refer to that field in your Value List setup.

                     

                    • 7. Re: Parsing part of one field to another
                      mfl666

                      Actually the calc field "Product_Desc" and the field "Product_CodeDesc" are in the same DB/table #1.

                      Ok, think I'm hooped.

                      Product_CodeDesc is an unstored calc field built from several different fields of which one is an unstored calc and I am unable to store it.

                      If I try to change storage I get the message "The calculation Product_Desc cannot be stored or indexed because it references a related field, a summary field, an unstored calculation field, or a field with global storage."

                      I have the data in the field but am unable to use it.

                      Not sure which direction to go from here, do I do the Replace Field Contents option with the auto enter calc or .....

                      • 8. Re: Parsing part of one field to another
                        philmodjunk

                        There are a number of options, but it's difficult to make a solid recommendation from here as we don't know the structure of your tables and how you need to use this part of your system.

                        One way is to define a simple text field in the appropriate table with an auto enter calculation that pulls the current values each time a new record is added to this table. Clear the "do not replace existing value..." option so that any edits to local fields it uses will update the calculation correctly. Then on any fields defined in other tables, use an OnObjectSave script trigger on the field to run a script that updates this text field for you. This method may work very well by updating just one or just a few records each time the external field(s) is/are edited or it could be very impractical, requiring the script to update hundreds of records each time such a field is edited--it depends on how you've set this up.

                        • 9. Re: Parsing part of one field to another
                          mfl666

                          Ok, so I created the plain auto enter text field.

                          Then ran the replace field contents.

                          Modified the value list to select from the new field and i believe I have lift off.

                          The true test is when I give it to the user, very finicky these people.

                          Still want to muck around a bit more before unleashing.

                          Need to verify that this field gets updated when the user creates a new record in DB #1

                          In the mean time Thank you very much raybaudi , PhilModJunk for your help.