Creating a subfolder using an expression for the FILE column

Struggling to get an expression working that will create a sub-folder based on the user’s ID. This is a subfolder to default based on Table name. Namely:

Table Documentation
Default folder for a ‘file’ is Documentation_Files
User ID is [PropertyUUID].[OwnerUUID] e.g: e60a348f

So the whole folder structure would look like:

Documentation_Files /e60a348f/a0c18248.File.174240.pdf.pdf

What actually gets created is:

Documentation_Files_/a0c18248.File.174240.pdf

DocumentURL expression
The reason for using default location is to make it easier to create generate the Document URL for this uploaded file, so the file can be referenced in a catalogue template report.

SWITCH( [FileOrURL],
“File”, CONCATENATE(
https://www.appsheet.com/template/gettablefileurl”,
“?appName=”, ENCODEURL(CONTEXT(“AppName”)),
“&tableName=”, ENCODEURL(CONTEXT(“Table”)),
“&fileName=”, ENCODEURL([File])
),

“Image”, CONCATENATE(
https://www.appsheet.com/template/gettablefileurl”,
“?appName=”, ENCODEURL(CONTEXT(“AppName”)),
“&tableName=”, ENCODEURL(CONTEXT(“Table”)),
“&fileName=”, ENCODEURL([Image])
),
“”
)

I have tried all manner of combinations of the following expressions for the FILE column and none appear to have worked:

  1. CONCATENATE("/",[PropertyUUID].[OwnerUUID], “/”)
  2. CONCATENATE([PropertyUUID].[OwnerUUID], “/”)
  3. CONCATENATE("/", [PropertyUUID].[OwnerUUID])
  4. CONCATENATE("/", “Documentation_Files”, “/”,[PropertyUUID].[OwnerUUID])
  5. CONCATENATE(“Documentation_Files”, “/”,[PropertyUUID].[OwnerUUID], “/”)

Any ideas as to what I am doing wrong? :thinking:

You’re putting those expressions here?

image

2 Likes

Hi @Marc_Dillon no, it’s in a expression in the File column of a table called Documentation. I.e. so when they click FILE to upload a document, it places in the sub-folder I am trying to create. So it is not a work-flow or bot action.

See screenshot.

Then you’re putting it in the wrong place. Use the field that I showed.

2 Likes

@Marc_Dillon Many thanks Mark I now understand. However is there any reason why the following works, where I refer to the directory using a virtual column

Where TestDirectory is defined to be

CONCATENATE(“Files”,"/", [_THISROW].[PropertyUUID].[OwnerUUID])

Yet, using the same formula in the directory reference, doesn’t work.

In the case above: it does not create the subdirectory based the OwnerUUID; simply ignores it and puts the file in the directory Files/

Possibly because you didn’t include a forward slash at the end?

Hi @Marc_Dillon I don’t believe the “/” at the end is the issue. The question there is: does AppSheet consider the string to represent a ‘folder’, in which case the last “/” may not be necessary. When it is added this is what appears in the File column in the database. Note: the double "//"

Files//1641979129000en-GB-1268f0cf-4c455942.File.091942.pdf

The only version I have found to work so far is to hard-code the [PropertyUUID].[OwnerUUID]. The version below works and creates the file in the sub-directory given by the OwnerUUID 1606154640000en-GB-121963bd-e60a348f

NB: there isn’t a “/” at the end… although hard-coding this is not a solution going forward.

CONCATENATE(“Files”,"/",“1606154640000en-GB-121963bd-e60a348f”)

Neither of the following work.

CONCATENATE(“Files”,"/",[_THISROW].[PropertyUUID].[OwnerUUID], “/”)
CONCATENATE(“Files”,"/",[_THISROW].[PropertyUUID].[OwnerUUID])
CONCATENATE(“Files”,"/",[PropertyUUID].[OwnerUUID])
CONCATENATE(“Files”,"/",[PropertyUUID].[OwnerUUID], “/”)

For some reason trying to access the OwnerUUID for the property to use as the sub-directory as the file directory location returns blank. Hence the reason it is missing when checking the File column in the database:

Files//1641979129000en-GB-1268f0cf-4c455942.File.091942.pdf

Perhaps this is a bug or the functionality to reference a column in the expression for the directory doesn’t exist. :thinking:

Put

[PropertyUUID].[OwnerUUID]

into a new temporary Virtual Column on the same Table, and use the “test” button, what are the results?

1 Like

Ok strange test.

Virtual column called: OwnerDirectory [PropertyUUID].[OwnerUUID]

For one OwnerUUID the formula works and pulls back the OwnerUUID. See screenshot below

However for another Owner, the one I am logged in as, it does NOT pull back the OwnerUUID, only the PropertyUUID is picked-up correctly but the OwnerUUID is blank. See second screenshot.

However for all properties the Owner owns the OwnerDirectory virtual column is correctly displayed. Seen in the screenshot below as text rather than as Ref column.

It does explain why the following doesn’t work either.

CONCATENATE(“Files”,"/",[OwnerDirectory])