Skip to main content

Formula Error issue

  • January 6, 2022
  • 4 replies
  • 14 views
K

Hi

Sorry if this seems dumb, but im a complete beginner.

I have a task id field with a simple concatenate formula that puts together three fields data in the same table. And shows {Customer Name} - {Type of Task} - {Date of Task} and it adds the date time format D/M/YYYY.
Its shows eg: John Smith - Photoshoot - 21/7/2022

This works brilliantly, but has a very minor annoyance of returning ERROR when adding a record because the three fields are blank. Once the fields are filled in it works a treat.

Is there any way the formula can allow for the blank fields without showing error?

Thank you in advance

4 replies

W
Forum|alt.badge.img+12

Hi @Keith_Landale,

Welcome to the community! Also, there is NO dumb question and thank you for posting this. I’ve seen this many times and it can be a pain. My recommendation is to include an “if” statement. It will depend on how you want it to act, however, the one below can get you started.

  • if(AND({Customer Name}="",{Type of Task}="",{Date of Task}=""),"",{Customer Name} - {Type of Task} - {Date of Task})
    Note that using the “AND” will return a blank in your formula field until ONE of the other fields is populated. If you replace the “AND” with “OR”, then ALL fields must be populated before the formula returns a value.

Hope this helps.
Chris

K
  • Keith_Landale
  • Author
  • New Participant
  • January 6, 2022

Hi @Keith_Landale,

Welcome to the community! Also, there is NO dumb question and thank you for posting this. I’ve seen this many times and it can be a pain. My recommendation is to include an “if” statement. It will depend on how you want it to act, however, the one below can get you started.

  • if(AND({Customer Name}="",{Type of Task}="",{Date of Task}=""),"",{Customer Name} - {Type of Task} - {Date of Task})
    Note that using the “AND” will return a blank in your formula field until ONE of the other fields is populated. If you replace the “AND” with “OR”, then ALL fields must be populated before the formula returns a value.

Hope this helps.
Chris


Thank you for your help so far but I cant get the formula to work, either on its own, or placed before or after my current formula.

My current formula is:-

CONCATENATE({Customer Name},” - “,{Type of Task},” - “,DATETIME_FORMAT({Date of Task}, “D/M/YYYY”))

Where do I copy your formula to make the whole thing work?

Thank you

W
Forum|alt.badge.img+12

Hi @Keith_Landale,

Thanks for pointing this out. I should’ve specified were your formula went.

  • if(AND({Customer Name}="",{Type of Task}="",{Date of Task}=""),"",your formula)
    or
    if(AND({Customer Name}="",{Type of Task}="",{Date of Task}=""),"",CONCATENATE({Customer Name},” - “,{Type of Task},” - “,DATETIME_FORMAT({Date of Task}, “D/M/YYYY”)))

This should alleviate the “ERROR” you were getting.

Thanks,
Chris

K
  • Keith_Landale
  • Author
  • New Participant
  • January 6, 2022

Hi @Keith_Landale,

Thanks for pointing this out. I should’ve specified were your formula went.

  • if(AND({Customer Name}="",{Type of Task}="",{Date of Task}=""),"",your formula)
    or
    if(AND({Customer Name}="",{Type of Task}="",{Date of Task}=""),"",CONCATENATE({Customer Name},” - “,{Type of Task},” - “,DATETIME_FORMAT({Date of Task}, “D/M/YYYY”)))

This should alleviate the “ERROR” you were getting.

Thanks,
Chris


That is fantastic Chris!
It works perfectly.
I have gone with the OR instead of AND.
It looks much cleaner.

Thank you for your help

Keith

W
Terms & ConditionsAccessibility statement