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?
Youโre putting those expressions here?
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.
@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.
Put
[PropertyUUID].[OwnerUUID]
into a new temporary Virtual Column on the same Table, and use the โtestโ button, what are the results?
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])
User | Count |
---|---|
40 | |
36 | |
33 | |
23 | |
17 |