Forum Discussion

OanaWhalen's avatar
OanaWhalen
Qrew Cadet
6 years ago

Field Names with Brackets

I am importing data from a system that kicks out a csv file with pre-defined column headings. Some of those headings include square brackets, such as photo-hyperlink[0]. I will be importing many of these csv's over the course of the project, and need Quick Base to automatically import these data fields using the standard Import from File function, so I believe I have to keep the Quick Base field name the same as the column heading. This works fine until I need to use that field in a formula, and then Quick Base gets confused because it thinks the field name is "[0]" instead of "photo-hyperlink[0]". I have no control over the column headings in the csv and do not have the ability to manipulate the csv before importing it into Quick Base within our workflow.

Is there any way to get Quick Base to recognize the full field name with the square brackets included in formulas? The only option I can think of would be to have an automation copy the text to another field when the data is imported, but I have 60 of these fields so if there is a more efficient way, I'd prefer to avoid setting up and repeatedly running this many actions.

Thanks,
Oana

------------------------------
Oana Toma
------------------------------
Ask about Quickbase functionality
Data import and export
Tables and fields
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    Yes, I ran into this once where a client chose to label fields with square brackets because he liked the look. (!!!)
    I don't have time to test immediately, but I believe that we had to "escape" the closing ] with a \

    like

    formula for extended sales price
    [qty\]] * [price\]]

    try this on a test app where the actual fields names are [qty] and [price] with the square brackets and if it does not work like I showed, then i will test.







    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • OanaWhalen's avatar
      OanaWhalen
      Qrew Cadet
      6 years ago
      This worked perfectly! Thank you so much!

      ------------------------------
      Oana Toma
      ------------------------------
    • MarcBarry3's avatar
      MarcBarry3
      Qrew Trainee
      5 years ago
      Hi Mark,
        I have a similar issue, where the brackets are in the field name.  Here's an example of the field name:  
      291 Core Subjects EC-6 - 802 MATH- Requires ProctorU LockDown Browser [Total Pts: 40 Score] |289212

      I did not include the brackets on either side so you can see the actual field name being imported from an external source.  I have another field that simply looks at this field (numeric formula) so I can use it in other formulas.  How would escape work with this field?  I'd appreciate any help and thanks!

      Marc

      ------------------------------
      Marc Barry
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        5 years ago
        This tested OK:

        Try this using  this is a formula


        [291 Core Subjects EC-6 - 802 MATH- Requires ProctorU LockDown Browser [Total Pts: 40 Score\] |289212]

        note the backslash escaper character \ before the closing ]

        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    6 years ago
    Oana,

    How stable is the naming convention and export routing from the other system?

    If it is internal and the IT department is your friend, then the escape will serve you well.  If this is coming from an external source and is subject to change you may want to consider building a normalization application to import your CSV and then have that write data to your business system.  

    I have got a client with many suppliers that send in data all formatted in horrible ways.   They import the CSV data where formula fields massage each one to a normalized format.  Then we move the data from QB App One to QB App Two.

    The key here is when the suppliers make a change, we only have to update the formula fields in QB App One to make the normalization routine correct again.  The older data in QB App Two is never disturbed by changes in the CSV or formula fields.

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------
    • OanaWhalen's avatar
      OanaWhalen
      Qrew Cadet
      6 years ago
      Don,

      Your approach is interesting. In our case, the system we are getting data from is an in-house mobile application that I know will not change naming conventions for the duration of this project, so I am comfortable with the workaround for the short term. The in-house application is being phased out though, so I like your idea of having a converter application in Quick Base that can be updated when the new version comes out. I have previously used a Python script to normalize our data but did not want to have to go through a separate program to get our data into Quick Base among other challenges. I had not considered having a separate app do the normalizing, and will definitely give that a try for our next project. It seems like a much cleaner way to make a lot of the data processing happen that the user never needs to see.

      Thanks for the suggestion!

      ------------------------------
      Oana Toma
      ------------------------------