Forum Discussion

TrishWehrle's avatar
TrishWehrle
Qrew Cadet
5 years ago

Ampersand messing up formula/form rule

I think having an '&' in my field is messing things up. Help

I have a formula text field 'Department Formula' that works great:
If(Contains([Employee/Contractor - Department],"Inflight Services"),"Inflight",
Contains([Employee/Contractor - Department],"Flight Operations"),"Flight Ops",
Contains([Employee/Contractor - Department],"Ground Operations"),"Ground Ops",
Contains([Employee/Contractor - Department],"Cargo"),"Cargo",
Contains([Employee/Contractor - Department],"Customer Support & Services"),"CS&S",
Contains([Employee/Contractor - Department],"Technical Operations"),"Tech Ops",
Contains([Employee/Contractor - Department],"Network Operations Control"),"NOC","Other")

And a multiple choice field 'Department' with these options:
Inflight
Flight Ops
Ground Ops
Cargo
CS&S
Tech Ops
NOC
Other

And a form rule when employee name is not blank, 'Department' is equal to the value in field 'Department Formula'. It works perfectly for all selections except CS&S. When the Employee is in that department, the 'Department Formula' field displays CS&S as it should. But the 'Department' field is blank.
Is it the & thats messing things up? Is there a way around it?
Thanks!

------------------------------
Trish Wehrle
------------------------------
Formulas and functions
  • BrianCameron's avatar
    BrianCameron
    Qrew Cadet
    5 years ago
    Trish,
    Check to see if there are preceding or trailing spaces in the "CS&S" value in the multiple choice field.  The only reason I can think that your form rule is not matching the Multiple Choice field value and the Formula Value.
    Brian

    ------------------------------
    Brian Cameron
    ------------------------------
  • BlakeHarrison's avatar
    BlakeHarrison
    Qrew Captain
    5 years ago
    Most likely, the ampersand is causing an issue, though I would suggest an alternative approach.

    It looks like you've already setup the Department as a Lookup from the Employee record. If you haven't already, I would set that Department field on the Employee table as its own table. Then, in the Departments table, add a new field for "Short Name", where you would enter the values that you're having the formula currently create. Once you have that done, you can simply bring the "Short Name" field down as a lookup as well and replace the current Formula / Multiple Choice Drop-down setup with the new lookup field.

    ------------------------------
    Blake Harrison
    bharrison@datablender.io
    DataBlender - Quickbase Solution Provider
    Atlanta GA
    404.800.1702 / http://datablender.io/
    ------------------------------