Consolidating two fields into one

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • Answered
I inherited a QuickBase database that had been set up (poorly) as a CRM by my predecessor. Rather than creating a drop down menu to classify a client as "current" or "prospect," he created several extra fields in one table. For example, I have a "Client Name" field, a "Client Address" field, a "Prospect Name" field, and a "Prospect Addresss" field. If a client current did business with us, he filled in the "Client Name" field and left the "Prospect Name" field blank, and if it was a prospect, he filled in the "Prospect Name" field and left "Client Name" blank.

The result is that I have a lot of extraneous fields, and my field reps are complaining that they can't find the information they need easily. I have already created a drop down menu to classify a record as a "Client" or a "Prospect," which has made it a bit easier.

There are about 670 "Prospect" accounts, and I would like to merge "Client" and "Prospect" fields into one field. Is there a way to do this easily?

Thank you!
Carrera
Photo of Carrera Halwachs

Carrera Halwachs

  • 172 Points 100 badge 2x thumb

Posted 3 months ago

  • 0
  • 1
You bet!

You'll need to create a new formula text field with a formula similar to the following, but using your field names.

If([Client]="",[Prospect],[Client])

This will ensure that you are always using the Client field should both Client and Prospect be populated.

After verifying the result looks the way you want, you will change the field type to be Text. It will retain the formula value, but will no longer be a formula. Then you can remove the old fields.


Photo of Pamela Bray

Pamela Bray

  • 272 Points 250 badge 2x thumb
Hi there, 

Personally, I think the cleanest and easiest way would be to export the Client and Prospect fields out to excel and merge the Prospect information into the Client information and then upload just the Client information back into QB, which will then allow you to delete the Prospect fields from your app. Otherwise you have to create yet more fields with formulas. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
..on that theme, if you make a formula field and then convert it to a text field, Quick Base says to itself "gee - what should I do with the calculated values for these 1000's of records".  So, it just leaves the straight text behind in each respective record. 

Maybe that thought will help too.
Photo of Carrera Halwachs

Carrera Halwachs

  • 172 Points 100 badge 2x thumb
I'm literally making this up as I go along, so I really appreciate all the quick responses.

I did initially try the text formula field (didn't know I could create text formulas), but ended up exporting it to a CSV, cleaning it up, and pulling it back in. It was my first time doing an export/import, so I created a duplicate of the app to test drive it, but I got it figured out without crashing and burning too badly along the way.

Thank you for the help!