Forum Discussion

SamW's avatar
SamW
Qrew Cadet
5 years ago

How to create lists of entries in a single field?

Currently, two of my tables include aliases for individuals and organizations. So at the top of each record I have the fields Name and Aliases. Name is just as regular text field, while Aliases is currently a multi-line field, as sometimes a person or organization has multiple different names/different spellings of names.

Users are supposed to separate each alias with a semi-colon. However, to my endless annoyance, many don't, using new lines or commas.

I was hoping for a workaround for this problem. I was thinking one solution would be a system to only allow the generation of one alias at a time, a bit like a multiselect field. However, a multiselect field would make everyone's aliases available to all the records, which I don't want.

Is there another option I'm not thinking of? Some other sort of way to create manual lists within a single field?

(I should add that aliases are not something we want to track in a separate table).



------------------------------
Sam
------------------------------
Formulas and functions
Relationships
Tables and fields
  • AustinK's avatar
    AustinK
    Qrew Commander
    5 years ago
    I know you said you do not want to track them in a separate table but this is exactly what that is for with a relationship. That way you could have an unlimited amount of aliases and also control exactly how they are written there.

    What is the reasoning for not wanting to do this in a separate table? Maybe there is an issue you have with it than can be overcome.
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      5 years ago
      Just to add to Austin's post above, the solution is to have the data entry be into a child table, as Austin suggested but then use a combined text summary field to float the alias names up to the parent so they can be seen on reports.  The words can be formatted to be vertically separated or with a semi colon or any separator.  That way they would be all standardized format.

      ------------------------------
      Mark Shnier (YQC)
      Quick Base Solution Provider
      Your Quick Base Coach
      http://QuickBaseCoach.com
      mark.shnier@gmail.com
      ------------------------------
  • ForrestParker2's avatar
    ForrestParker2
    Qrew Trainee
    5 years ago
    One think you might do is just anticipate that they will use characters other than a semi-colon and replace them with the semi-colon.

    The formula for the "Aliases (formula)" field walks through each anticipated incorrect separator and replaces it with what we want.

    var text replaceCommasAndSpaces = SearchAndReplace([Aliases (separate with ";")],", ",";");
    var text replaceSpacesAndCommas = SearchAndReplace($replaceCommasAndSpaces," ,",";");
    var text replaceCommas = SearchAndReplace($replaceSpacesAndCommas,",",";");
    var text replaceSemiColonsAndSpaces = SearchAndReplace($replaceCommas,"; ",";");
    var text replaceSpaces = SearchAndReplace($replaceSemiColonsAndSpaces," ",";");
    var text addBackSpacesAfterSemiColon = SearchAndReplace($replaceSpaces,";","; ");

    $addBackSpacesAfterSemiColon​


    ------------------------------
    Forrest Parker
    ------------------------------