Column with multiple URLs being combined

[R_Newell]

I have a table that captures urls to files from another app. This app accepts multiple files and the urls are listed in a single cell in the column, one per line. When I display the column data in AppSheet, the urls are squished together and made into one which breaks them.

Is there a way to display these in a list?

[Marc_Dillon]

R_Newell:

urls are listed in a single cell in the column, one per line.

You mean the URLs are all in one cell, separated by line breaks? Like this?

If so, the LongText column type is the only one that can handle line breaks in its value. So you’ll need to set the column as LongText. Then, in another column, you can SPLIT() them into a List, using a line break as the delimiter:

SPLIT( [urls] , "
" )

help.appsheet.com

SPLIT()

List from text

[R_Newell]

Marc_Dillon:

Thanks. That worked to break them out, but it does not create each as a clickable link in the app even though I set the element type to URL.

[graham_howe]

If you apply the hyperlink() function to the split values then that should make them clickable.

HYPERLINK()

New hyperlink

[CRS_R_Newell]

I need to do this in a new virtual column, correct? I tried it and just keep getting invalid inputs.

[CRS_R_Newell]

Should I apply that function in a separate column or within my split value column? This is how the split value column is set up:

 

[CRS_R_Newell]

It seems like there may be no way to accomplish this with clickable links...

[Koichi_Tsuji]

One possible workaround is to user Preview Feature, rich text. But this is no longer "No coding" but you construct html manually using virtual column with expressions like this.

substitute(
"<a href="&text('"')&Substitute(
[Enumlist]," , ", text('"')&">Link<a/> , <a href="&text('"'))
&text('"')&">Link</a>",
" , ",
"</br>"
)

Then turn on preview feature and make this VC to longtext with HTML enabled.

@takuya_miyai 

@Steve 

[Kirk_Masden]

I've had difficulty with using HTML links to URLs in rich text:

https://www.googlecloudcommunity.com/gc/Announcements/Rich-Text-Formatting-in-Preview-Program/m-p/35...

[Koichi_Tsuji]

Can you re-phrase your problem? to see if I can help.

[CRS_R_Newell]

Hello. Yes of course.

I have a table with a column that feeds multiple urls to files. These urls are one per line in one cell, like this:

Submission	URL to files
1	http://www.example.com/001.pdf http://www.example.com/002.pdf

When I display them as a file in AppSheet, the urls are combined, breaking both links:

http://www.example.com/001.pdfhttp://www.example.com/002.pdf

I would simply like to display the list of urls as clickable links, if possible.

I tried splitting them into a list then applying the HYPERLINK function in a new column, but I cannot seem to get valid inputs.

[Koichi_Tsuji]

Are you setting the column type to Text instead of Longtext?

[CRS_R_Newell]

Sorry I didn't see your earlier replies.

My initial file field is set to lontext

I was splitting them with this column:

[graham_howe]

I think we've all been looking at this problem from the perspective of a list in AppSheet terms. Could you try just entering a space between the links using substitute()? The expression would look like this

Substitute ([File Upload], "pdfhttp", "pdf http")

[graham_howe]

If this does at least display the URLs separated correctly, then we can go a stage further and wrap them in some html like this:

concatenate(

  "<ul><li><a href=" & text('"'),

  substitute( [File Upload], "pdfhttp",

  "pdf" & text('"') & ">Link</a></li><li><a href=" & text('"') & "http" ),

  text('"') & ">Link</a></li></ul>"
)

[CRS_R_Newell]

The substitute works, and also the listing code to separate each url out, but they're still not clickable.

[CRS_R_Newell]

I just noticed the "<ul><li><a" is showing in the app

[graham_howe]

Yes, this rich text formatting only works for LongText and is not really intended yet for editing. So if you are expecting users to modify these url lists you should look for a different approach. But the assumption here was the other application was generating the links in the cell and that they would be displayed in AppSheet through a VC.

[CRS_R_Newell]

Sorry, you are correct. The other app is generating the links. They will not be edited by the user.

Forgive me, I am sure I am missing something here.

[graham_howe]

Can you provide a screen shot of what you're seeing in the view?

[CRS_R_Newell]

Certainly.

This is with just the substitute:

And this is with concatenate

[graham_howe]

OK that second example doesn't look right, can you paste your expression code here, it might be something I wrote incorrectly or it could be a typo in your code. Just to be clear, the initial substitute example was just a test, that expression should be completely replaced by the concatenate one which include a nested substitution. If you are applying both expressions then it won't work. Also please confirm the column Type that this expression is going into is LongText, the html will not render correctly in anything else. 

[CRS_R_Newell]

Certainly 

In the first image it says text but that was from earlier. I set it to longtext before the screenshot

[graham_howe]

Interesting, I just performed a test and it worked fine, I imagine there must actually be some hidden character (like newline or similar) in the [File Upload] column which is preventing the substitution. Try the following which should work regardless:

concatenate(
  "<ul>", 
  substitute(
    substitute(
      [File Upload], 
      "pdf", 
      "pdf" & text('"') & ">Link</a></li>"
    ), 
    "http", 
    "<li><a href=" & text('"') & "http"
  ),
  "</ul>"
)

[CRS_R_Newell]

I appreciate your time and help so much. It is still printing the code.

[graham_howe]

OK this definitely works, I already had some rich text (generating a table) in one of my apps so I just added this code onto the bottom of my expression and got this:

The links do lead to different URLs and they are clickable. However, I wonder if we've missed the most important point? In all views other than Detail, the value will render as plain text. Are you by any chance using something other than a detail view?

Also this is a Preview Program feature, so you need to enable that:

- Go to UX > Options
- In the General section, turn on/off the toggle to “Preview new features”
- Save the change (Click ‘Save’)

[CRS_R_Newell]

My view was the default one after clicking the data in a table view. The table is called Form Responses and the view when clicked is called Form Reponses_Detail

[graham_howe]

and you have "Preview new features" enabled?

[CRS_R_Newell]

Yes

[graham_howe]

I'm sorry but I've run out of ideas. The expression is definitely producing the right text, for some reason your view is just not rendering it. The only other issue I know of is if the view is embedded in a dashboard then the rendering also doesn't work, but if you are just accessing the default view then that is exactly what I am doing. It might be time to reach out to support because I don't believe there is anything you are doing wrong.

[CRS_R_Newell]

Okay I will. Thanks again.

Where do I send your beer money??

[graham_howe]

OK I'm an idiot, I bet I know the problem and @Koichi_Tsuji even mentioned it in his post much earlier. You need to turn on HTML formatting for that column

[CRS_R_Newell]

It works! 

Bravo.

Beer?

[graham_howe]

Excellent! No beer required, I'm actually embarrassed that I missed something so obvious, but hopefully it is now useable.

By the way, if you know the maximum number of such links which might exist in a cell, and if the number is not too big, then it would be possible to create some more meaningful link text. Essentially you would go back to us splitting the text up into a list, and then working with each list item using index() to determine what to display. It could be as simple as Link 1, Link 2, Link3, or you could get really clever and start slicing the link URL up to extract the filename. It would be a fair bit more complicated, but it is possible.

[CRS_R_Newell]

Thanks. Yes I was thinking about how i will display the link in a nice way. I also need to factor in that there will be other file types other than PDF.

[clgielow]

This works beuatifully!  I would like to take you up on that offer to provide more meaningful link text. Could you assist me? This is completely new to me and I don't know where to start.

[Kirk_Masden]

Hi @Koichi_Tsuji ! If you are asking me to rephrase the problem, I described it in the post I linked to but the basic idea is that I the URL links didn't work properly on my phone.

[Koichi_Tsuji]

Yeah, i had a look at it, but i could not understand. that's why I asked you to re-phrase for me. Even in Japanese is fine! 😁

[Kirk_Masden]

I tried to illustrate the problem on my app but now it is working fine.  I'm not sure what happened before but I am no longer experiencing the problem.  Sorry to bother you. お騒がせしました。