Concat subfolder with file results in full URL? Expected?

AppSheet AppSheet Q&A
1

It has been a while since I’ve needed to insert a path + filename into a column to open generated file. I am seeing why others are having some issues.

I have a folder into which generated PDF’s are placed. I then pull that folder into the app as a source to a Reports table. I add Virtual Columns that extract row identification from the filename so the file can be attached to the data row generating the file.

I am having trouble computing the correct path + filename. If I simply use an expression like this:

ANY(SELECT(Inspection_Reports[File], [_ID] = MAXROW("Inspection_Reports", "Completion Date", [Inspection] = [_THISROW].[Inspection ID])))

The File column is assigned this file name:

de4a62c7_447845a8_20221011_140850_524.pdf

HOWEVER, if I use that same expression and attempt to append the proper subfolder name with this expression (or even with “&”) like below:

CONCATENATE("Inspection_Reports/", 
            ANY(SELECT(Inspection_Reports[File], 
                    [_ID] = MAXROW("Inspection_Reports", "Completion Date", [Inspection] = [_THISROW].[Inspection ID]))))

I get returned a “gettablefileurl” constructed like this:

“Inspection_Reports/https://www.appsheet.com/template/gettablefileurl?appName=PostConstructionInspections-1969021&tableName=Inspection_Reports&fileName=de4a62c7_447845a8_20221011_140850_524.pdf&appVersion=1.000896&signature=761913f3787c6516ca8039c593be0cffad5638bafe3754320dd1495ab21475d960b02db93e3be5d02b6d46d401c0de7e”

Does anyone know if this is expected behavior? Or do you agree this is likely a bug?

EDITED: I was able to resolve this by simple writing the [File] column into a Text based Virtual Column. But do I really need to do that?

2

thats exactly what I did to show an action in order to open a generated file. I believe @Steve mentioned in othetr post why this ‘“gettablefileurl”’ appears, but it had its purpose.

3

I think is expected based on the column type of the column you are asking the file/files from.

I use this method to populate an EnumList basetype File to send multiple attachments via email and the data in the worksheet is a list of URLs

1 Like
4

Maybe I didn’t explain the problem well?

If I use an ANY(SELECT()) - I get back just the file name.

If I try to append a subfolder to the ANY(SELECT())- I get back the long “gettablefileurl”.

I can’t think of any reason why this would be expected behavior but I don’t use web or cloud file locations often enough to know.

5

Some ideas to further troubleshoot this:

  1. What happens when you use just CONCATENATE() without the subfolder? My guess is that you get the same result
  2. What if you change ANY() with INDEX(x, 1)?
  3. This happends regardless of your column type?
6

Some good suggestions. Here are the results

If I use CONCATENATE by itself OR append “”, I get back just the filename. If I append space or any other printable character, with CONCATENATE or the “&” shortcut, I get the “gettablefileurl”

INDEX behaves the same

I tried Text column type and get the same behavior.

7

thats the url of your PDF. As @Steve once said, the App generate this temporally (the ‘signature’ part change after that) url to open your file and protect it.

So its OK to get what you said. when a column formula unite the path+file name you get:

  • file/sub file/filename.extension in the sheet
  • url when clicking the action button
8

The URL itself is not the problem. I know I haven’t done a good job explaining, so I’ll be a little more succinct.

If I do this: ANY(SELECT(Inspection_Reports[File]…)) I get this: de4a62c7_447845a8_20221011_140850_524.pdf

BUT, if I do this “Inspection_Reports/” & ANY(SELECT(Inspection_Reports[File]…)) then I get this (note the red portion):

“Inspection_Reports/https://www.appsheet.com/template/gettablefileurl?appName=PostConstructionInspections-1969021&tableName=Inspection_Reports&fileName=de4a62c7_447845a8_20221011_140850_524.pdf&appVersion=1.000896&signature=761913f3787c6516ca8039c593be0cffad5638bafe3754320dd1495ab21475d960b02db93e3be5d02b6d46d401c0de7e”

9

Just for the sake of your mental health, it seems like a bug indeed, or at least it’s not very clear why AppSheet would decide to use a URL on one side instead of the other.
You found a workaround with an extra VC right?

Also, try this:

CONCATENATE(
 "SubfolderPath/",
 CONCATENATE(
  ANY(SELECT....
 )
)
10

Maybe its because your are ‘calling’ a text in wich, first you have the path (Inspection_Reports/") plus the result of the value in the column from the ‘select’.

So the result of that column Inspection_Reports[File] is the route of the file.: (https://www.appsheet.com/template/gettablefileurl?appName=PostConstructionInspections-1969021&tableN…" )

thats why you get

“Inspection_Reports/https://www.appsheet.com/template/gettablefileurl?appName=PostConstructionInspections-1969021&tableN…”

Do as @SkrOYC suggest. I do the same and dosent get that ‘anomaly’.

11

I have a workaround already. I’m was trying to determine if this is expected behavior. I don’t believe it is. I will open a report to AppSheet support.

1 Like
12

It is. Image and File columns are magic and cannot be used as textual values.

2 Likes
13

Could you check if it’s working as you expect on your side? Because now it’s not on mine (which the opposite of you, I need the URL)