Auto Formating Text Fields

  • 0
  • 1
  • Question
  • Updated 8 months ago
  • Acknowledged
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...
Photo of Dan Park

Dan Park

  • 410 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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.
Photo of Donna Troestler

Donna Troestler

  • 324 Points 250 badge 2x thumb

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?

Photo of Dan Park

Dan Park

  • 410 Points 250 badge 2x thumb
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?
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
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.