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.exampleDates:01/01/200102/22/201003/13/1999AlphaCA 2000 1234567MU 1999 1234567TX 2001 1234567What I want is a final text field that looks like this:CA 2000 1234567 01/01/2001MU 1999 1234567 02/22/2010TX 2001 1234567 03/13/1999Once 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

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.

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.

I usually test all my answers before posting and it worked for me. Perhaps you would be more successful using a JavaScript solution - native can be tricky.

Combine two text fields line by line

KevinPlant
Qrew Cadet
7 years ago
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.
ROBERTSACHS
Qrew Cadet
7 years ago
That would not result in the line by line concat as above, based on my experience with excel.
- KevinPlant
  Qrew Cadet
  7 years ago
  I see no reason why not? You say that the two tables have an equal number of records. So place the two original data field columns side by side, enter the formula in at the head of column three and copy the formula down. Therefore the new field will be generated line by line.
- _anomDiebolt_
  Qrew Elite
  7 years ago
  This issue is that the multiline text for each field value would be in once cell. You would have to bust each multiline text value out into individual cells first to concatenate in the manner you describe.
- ROBERTSACHS
  Qrew Cadet
  7 years ago
  The above is correct; each set of dates and alphas is in one cell, so extract and reload this way won't work.
_anomDiebolt_
Qrew Elite
7 years ago
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.
- ROBERTSACHS
  Qrew Cadet
  7 years ago
  I used the shorter version, and it almost worked. It only copied the first N/2 lines
QuickBaseCoachD
Qrew Captain
7 years ago
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.
- _anomDiebolt_
  Qrew Elite
  7 years ago
  I usually test all my answers before posting and it worked for me. Perhaps you would be more successful using a JavaScript solution - native can be tricky.
- QuickBaseCoachD
  Qrew Captain
  7 years ago
  Hmmmm, that totally worked. There must have been a mistake in my own testing.
- _anomDiebolt_
  Qrew Elite
  7 years ago
  Why not just go full on script? You will not have these moments of self doubt that come with native.
ShaniceCaesar
Qrew Trainee
7 years ago
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!

Forum Discussion

Combine two text fields line by line