8 Replies Latest reply on Nov 23, 2015 7:12 PM by JennyStratton

    Populating an auto-fill field for existing records?

    JennyStratton

      I just learned how to concatenate, and have defined automatic data entry to concatenate data from three fields into one.  Problem is - I can get it to work for new records, but I've got 900+ preexisting records for which I'd love to get this field auto-filled.  What's the trick to make this happen for existing records, and not just newly created records?

       

      Thanks!!

        • 1. Re: Populating an auto-fill field for existing records?
          SteveMartino

          Make a back up first.

          Create a found set of the old records. Click on the field, then from the top menu go to Replace field contents, and select replace by calculation.  Use your calculation.

          ANother way may be to export the found set and import it back in.

          • 2. Re: Populating an auto-fill field for existing records?
            JennyStratton

            Perfect!  This is going to come in very handy.  Thanks, Steve!

            • 3. Re: Populating an auto-fill field for existing records?
              keywords

              Just be VERY careful when using the Replace field contents function. One of the biggest dangers, in my opinion, is using Replace on the wrong found set. It is very powerful, and is a one way street—you cannot go back to a previous state, hence Steve's first step—make a backup before you start.

              • 4. Re: Populating an auto-fill field for existing records?
                JennyStratton

                Yes - definitely.  With great power comes great responsibility!  I'll use the function sparingly and with care!

                • 5. Re: Populating an auto-fill field for existing records?
                  LaRettaK

                  Jenny said, "I just learned how to concatenate, and have defined automatic data entry to concatenate data from three fields into one.  Problem is - I can get it to work for new records, but I've got 900+ preexisting records for which I'd love to get this field auto-filled.  What's the trick to make this happen for existing records, and not just newly created records?"

                   

                  Jenny, I wanted to point out a few things here:

                   

                  1) If this same concatenated data from three fields happens in every record, why are you not using a regular calculation instead so you never have to populate it?  Are Users ever allowed to change this data?  It seems not.

                   

                  2) Do you need this concatenated field for export?  I ask because many folks forget that we can just use merge field on the layout.  That might work for you and it might not but I wanted to mention it. And for rarely used calculations, a placeholder field works great.  Create a global field which can never hold data (set it to valid always with If ( not IsEmpty ( Self ) ; "" ).  Place it where you want to display this rarely-used calculation and go to Inspector > Data tab and placeholder and set your calculation there.  It works great in browse and find modes.

                   

                  3) A quick way to data-populate ALL RECORDS in a table with a calculated value is to a) back up first, b) change the field definition from text to the desired calculation, c) make sure the calculation result is text, d) then exit manage database then go back into the definition and change it back to text.   All records, regardless whether they are in found set or not, will inherit the new concatenated data so if Users have modified new records, this will not work here.  There is no difference between stored calculations and data except the former can't be modified by Users (which most times is preferred).  This method means that you do not have to go to a layout belonging to the table you are modifying nor that you need to show all records first and it is much faster since you do not have to physically loop through records.

                   

                  As with Replace Field Contents, changing data via field definitions should not be ran when Users are in the solution.  Any time you even open a field definition, you can cause errors for a User.


                  Calculations, particularly concatenated calculations of other fields, should be limited since they hinder performance.  Also on item #3, this will not work if any part of your calculation includes an unstored calculation, related field or global field.  I assume your concatenation is comprised of three fields within the same record, right?

                  • 6. Re: Populating an auto-fill field for existing records?
                    JennyStratton

                    LaRetta, thanks for the insights!

                     

                    1 - If I understand rightly how to describe what I'm doing, I think I am using a regular calculation.  I had just been continuing to add and modify fields after I imported my first set of 900 records, and was unable to get the field filled for preexisting records unless I went in and modified at least one of the contributing fields for each record.  From this point on, I shouldn't have to repeat Steve's fix, since the regular calculation is working as expected.  And yeah, I don't want this users to be able to modify this field.  It's a concatenation of genus-species-subspecies names, and should always match exactly whatever has been put into those three initial fields.

                     

                    2 - good to know!  I don't need the field for export, but rather for matching when importing additional records, which are likely to have genus-species(-subspecies) names strung together rather than separated.

                     

                    3- I wonder if this describes my problem in the first place - I had set up an auto-enter calculation in the desired field, but left field type on "text."  I guess I should have set it to "calculation"!  It's working fine now still set on "text," so I'll leave it be, but will remember your solution for future issues.

                     

                    And yeah - all fields involved in the concatenation are within the same record.

                     

                    Thanks again!

                    • 7. Re: Populating an auto-fill field for existing records?
                      keywords

                      Re: "It's a concatenation of genus-species-subspecies names, and should always match exactly whatever has been put into those three initial fields."

                       

                      Given the above, I would suggest you simply convert the field to a calculation field. Forget all about auto-entering and updating existing records. A standard concatenation calc will simply BE as up to date as its component fields—for ALL records, not just new ones.

                      • 8. Re: Populating an auto-fill field for existing records?
                        JennyStratton

                        Ok, I think I'm learning!  I must not have a regular calculation in the field after all, but that's clearly what I've needed from the beginning.  Thanks, keywords!