Forum Discussion

Frontman's avatar
Frontman
Qrew Trainee
2 years ago

Join multi-line text field to one line

Hi all. I'm trying to find a way (either using a formula or in Pipelines) to join a multi-line text field to a single line.

Use case: The multi-line text field will contain text that will go to the JSON request body of an API call in Pipelines, so the field needs to be converted to a single line. The field has to a multi-line text field, because values can contain several paragraphs and app users can easily customize it.

Thanks!

Pipelines

5 Replies

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    2 years ago

    I'm no JSON expert but I know native Quickbase.  Can you provide an example of what the data now looks like and what it needs to look like?

    • Frontman's avatar
      Frontman
      Qrew Trainee
      2 years ago

      It could be any string that has multiple line breaks/spaces, and I need it to be converted into a single line without any line breaks. For example

      "blue
         green
            yellow"

      Convert to

      "blue green yellow"

      • PrashantMaheshw's avatar
        PrashantMaheshw
        Qrew Captain
        2 years ago

        line breaks are identified as \n . So you need to use jinja to replace \n with ' '.

        from QuickBase page 

        Return a copy of the value with all occurrences of a substring replaced with a new one. The first argument is the substring that should be replaced, the second is the replacement string. If the optional third argument count is given, only the first count occurrences are replaced:

        {{ "Hello\nWorld"|replace("\n", "") }}
        output: Goodbye World

        {{ a.myfield | replace('\n', '') }}



  • RicardoSendra__'s avatar
    RicardoSendra__
    Qrew Cadet
    12 days ago

    I have fought this issue many times and here is what I have found as I hope this helps others out!

    The issue is with non-printing characters & there are different ways of dealing with "new lines" as Windows, Mac & Unix/Linux variants can deal with this differently. 

    Some only add <carriage return>, <New Line> or Both of them at the end of each line. yet there are actually 3 possibilities:

    • Carriage return means to return to the beginning of the current line without advancing downward. The name comes from a printer's carriage, as monitors were rare when the name was coined. This is commonly escaped as "\r", abbreviated CR, and has ASCII value 13 or 0xD.
    • Linefeed means to advance downward to the next line; however, it has been repurposed and renamed. Used as "newline", it terminates lines (commonly confused with separating lines). This is commonly escaped as "\n", abbreviated LF or NL, and has ASCII value 10 or 0xA. CRLF (but not CRNL) is used for the pair "\r\n".
    • Form feed means advance downward to the next "page". It was commonly used as page separators, but now is also used as section separators. Text editors can use this character when you "insert a page break". This is commonly escaped as "\f", abbreviated FF, and has ASCII value 12 or 0xC.

     

    So you might be able to get away with a formula like this: (Replaces the "newline" with "|"
       SearchAndReplace(
          SearchAndReplace(
             SearchAndReplace( [Notes], "r\n", "|"),
          "r", "|"),
       "\n", "|")

    If that does not work, you have other non-printable characters at play so use this site to help you see what your text actually has:
    https://www.soscisurvey.de/tools/view-chars.php

    Then you would need to UUEncode the text to "see" all of the problem characters, then run a SearchandReplace() for EACH DIFFERENT non-printable character, then UUDecode it back to text.  It is not pretty but it works well to find and remove Non-printing ASCII, Unicode & emoji Characters which cause problems, if your users & upstream processes can add these characters.

    NOTEs:

    • Quickbase HTML processing will modify what is displays on the view form, to see what is actually in the field you must EDIT the record and get the data from the field.
    • There is some issue that I nor support have been able to identify, where unicode characters in a record will cause Connected tables to throw update errors with existing records. I have found that I have to delete the offending record in Quickbase to get the new data. Luckily is it not frequent that this happens for me.

     

    I hope this will point you to a solution.