Jan 23, 2022 06:34 AM
Hi there,
Lets say I have four fields:
A
B
C
D
I would like to create a single formula field that could show which of the above fields are blank.
So, for example:
If only A is blank, the field should say “Field A is blank”
If A and B are blank, the field should say “Field A and B are blank”
And so on …
I have been able to create a formula that tracks whether all the fields are blank, but I am struggling to find a way to use the formula field to display which specific fields are blank.
If anyone has any advice, that would be much much appreciated!
Solved! Go to Solution.
Jan 23, 2022 07:09 AM
Having a formula that produces a natural language sentence requires a lot of complex logic that is hard to maintain. It is much easier to create an independent sentence for each item.
CONCATENATE(
IF( NOT({A} & ""), "A is blank\n"),
IF( NOT({B} & ""), "B is blank\n"),
IF( NOT({C} & ""), "C is blank\n"),
IF( NOT({D} & ""), "D is blank\n")
)
Jan 23, 2022 07:09 AM
Having a formula that produces a natural language sentence requires a lot of complex logic that is hard to maintain. It is much easier to create an independent sentence for each item.
CONCATENATE(
IF( NOT({A} & ""), "A is blank\n"),
IF( NOT({B} & ""), "B is blank\n"),
IF( NOT({C} & ""), "C is blank\n"),
IF( NOT({D} & ""), "D is blank\n")
)
Jan 26, 2022 04:46 AM
Thank you so much, this is so useful.
Can I ask, if I wanted to create a formula that would count how many blanks are blank, how would I go about doing that? Is there a way to insert a counter into a formula that would increment each time specific criteria are met (e.g., in this case, that the field is blank)?
I really appreciate your help!
Jan 26, 2022 04:49 AM
You can add up the number of blanks. Use the same method to tell if a field is blank.
SUM(
IF( NOT({A} & ""), 1),
IF( NOT({B} & ""), 1),
IF( NOT({C} & ""), 1),
IF( NOT({D} & ""), 1)
)
Jan 26, 2022 05:25 AM
Thanks again, this has been so useful!