LinkedIn
Twitter
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:
- CONCATENATE("/",[PropertyUUID].[OwnerUUID], “/”)
- CONCATENATE([PropertyUUID].[OwnerUUID], “/”)
- CONCATENATE("/", [PropertyUUID].[OwnerUUID])
- CONCATENATE("/", “Documentation_Files”, “/”,[PropertyUUID].[OwnerUUID])
- CONCATENATE(“Documentation_Files”, “/”,[PropertyUUID].[OwnerUUID], “/”)
Any ideas as to what I am doing wrong? 
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.
@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/
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.
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])
