5 Replies Latest reply on Jun 7, 2011 2:51 PM by LaRetta_1

    new concatenated field only works for new records

    jayGamel

      Title

      new concatenated field only works for new records

      Post

      A new concatenated calculated field consisting of first and last names with a space in between; i.e., First & " " & Last -- will not calcultate for existing records. Works for new ones. The field result is for text, the results are NOT stored, and relookup has no effect (there is another field I use for that). Any suggestions? I added a similar, much more complex calculated concatenation for address labels last week and it works fine. I can see no different settings for either field adn the new one does work for new records.

      Jay Gamel

        • 1. Re: new concatenated field only works for new records
          philmodjunk

          In Manage | database | Fields, does the field type show as text or as calculation?

          From what you describe, it sounds like you have a text field with an auto-entered calculation. If so, you should either change the field's type from text to calculation or you need to use Replace Field contents with this same calculation to update all your existing records. (Replace Field Contents will update a field in every record in your found set so do a show all records before you update.)

          • 2. Re: new concatenated field only works for new records
            LaRetta_1

            Hi Jay,

            "The field result is for text, the results are NOT stored,"

            This isn't logical.  If the field is text it will, by its very nature, be stored.  Only if it is a calculation (and not standard text field) will you be given a Storage Option (in which you might set it to NOT be stored).  Phil is right to suspect that it is auto-enter text and not true calculation.  If it were a true calculation field, ALL DATA would change properly when you exited field definitions.

            That field should be CALCULATION.  The only time you would want to leave it as auto-enter standard text is if you need to override the data at some point but, since you do not ever want to change the data directly (instead you change data in the fields which are concatenated), it should NOT be regular text field.

            So go now and change it to calculation.  And be sure, when in the calculation dialog, to change it from Type Number to Type text.   Do not uncheck 'do not evaluate if all referenced fields are empty' and leave Storage Options set to 'none' and 'auto-index'.   :^)

            • 3. Re: new concatenated field only works for new records
              philmodjunk

               " The only time you would want to leave it as auto-enter standard text is if you need to override the data at some point but, since you do not ever want to change the data directly"

              Or if you need a validation rule that only works when it validates the two original data fields in combination such as requiring the combined first and last names to be unique.

              • 4. Re: new concatenated field only works for new records
                jayGamel

                Thank you both. You are spot on. Changing to "calculated" did the trick. I don't know which to say "best" answer because you both have it perfectly right. Thanks again.

                Jay Gamel

                • 5. Re: new concatenated field only works for new records
                  LaRetta_1

                  True, Phil. 

                  If auto-enter were used for the concatenation (for purpose of unique validation), you would want to also uncheck 'do not replace existing value if any.'  If you don't uncheck that box, the auto-enter calculation will never complete the concatenation.  YOu will type the first name and go to the last name field.  The auto-enter (do not replace) calculation would enter the first name but would never enter the last name.