Combine two text fields line by line

  • 0
  • 1
  • Question
  • Updated 9 months ago
  • In Progress
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
Photo of ROBERT SACHS

ROBERT SACHS

  • 390 Points 250 badge 2x thumb
  • Uncertain?

Posted 1 year ago

  • 0
  • 1
Photo of Kevin

Kevin

  • 260 Points 250 badge 2x thumb
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.
Photo of ROBERT SACHS

ROBERT SACHS

  • 390 Points 250 badge 2x thumb
That would not result in the line by line concat as above, based on my experience with excel.  
Photo of Kevin

Kevin

  • 260 Points 250 badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,272 Points 20k badge 2x thumb
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.
Photo of ROBERT SACHS

ROBERT SACHS

  • 390 Points 250 badge 2x thumb
The above is correct; each set of dates and alphas is in one cell, so extract and reload this way won't work.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,352 Points 20k badge 2x thumb
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.
(Edited)
Photo of ROBERT SACHS

ROBERT SACHS

  • 390 Points 250 badge 2x thumb
I used the shorter version, and it almost worked.  It only copied the first N/2 lines 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,352 Points 20k badge 2x thumb
 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.


Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
Hmmmm, that totally worked.  There must have been a mistake in my own testing.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,352 Points 20k badge 2x thumb
Why not just go full on script? You will not have these moments of self doubt that come with native
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
Typically, your script results cannot be used in reports or in other formulas.
(Edited)
Photo of Shanice Caesar

Shanice Caesar

  • 140 Points 100 badge 2x thumb
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!