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?
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.
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?
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 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.
