Check out "Replace field contents". This tool can put the same value or a calculated result in a specified field of every record in the found set. You can do this manually or in a script.
Thanks for the advice. I've used the find/replace feature quite well on other data but I'm running into a hurdle to easily get the category & subcategory fields to populate when replacing just one of them. Any tips as I know there is an easier way.
Basically here is the "manual" process I've used...
Step One: Sort data by the "old_BusCode" field
Step Two: Perform find on one of those to only show all the records (ex: 32175 in that field)
Step Three: Fill in one of the records "Subcategory" field with the appropriate one and then use the find & replace to populate all found records
Step Four: Do the same thing to populate the "Category" field on all the found records
Isn't there a better way?
If your DB was linked to the new database by the Subcategory field, and Category was setup as a lookup from the new database, it should populate the Category field whenever you change the Subcategory field.
Although, it seems backwards to populate Category by first filling in the Subcategory...
Thanks. I was actually fooling around with the relationship/lookup and got it to work that way. It doesn't seem like the most efficient way to do this but it is effective and fairly quick.
Thanks for the advice. I've used the find/replace feature quite well on other data ....
Find/Replace and Replace Field contents are two different tools.
Replace Field contents is found in the Records menu and Find/Replace is found in the File menu.
Replace Field contents allows you to put the same value, a calculated value (which can then be different for every record), or a serial number value into a specified field of every record in the found set.
You should be able to build a script that does what you need quite easily.
You'd think I could whip together a script pretty easy but that isn't my strength. Any chance you could "whip" one together that I could implement??? IT'd be much appreciated and it'll save me hours of manually going to each "old code", finding all records with that, updating the category/subcategory for the first record and then using the replace field contents to populate the rest.
I re-read the thread and don't see enough info to tell if you can automate selecting the original Category, and SubCategory information. Let's assume you have to do this manually.
Set up a table, "Updater", with three fields:
BusinessCode, Category, SubCategory.
Set the following validation rule on BusinessCode: Unique values, always validate.
Now use import records to copy all the records from your existing table into this new table. (you'll need to map your "old business code field" to match BusinessCode.) The above validation rule will only accept the first instance of each Business Code, thus filtering out duplicate values. Now hand enter the appropriate Category and SubCategory values for each of these new records.
Define a relationship matching YourTable:: OldBusinessCode = Update::BusinessCode
Now you can script the conversion or do it all in two big manual Replace fields operations:
As a script:
Show All Records
Replace Field Contents [no dialog; Yourtable::Category; Update::Category]
Replace Field Contents [no dialog; Yourtable::SubCategory; Update::SubCategory]
Note: Make a copy of your file first so you can try this again if you don't do it right on the first try.