8 Replies Latest reply on Oct 13, 2016 6:35 PM by cortical

    Adding a prefix to all data in a specific field in a found set

    jeremyb

      I need to add the prefix "BLB" to many current records for the field ItemIDs. So what is now "12345" needs to become "BMB12345".  The field is already set to text and I have already conducted a Find to generate my Found Set.  What is the best way to write a script to add the prefix to the field ITEMID for all records in the found set? Thanks! -Jeremy

        • 1. Re: Adding a prefix to all data in a specific field in a found set
          alquimby

          Be sure your ItemID field allows modification of data (temporarily during this replace). In your found set of records put your cursor in the ItemID field and go to Records > Replace Field Contents > Replace with Calculated Result.

           

          Your calculated result will be "BLB" & ItemID. Hit replace. All found records should update in that field.

           

          No script necessary. Be sure to make a BACKUP before you do this. You cannot undo.

          1 of 1 people found this helpful
          • 2. Re: Adding a prefix to all data in a specific field in a found set
            jonmyrah

            Is this ItemID a key field used in any relationships? If so, changing this value will break these relationships unless you also update the foreign key value in other tables as required. To avoid this you could create a second calculated field that equals "BLB" & table::ItemID, with the result set to Text.

             

            Alternatively, if this is just a data field not linked to anything else you can use "Replace Field Contents..." with the same calculation. Make sure you are in the ItemID field in one of your records. "Replace Field Contents..." only runs on your current found set.

            1 of 1 people found this helpful
            • 3. Re: Adding a prefix to all data in a specific field in a found set
              beverly

              1. back up the database before anything!

               

              2. create a looping script, something like:

               

              go to record ( first )

              loop

              set field ( itemID ; "BMB" & itemID )

              exit loop if ( get ( foundcount ) = get ( recordnumber ) // exit after final process )

              go to record ( next )

              end loop

               

              beverly

              • 4. Re: Adding a prefix to all data in a specific field in a found set
                coherentkris

                Pay attention to johnmyrah when he warned about changing data that may be used in relationships.

                Bevs suggestion to back up should be a no brainer.

                If ItemID is a primary key for the table then anywhere it is used as a foreign key needs to be reset as well.

                I would also caution about out using "smart" values for keys.

                If they are "dumb" and not exposed to the user you'll never have to reset them.

                I always go for pk's that have no meaning to the user or the data.

                1 of 1 people found this helpful
                • 5. Re: Adding a prefix to all data in a specific field in a found set
                  mweldon@ansi.org

                  Don't replace your real data until you first try your approach on a test database. As mentioned earlier if this Item_id field is used in a relationship you will need to create another set of relationship fields in each table, call them Item_id copy and then replace the contents of the new Item_id copy fields with the contents of the original Item_id fields. This way you still have your original data. To do this you will have to use the Replace Field Contents feature. In table 1 go to the Item_id copy field and hit Ctrl+= keys and select "Replace with calculated result" and "specify":

                  Item_id

                  Go to table 2 and do the same thing, then create the relationship in the graph:

                  table1::Item_id copy<------>table2::Item_id copy

                   

                  Next...To replace the data in the set of records that have numerical codes such as 1234, do this: After you find your set of records, place your cursor in the Item_id field and hit Ctrl+= keys. This will present you with the "Replace Field Contents" dialogue box. Use "Replace with calculated result" and "specify":

                  BLB & Item_id

                   

                  Now here's the tricky part... If you want all records going forward to have this same alphanumeric code then you will need to change the field definition of Item_id so it works like this.

                  Item_ID should be a text field. Select Options and use the Auto-Enter tab. Check off Calculated Value and specify:

                  "BLB" & Get ( RecordID ) and Check the box "Do not replace the existing value of field (if any)"

                  You may want to also check off "Prohibit modification of value during data entry".

                  Every new record created will have BLB and a new serial number with increments of 1. I.E.: BLB2789, BLB2790, BLB2791, BLB2792, etc... Hope this helped!  --Mary Weldon

                  • 6. Re: Adding a prefix to all data in a specific field in a found set
                    keywords

                    "I always go for pk's that have no meaning to the user or the data."—I totally agree with that approach. I think of key fields as developer-only fields; users need not even know they exist.

                    • 7. Re: Adding a prefix to all data in a specific field in a found set
                      philmodjunk

                      Many times, however, support is required for such a field either because it is already in use or because a user insists on it.

                       

                      Should you have no choice but to employ such a ID field, do so, but don't use it as the match field in relationships. Treat it as a special "label field" that can be used for searching and sorting, but use a real, no-encoded meaning ID as your actual primary key.

                      • 8. Re: Adding a prefix to all data in a specific field in a found set
                        cortical

                        as others"

                        1. where else is it used (as foreign key) - all will need to be correspondingly updated

                        2. redefine the next serial

                        3. prohibit modification disabled ( this is NOT optional)

                        4. do not attempt live, i.e if there are users logged in, or else open records ( in use by someone else) will fail

                        5. don't forget to show all records before replace

                         

                        Whenever I REPLACE, I first copy ('replace') the original values to a dev field, then do the REPLACE on the real target

                         

                        I have never subscribed to the 'no meaning' school, a PK is a PK

                        Looking at multiple columns of key data for dev purposes, alpha prefixes are vastly more readable

                        2 of 2 people found this helpful