Formula Text field based on multiple fields

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered

I have a form which contains a field called "System Intake Category" which has 15 choices.  If one of those choices are selected, a corresponding field populates which has a list of applications for that given system.  For reporting purposes I created a formula text field that I want to populate with whatever application was chosen, regardless of the system.  For the formula I used an If statement with not IsNull.  The problem is that the field only populates a name if the application field for the first option is not null.  Even if one of the other systems is not null, it's not showing that name.   I've copied the formula if someone can provide feedback on what I've done wrong.

If(
not IsNull([Advanced Solution System]), ToText([Advanced Solution System]),
not IsNull([Back Office Support System]), ToText([Back Office Support System]),
not IsNull([Chat System]), ToText([Chat System]),
not IsNull([Contact Relation Management (CRM) System]), ToText([Contact Relation Management (CRM) System]),
not IsNull([Customer Support Tool]), ToText([Customer Support Tool]),
not IsNull([Desktop Automation/Workflow System]), ToText([Desktop Automation/Workflow System]),
not IsNull([DirecTV and U-verse Billing System]), ToText([DirecTV and U-verse Billing System]),
not IsNull([Knowledge Management System]), ToText([Knowledge Management System]),
not IsNull([Legacy Wireline System]), ToText([Legacy Wireline System]),
not IsNull([Mobility Billing System]), ToText([Mobility Billing System]),
not IsNull([Ordering System]), ToText([Ordering System]),
not IsNull([Payment System]), ToText([Payment System]),
not IsNull([Retail Support System]), ToText([Retail Support System]),
not IsNull([Technical Support System]), ToText([Technical Support System]),
not IsNull([Third Party System]), ToText([Third Party System]))

Photo of Michael Sargent

Michael Sargent

  • 150 Points 100 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
While not intuitive, the IsNull is documented not to be usable with text fields.  To test for bank you need to use a test looking for empty quotes. The Trim is optional, but its a good idea if there is a possibility that the user has typed an invisible  space into the text field. 


If(
Trim([Advanced Solution System])<>"", ToText([Advanced Solution System]),
Trim([Back Office Support System])<>"", ToText([Back Office Support System]),
etc
Photo of Michael Sargent

Michael Sargent

  • 150 Points 100 badge 2x thumb

That did it.  Thank you so much!

Photo of Michael Barrow

Michael Barrow

  • 2,206 Points 2k badge 2x thumb
Does this pretty much do the same thing as Length([TextField]) = 0 ?