Discussions

 View Only
Expand all | Collapse all

Combine two text fields line by line

  • 1.  Combine two text fields line by line

    Posted 11-12-2017 02:59
    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


  • 2.  RE: Combine two text fields line by line

    Posted 11-12-2017 14:57
    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.


  • 3.  RE: Combine two text fields line by line

    Posted 11-12-2017 17:46
    That would not result in the line by line concat as above, based on my experience with excel.  


  • 4.  RE: Combine two text fields line by line

    Posted 11-12-2017 19:02
    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.


  • 5.  RE: Combine two text fields line by line

    Posted 11-12-2017 19:08
    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.


  • 6.  RE: Combine two text fields line by line

    Posted 11-12-2017 21:29
    The above is correct; each set of dates and alphas is in one cell, so extract and reload this way won't work.


  • 7.  RE: Combine two text fields line by line

    Posted 11-12-2017 17:58
    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.


  • 8.  RE: Combine two text fields line by line

    Posted 11-12-2017 22:11
    I used the shorter version, and it almost worked.  It only copied the first N/2 lines 


  • 9.  RE: Combine two text fields line by line

    Posted 11-12-2017 18:20
    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.


  • 10.  RE: Combine two text fields line by line

    Posted 11-12-2017 18:25
     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.




  • 11.  RE: Combine two text fields line by line

    Posted 11-12-2017 19:40
    Hmmmm, that totally worked.  There must have been a mistake in my own testing.


  • 12.  RE: Combine two text fields line by line

    Posted 11-12-2017 19:45
    Why not just go full on script? You will not have these moments of self doubt that come with native


  • 13.  RE: Combine two text fields line by line

    Posted 11-12-2017 21:25
    Typically, your script results cannot be used in reports or in other formulas.


  • 14.  RE: Combine two text fields line by line

    Posted 02-27-2018 23:26
    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!