Get file URL in Select query

[BrianI]

Hi, I would like to render a link to a stored file on a datapage tabular report. The file is not directly in the data source of the page so I need to get it using a SELECT query...

SELECT form FROM ns_tbl_client_forms WHERE client_fk = target.[@field:ce_tbl_Client_client_id] AND form_type_fk = '1HXYQIHS'

'form' is the name of the field where the file is stored.

The above query correctly returns the name of the file, example 'testfile.txt'

I do not know how to get from this to a correct link.

I notice that the links in other parts of the app are formulated using an ID for the file such as...

https://xxxxxxxx.caspio.com/dp/78f9d0000333fdfa577f4xxxxxxxfiles/3706886

I don't see how to get from the file name 'testfile.txt' to the ID '3706886'

Any pointers would be appreciated..

Thanks

[BrianI]

According to Caspio support this is not possible without enabling Filestor CDN, but that is not secure and we have sensitive documents.

[Barry]

That is correct, the only way would be using Filestor CDN or store the file externally. As with the standard file system, the files are saved in Caspio and when they are requested by a DataPage the url to the file is generated with a random identifier. It is like that for security reasons.

What I have done in one of my applications is to upload the files to Drive so I can share files externally. You can check it out here: 

 

[BrianI]

In the end I added a button on the report to open  a modal popup and added a filtered details page to the correct file.