Forum Discussion

KristinBombaci's avatar
KristinBombaci
Qrew Member
6 years ago

Can I concatenate two fields into one when one field is blank?

I need to merge two field into one field if one field is blank. I have developed the below formula based on advice from this page but it has not been successful.

If([MERRecord - MERS Analyst]="",[MERRecord - MERS Analyst]&""&[PORecord - PO Analyst])

I want the value in the PORecord - PO Analyst field to populate in the MERRecord - MERS Analyst field when the MER Record - MERS Analyst field is blank.

------------------------------
Kristin Bombaci
------------------------------
Formulas and functions
  • AustinK's avatar
    AustinK
    Qrew Commander
    6 years ago
    The way I would handle this is to have the MER Record - MERS Analyst field only show when editing/adding the record. Then create a new formula text or user(this is my assumption) field that will be shown when viewing the record and use this formula. Have the formula field only show when viewing the record.

    If([MERRecord - MERS Analyst]="",[PORecord - PO Analyst], [MERRecord - MERS Analyst])

    This should result in the user being able to add text to the MER Record - MERS Analyst field when editing or creating the record but it will only display the formula field when they view it which will show whatever field is filled in but defaulting to the MERS Analyst when there is text in both. You may also want to put a little help text bubble on both fields to let people know that if nothing is entered there it takes the text/user from [PORecord - PO Analyst] and uses that.
    • KristinBombaci's avatar
      KristinBombaci
      Qrew Member
      6 years ago
      Thank you for your response Austin. I should clarify that I am trying to do this in a report, not in a form so the advice above would not apply. Do have a suggestion on a formula I can use in a report?

      ------------------------------
      Kristin Bombaci
      ------------------------------
      • AustinK's avatar
        AustinK
        Qrew Commander
        6 years ago
        That makes it less confusing with what needs to be shown on the forms. The formula I gave should work for a report as well. You just need to create a new formula field for it. 

        I can help explain what the formula from my last post is doing in words rather than code.

        Basically it is "If [MERRecord - MERS Analyst] is equal to blank, show [PORecord - PO Analyst], otherwise show [MERRecord - MERS Analyst]."