Expand all | Collapse all

Formula Text field based on multiple fields

  • 1.  Formula Text field based on multiple fields

    Posted 06-16-2017 14:11

    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.

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

  • 2.  RE: Formula Text field based on multiple fields

    Posted 06-16-2017 14:49
    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. 

    Trim([Advanced Solution System])<>"", ToText([Advanced Solution System]),
    Trim([Back Office Support System])<>"", ToText([Back Office Support System]),

  • 3.  RE: Formula Text field based on multiple fields

    Posted 06-16-2017 14:54

    That did it.  Thank you so much!

  • 4.  RE: Formula Text field based on multiple fields

    Posted 06-19-2017 19:23
    Does this pretty much do the same thing as Length([TextField]) = 0 ?