Skip to main content
Question

Combine two text fields line by line

  • November 12, 2017
  • 0 replies
  • 199 views
Forum|alt.badge.img+6
I want to combine text from two fields on a line by line basis.

I have two multiline text fields, call them "Dates" and "Alpha". 

"Dates" is a column of dates, formatted "MM/DD/YYYY"

"Alpha" is a column of alphanumerics, formatted "CC YYYY nnnnnnnnn" (where there are a variable number of n's).

The fields always have the same number of rows.

example

Dates:
01/01/2001
02/22/2010
03/13/1999

Alpha
CA 2000 1234567
MU 1999 1234567
TX 2001 1234567

What  I want is a final text field that looks like this:

CA 2000 1234567   01/01/2001
MU 1999 1234567   02/22/2010
TX 2001 1234567   03/13/1999

Once the final text field is created, I want to delete the source fields from the table.

The database already has 1500 records, so I don't want to risk loss of data unless I know this will work.

Thanks
    This topic has been closed for replies.

    • KevinPlant
    • Registered
    • November 12, 2017
    Hi, i think the easiest way to achieve this would be to export both table to excel, create a concatenated field from the two data fields datafield1&� �&datafield2 and the import the new field back into quickbase. No risk of any data loss.
    Forum|alt.badge.img+6
    • ROBERTSACHS
    • Author
    • Registered
    • November 12, 2017
    That would not result in the line by line concat as above, based on my experience with excel.  
    You can create a new formula text field using this formula:

    List("\n",
      List(" ", Part([Alpha], 1, "\n"), Part([Dates], 1, "\n")),
      List(" ", Part([Alpha], 2, "\n"), Part([Dates], 2, "\n")),
      List(" ", Part([Alpha], 3, "\n"), Part([Dates], 3, "\n")),
      List(" ", Part([Alpha], 4, "\n"), Part([Dates], 4, "\n")),
      List(" ", Part([Alpha], 5, "\n"), Part([Dates], 5, "\n"))
    )

    Once you confirm this produces the desired result, you can convert the text formula field to a text field and delete the original [Alpha] and [Dates] fields.

    Notes:

    (1) You have to implement this formula assuming a specific maximum on the number of lines in your data (n=5 for my formula).

    (2) The List() function is used to avoid adding spaces if [Alpha] or [Dates] don't have the same number of lines. If you do have the same number of lines in all your data, you can use this shorter formula:

    Part([Alpha], 1, "\n") & " " & Part([Dates], 1, "\n") & "\n" &
    Part([Alpha], 2, "\n") & " " & Part([Dates], 2, "\n") & "\n" &
    Part([Alpha], 3, "\n") & " " & Part([Dates], 3, "\n") & "\n" &
    Part([Alpha], 4, "\n") & " " & Part([Dates], 4, "\n") & "\n" &
    Part([Alpha], 5, "\n") & " " & Part([Dates], 5, "\n")

    (3) I have to tell you that I hesitate to answer using native formulas as I feel like I am enabling bad practices. The formula language is very limited and in today's world you should not have to make assumptions such as the maximum number of instances of some characteristic. The equivalent JavaScript implementation is much more succinct and covers more cases than your particular [Alpha] and [Dates] fields. In the most helpful way but using strong language I have to tell QuickBase to enter the modern world - the product will be a lot more successful if you re-channel your userbase's resourcefulness from coming up with workarounds like this to working with more expressive product features. Replace the formula language with script.
    Dan
    I tried that and it did not work to try to trap a carriage return using \n, so I did not post a response. I was stuck.
    Hi all - please please help 

    I also have two fields I need to be displayed side by side. 
    I have tried the above formulas and none seem to work for me. Can someone advise me (no coding experience whatsoever) if I need to do more than to simply copy and paste the above formula and replace the [Alpha] and [Dates] fields with the fields I wish to display to get it to work? 

    My error message says: 

    Formula error -- Bad or missing arguments in function call

    The types of the arguments or the number of arguments supplied do not meet the requirements of the function Part.

    The function is defined to be Part (Text t, Number p, Text d).

    Thanks so much in advance!
    Terms & ConditionsAccessibility statement