Replace a field on a custom form with a new one and move the data from the old field to the new field. Can this be done without Fusion? If so, how can it be done?

[Tracy_LG]

Hello.  So, I have an old field on a custom form called "Review Proofs."  We would like to replace that field with a new field called "Reviewing Proofs."  I need to move all data from the old field to the new field.  Can this be done in text mode versus using fusion?  If so, how do I do it?  I'm not good at text mode whatsoever.

Thank you for your support!

Tracy

[KatherineLa]

Your options partly depend on the nature of the data in the existing field, and how many records already contain data in it. I'm also assuming that simply renaming your field's form display name, or re-labeling report column headers to clarify isn't an option here? 

When I've had minimal records that were previously completed, I added the new field to the form and saved. Then I built a report that showed both fields side-by-side, and copy/pasted while my coffee brewed. It's not often feasible but sometimes the easiest solution.

In some instances I've had way too much legacy data for that to work, but the legacy field was also a picklist/radio button style field. In those cases, I did the same as above but then cycled through each item in the legacy drop-down with report filters. Isolate all the records where <Legacy Field> = 'Red', and then made a mass-update on all resulting records to select whatever the corresponding value was in the new picklist. Rinse and repeat for each value in the legacy picklist.

In the really difficult ones, I've had legacy data that was either text or typeaheads where neither option above is practical. In those cases, then I've built a Fusion scenario to copy everything over. 

[Tracy_LG]

Hi, Katherine.  Thanks so much for your response.  I should've mentioned this:

The old field is a string.

The new field is a numerical value.

Hence the field change.

I know, a big omission.   Sorry.

Tracy

[Doug_Den_Hoed__AtAppStore]

Hi @Tracy_LG,

Sadly, such "Text" mistakes happens (especially being the default data type); and as you've been discussing, once data is entered into a custom parameter, the data type cannot be changed. Given that, I invite you to consider our Excel Updater solution, which -- unlike native Kickstarts, which can only insert data -- includes data updates, and with which you could:

• Export a Workfront Report of each ID of interest (e.g. Project ID, if this is a Project form) along with the "Review Proofs" (text) column to Excel
• Delete the first tab (with hyperlinks, etc.) from Excel
• Rename the second tab to the Object Type (e.g. PROJECTS)
• Rename the Reviewing Proof column to "Reviewing Proofs" (number)
• Upload and run Excel file through the Excel Updater
• Repeat the previous step: if it worked, this second pass will have nothing to do, confirming the first pass worked (as you'd then be able to observe within Workfront)
• Delete the Reviewing Proof column if it is no longer needed
• Update any existing views, reports, etc. to now refer to the new Reviewing Proofs column
• Put a post-it note on your monitor that says "Got the right datatype?" to avoid future incidents

Depending on how many rows you have, the coffee brew option @KatherineLa mentioned might suffice, but I decided to mention this approach, too, so you and others can Save Until Needed.

Regards,

Doug

[Doug_Den_Hoed__AtAppStore]

Ah: and a late entry ...

My colleague @skyehansen just mentioned another possibility that gave me an idea you might consider (not recommended, but quick and dirty): delete your Review Proofs numeric textbox parameter and re-add it as a numeric calculated parameter that simply "points" to the Review Proof (text) parameter. If the latter is a number, it will translate correctly. If not...it will be blank, so I'd then go back to my Excel Updater suggest.

Regards,

Doug

[skyehansen]

I'm not sure I'm following.

In cases where I made a text field Review Proofs and formatted it as text, and then later, I feel a need to format it as number, I usually run a report that shows me all the content in the field. It's then a manual effort to change any text answer to a number, and then you can reformat the text field as a number instead of text. (keep the same field in other words)

So if I run a report and get six rows with a Review Proofs column that reads:

• 2
• 3
• 4
• five
• 1
• 3

I'll make the change to the "five" to change it to 5, and then I should be able to change the text field to a number field.

Otherwise I don't think there's any way to move text content into a number field anyway, so you would still be missing that content if you do it via automation.

[KatherineLa]

You can get part of the way there with automation, though not 100%. What I do is create an Update module that says 'take the data from the legacy field, and put it in the new field' but I wrap the value in a parseNumber() formula first, and then tell Fusion to remove the value from the legacy field after it's done. When it encounters a 'five' rather than '5' it will thrown an error, so I put error handling on to tell it to ignore and keep going.

Once it's complete, I have all the values that were numeric to begin with in my new field, and I can quickly build a report in WF to show me all the data remaining in the legacy field that couldn't be converted. Now that I've moved the real numeric data out of the way, it's a much shorter list to review. In a few cases, that showed patterns of text I was able to keep converting with Fusion if I had a really detail-oriented end-user. I had one who typed things like "Counts: #####" over and over again. Then I could strip off the first part and get the real numbers with formulas too.

[Tracy_LG]

Hi, Doug.

Thank you for the insight. I appreciate the guidance on the Excel Updater.  I'm not sure if we can get that, but I really like the direction!

Thanks again.

Tracy

[KatherineLa]

Ooh boy, that's a painful one! I've been through that before myself. Assuming you have too much data to simply copy/paste, then Fusion or Doug's solution below are your main options. If Fusion is available to you, I can give some notes on how I set my scenario up when it was my turn for that cleanup.

[Tracy_LG]

Hi, Katherine.

Thanks so much for your reply.  I always look forward to reading your solutions.  I'm moving forward with your thoughts and will use fusion to make sure we've captured all occurences.  Thanks, again!

Tracy

[Richard__Carlson]

Can you create a report which isolates all of the old data and then using that report bulk move the values from one to the other by editing?

[Doug_Den_Hoed__AtAppStore]

That’s a good option @Richard__Carlson,

Doing a group by, then bulk updating each group in chunks can work. Alternatively, back to the Excel Updater approach, you could add a bullet point to cleanse the data in Excel (eg five becomes 5) prior to uploading.

Lots of alternatives to soak up this spill.

Regards,

Doug

[-JC]

I use API calls to achieve this. If for example, the object type is Project, I will pull a project report in Workfront with (at minimum) the Project ID and the "Old field". I'll throw that in Excel, add a forth column, generate an API key for myself in Workfront, and write a HYPERLINK formula using the Project ID, "Old field", and API key values to then populate the new field. I still have to click in order to update the "New field", but it's less clicks than manually replacing the data within Workfront if I know I have a wide swatch of unique values.

For example, our instance unfortunately had two "Cost centre" fields that I had to consolidate. I pulled a Project report to display all projects where the "wrong" cost center field was populated. I then used the following formula in Excel:

=HYPERLINK("https://MYINSTANCENAME.my.workfront.com/attask/api/v17.0/project?ID="&[@ID]&"&apiKey=MYAPIKEY&method=PUT&DE:Cost%20centre="&[@[Old cost centre field]])

You would replace MYINSTANCENAME and MYAPIKEY with your own, as well as the project ID ([@ID] in my table), new field ("DE:Cost%20centre" in my case), and old field ([Old cost centre field] in my table). Then, I would click the links which opens up a new browser window for each click, wherein you should receive a message that the information has been updated.

Not sure this is the simplest explanation, but hopefully it helps you or someone else reading!