Discussions

Expand all | Collapse all

Field Names with Brackets

  • 1.  Field Names with Brackets

    Posted 01-27-2020 13:20
    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
    ------------------------------


  • 2.  RE: Field Names with Brackets

    Posted 01-27-2020 14:29
    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
    ------------------------------



  • 3.  RE: Field Names with Brackets

    Posted 01-27-2020 14:42
    This worked perfectly! Thank you so much!

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



  • 4.  RE: Field Names with Brackets

    Posted 9 days 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
    ------------------------------



  • 5.  RE: Field Names with Brackets

    Posted 9 days 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
    ------------------------------



  • 6.  RE: Field Names with Brackets

    Posted 9 days ago
    That did it!  Thank you!

    ------------------------------
    Marc Barry
    ------------------------------



  • 7.  RE: Field Names with Brackets

    Posted 01-27-2020 23:03
    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
    ------------------------------



  • 8.  RE: Field Names with Brackets

    Posted 01-27-2020 23:45
    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
    ------------------------------