- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 06, 2022 01:55 AM
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 06, 2022 07:00 AM
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 06, 2022 08:20 AM
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 06, 2022 08:52 AM
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 06, 2022 09:44 AM
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