Forum Discussion

CarreraHalwachs's avatar
CarreraHalwachs
Qrew Trainee
6 years ago

Consolidating two fields into one

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
Forms
Tables and fields

4 Replies

Sort By
  • Quick_BaseJunki's avatar
    Quick_BaseJunki
    Qrew Captain
    6 years ago
    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.


  • PamelaBray's avatar
    PamelaBray
    Qrew Cadet
    6 years ago
    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. 
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    ..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.
  • CarreraHalwachs's avatar
    CarreraHalwachs
    Qrew Trainee
    6 years ago
    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!