Skip to main content

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!

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")

)

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!

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)

)

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)

)


Thanks again, this has been so useful!

Reply


Terms & Conditions