At your company's inception, you or someone else may have felt that certain fields should be recorded separately and independently of one another.
Sometimes down the line though, as your company advances their use of UCM, that decision feels less and less like a good one, or maybe you just want to get more organized.
But how can these fields be combined without losing data??
For this example we'll combine two single line fields into one multi-line field. Specifically, 'Vendor Address 1' and 'Vendor Address 2' becomes Vendor Address'
Before making any big changes to the system, do a data back-up!
Ok, I have a data back-up. Now what?
- Since Address 1 and Address 2 are going to be bulk-loaded together into one multi-line, you only need to add Vendor Address as a new multi-line field, if it hasn’t been created already.
- Add the New field to the Field Group(s) it belongs in. IE: anywhere you found the old Fields! This step is very important to keeping your Fields in the Field Group you want. Without adding them to a Field Group they just get strewn in the Summary Tab.
Create a View
- Create a new view with the criteria below, to show contracts that contain data in the fields you’re replacing.
Vendor Address 1 – Is Null – False
[or] Vendor Address 2 – Is Null -- False
- Now, under Default Fields and Sequence, you’re going to pick the columns. At minimum you must have: Contract ID, Vendor Address 1, Vendor Address 2
- Click on “Save this View” and give it a name that makes sense to you. When you’re re-directed to the Contracts Grid, click on “report” and choose the “Bulk Loader” report. This creates a .CSV file of your exported view.
- Once the report is downloaded, crack it open and delete the first two rows of the sheet. One will be a blank row above the grid headings and the other will have the name of the view you created
Edit your Report
- Since we’re combining 2 fields into one multi-line field, we’ll have to get all the data from the columns for Vendor Address 1 and Vendor Address 2 into one, single column. This can be done with an ampersand!
- On our sheet, Contract ID, Vendor Address 1 and Vendor Address 2 are set up like so:
- Since column D is blank, that’s where I’ll be putting the formula to join column B and C. So, in Column D, Row 2 type out this easy formula to combine the columns. The " " between your combined columns will make sure there's a space between the data!
- When you are done entering the formula, hit Enter and in place of the formula should be a combination of column B and C to make the full address. Fill all of column D with that formula by clicking and dragging the formula down, or double-clicking on the little square in the corner of the cell.
- Now, select all the data in column D and copy/cut it. Paste it back into column D using “Paste Values Only.” This seems like an unnecessary step, but if you delete the old fields, the formula you used will have nothing in column B and C to combine!
- Delete column B and C with the old fields types! Now you should have only the Contract ID and the completed Address field. Time to give it a header! To bulk load these values into the system all at once, the header needs to be in the format Field Group::Field Name.
This field is a Vendor Address field, in this company, it’s found in the Vendor Info field group.
So, the header should be Vendor Info::Vendor Address
Bulk Loading your Sheet
- You’re almost done! Save this file as a .CSV file.
- Now, fire up the bulk loader and enter your credentials. (Make sure the file you’re using to bulk load is closed!
- Click on Menu -> Upload Contracts. Then click “Update Contracts” checkbox. This will just update contracts by using the Contract ID (selected by default). Click “Browse” to dig up the file you just edited and click “Upload Contracts” With some luck, you will be able to update your fields with no issue! ? ? ?
- Once you’ve successfully loaded in the field data, go to the view you created to export. Under “Default Fields and Sequences,” add the column “Vendor Address” that you created. If everything went well, your Vendor Address field should be a successful Frankenstein’s monster of Vendor Address 1 and Vendor Address 2.
- If that’s the case, go ahead and delete those 1 and 2 fields from Contract Fields Administration and you’re done!