Re: Concat subfolder with file results in full URL...

WillowMobileSys · 10-11-2022 07:57 AM

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?

Luis_Rodriguez_

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.

SkrOYC

@WillowMobileSys wrote:
Does anyone know if this is expected behavior?

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

WillowMobileSys

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.

SkrOYC

Some ideas to further troubleshoot this:

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

WillowMobileSys

Some good suggestions. Here are the results

@SkrOYC wrote:

What happens when you use just CONCATENATE() without the subfolder? My guess is that you get the same result

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"

@SkrOYC wrote:

What if you change ANY() with INDEX(x, 1)?

INDEX behaves the same

@SkrOYC wrote:
This happends regardless of your column type?

I tried Text column type and get the same behavior.

Luis_Rodriguez_

@WillowMobileSys wrote:
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"

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

WillowMobileSys

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&tableN..."

SkrOYC

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....
 )
)

Luis_Rodriguez_

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'.

WillowMobileSys

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.

Steve

@WillowMobileSys wrote:
Does anyone know if this is expected behavior?

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

SkrOYC

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)

Concat subfolder with file results in full URL? Expected?