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
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
 440 Points
 Uncertain?
Posted 1 year ago
 260 Points
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.
 440 Points
That would not result in the line by line concat as above, based on my experience with excel.
 260 Points
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.
Ⲇanom the ultimate (Dan Diebolt), Champion
 29,044 Points
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.
 440 Points
The above is correct; each set of dates and alphas is in one cell, so extract and reload this way won't work.
Ⲇanom the ultimate (Dan Diebolt), Champion
 29,044 Points
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 rechannel your userbase's resourcefulness from coming up with workarounds like this to working with more expressive product features. Replace the formula language with script.
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 rechannel your userbase's resourcefulness from coming up with workarounds like this to working with more expressive product features. Replace the formula language with script.
(Edited)
 440 Points
I used the shorter version, and it almost worked. It only copied the first N/2 lines
QuickBaseCoach App Dev./Training, Champion
 57,702 Points
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 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.
Ⲇanom the ultimate (Dan Diebolt), Champion
 29,044 Points
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.
QuickBaseCoach App Dev./Training, Champion
 57,702 Points
Hmmmm, that totally worked. There must have been a mistake in my own testing.
Ⲇanom the ultimate (Dan Diebolt), Champion
 29,044 Points
Why not just go full on script? You will not have these moments of self doubt that come with native.
QuickBaseCoach App Dev./Training, Champion
 57,702 Points
 140 Points
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!
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!
Related Categories

Formulas & functions
 2760 Conversations
 64 Followers

Tables & fields
 7022 Conversations
 153 Followers

Tips & tricks
 310 Conversations
 24 Followers