Forum Discussion

DanPark1's avatar
DanPark1
Qrew Cadet
9 years ago

Auto Formating Text Fields

Is there a way to auto format text that is entered on a form?

Example...
Say I have a form with fields First Name and Last Name

The user then fills in First Name with  "JOhN" and Last Name with "  SmITh                         "

I would like the fields to get formatted properly with trimming.
First Name: "John"
Last Name: "Smith"

Would like to do with to 5-7 text fields and trying to do this the cleanest way possible using as few fields as possible OR a webhook...
Please advise...
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    9 years ago
    I use the two fields and combine them into a [Full Name] field that formats it as needed.

    Its a tricky formula, but here it is:

    Upper(Left([First Name], 1))&""&Lower(Right([First Name], Length([First Name])-1)) &" "& Upper(Left([Last Name], 1))&""&Lower(Right([Last Name], Length([Last Name])-1))

    I then only use the full name field in reports and all other places in the app, the first and last name fields are only used for the edit / add forms

    Note: If they have 2 last Names, it will put the second last name in all lowercase.
    • DonnaTroestler's avatar
      DonnaTroestler
      Qrew Cadet
      7 years ago

      Thanks for this formula! Have you subsequently figured out a way to get the first letter of the second last name capitalized, rather than in lowercase?

  • DanPark1's avatar
    DanPark1
    Qrew Cadet
    9 years ago
    Thanks for the reply but that doesnt really answer my question. I'm not looking to combine/create a full name. I'm looking prevent messy data from getting into the database. Is there a way to do that without having to create a 2nd field for EVERY field I want to clean up?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    Just chiming into Matthew's thread here :)

    You can have a formula field for every field to be cleaned up which i know you don't want to do) and have a form rule:

    when the record is saved,
    Change the name field to the value in the field [name formatted formula]

    You would need to have those formula fields on the form even if they are in a hidden section.

    But yes, you need to make those formula fields.  This could also be supported by a Webhook, but once again,, QuickBase cannot read your mind as to the format.

    The president of one of my client companies is Ed duDomain.  You may need to have a checkbox to not change the data entry for unusually capitalized names.