Discussions

Expand all | Collapse all

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

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

    Posted 03-09-2020 14:07
    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
    ------------------------------


  • 2.  RE: Can I concatenate two fields into one when one field is blank?

    Posted 03-09-2020 15:46
    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.



  • 3.  RE: Can I concatenate two fields into one when one field is blank?

    Posted 03-09-2020 16:08
    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
    ------------------------------



  • 4.  RE: Can I concatenate two fields into one when one field is blank?

    Posted 03-09-2020 16:41
    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]."


  • 5.  RE: Can I concatenate two fields into one when one field is blank?

    Posted 30 days ago
    I created the new field and used the formula you provided and it works exactly as I was hoping it would! Thank you for all your help Austin!

    ------------------------------
    Kristin Bombaci
    ------------------------------