Formula Field Return Multiple Combinations of Responses

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

Can a formula field return the name of all of the fields below that return the value as true? For example if the [Actual Buy Cost] and [Fully Executed Purchase Agreement] returned as true, I would want the field to read as Actual Buy Cost, Full Executed Purchase Agreement. Essentially it listed everything that is missing. 

[Actual Buy Cost]<>0, 

[Actual Add Buy Cost]<>0, 

[Fully Executed Purchase Agreement]="", 

[Executed Purchase Closing Package]=""

Photo of Aaron

Aaron

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 800 Points 500 badge 2x thumb
Create a Formula Text field called [Missing Items], and use the following formula:

List (", ",

If ([Actual Buy Cost] = 0, "Actual Buy Cost"),

If ([Actual Add Buy Cost] = 0, "Actual Add Buy Cost"),

If ([Fully Executed Purchase Agreement] = "", "Fully Executed Purchase Agreement"),

If ([Executed Purchase Closing Package] = "", "Executed Purchase Closing Package")

)

This puts a list with commas in between, and has a separate If statement for each item you're testing for.  If the test isn't true, then the item isn't added to the list.
Photo of Aaron

Aaron

  • 0 Points
This is the formula I am trying. Its returning an Error

List (", ",



If ([Actual Buy Cost] <> 0, "Actual Buy Cost"),



If ([Actual Add Buy Cost] <> 0, "Actual Add Buy Cost"),



If ([Fully Executed Purchase Agreement] = "", "Fully Executed Purchase Agreement"),



If ([Executed Purchase Closing Package] = "", "Executed Purchase Closing Package"),



If ([Purchase Title Work]="", "Purchase Title Work"),



If ([Main Photo]="", "Main Photo"),



)
Photo of Xavier Fan

Xavier Fan, Champion

  • 800 Points 500 badge 2x thumb
Remove the comma at the end of the last item:

If ([Main Photo]="", "Main Photo")
Photo of Aaron

Aaron

  • 0 Points
Working great! Thank you
Photo of Xavier Fan

Xavier Fan, Champion

  • 800 Points 500 badge 2x thumb
Great - happy to help!