Attaching Files

[dsajones]

Hi.  Hope someone can help me with this.  I've looked through a number of posts but can't understand what the basics are for attaching one or more files (usually PDFs, sometimes JPEGs occasionally spreadsheets) to a record and then saving it so the record is in my Google Sheets data source table and the files have been uploaded from the user's PC to our Google Drive.

Here's what I'm trying to do:

The application is recording servicing records for vehicles.  Each vehicle has service records in a table with a one to many relationship.

The vehicle goes to a garage for servicing.  We then receive an invoice, maybe a job card etc. all related to that service.  They will have come in to someone in our organisation via an email from the garage.  Usually they are in PDF format.  The user will save them as attachments on their hard drive.

We want to create a service record where we describe what servicing was done, then the user locates the file(s) on their own hard disk using a normal file browser and uploads them then saves the record.

You can then go back in to that record and see all the attachments and click on them to view them.

I know that the file field can only hold one file at a time so I would have 3 or 4 separate fields e.g. file 1, file 2 etc.

Is this possible to do?  If yes, can someone point me in the right direction of how to do it?

Many thanks.

[SkrOYC]

If you want to have more than just 1 file assigned to certain record, then you would need to create another table just for those files so that each file is also a child record of the main one. In your case a simple table called something like [Files] with 3 columns should be enough, even a 4 column could be good in order to add comments on each upload.

Anyway, you can't upload more than one file at once

[dsajones]

Hi SkrOYC.  Thanks for your response.  OK, I can work with a related table to store the file in a one to many with each service record.  Can you point me in the direction of instructions on how to actually make the file upload happen?  I've seen lots of mention of workflows but I can't actually see how to make it work in the user interface.

[SkrOYC]

Well, it depends on what you expect from a UI/UX perspective.
The inline view of the related records will already have an "add" action that you can change to "add file", "upload file", etc.

You could also deactivate that and add a custom action that opens a form view to that Files table with the foreign key added automatically so it could be more obvious on detail views and customizable

[dsajones]

Hi SkrOYC.  I've now added a column of type File to the table as well as a text field to allow the user to enter a title for the file.  This allowed me to search for and add the PDF as well as fill in a title.  When I hit the Save button on the form and it redisplayed, I got the following result:

I had entered - "Work carried out" in the text box "File Title"

When I view the record this has now been replaced with the following text:

https://www.appsheet.com/template/gettablefileurl?appName=MFJobLog-260862&tableName=VehicleServiceRe...

The actual file has been uploaded to a default folder in my Google Drive and the filename has been changed to LG16 HSLb52fb0ea.File 1.055522.pdf

When I view the record I was expecting that if I clicked/tapped on the file name it would open the PDF in some form of viewer.

I would also like the text that i enter in the File Title text box to be saved exactly as I've typed it.

So it's clear I have further work to do to make this all happen.  But as hard as I search through documentation and help, I can't find the basic information on what formulas I need to put where, what additional UX views I need, if any.  

WOuld be great if you could point me in the right direction.

[dsajones]

UPDATE - I opened the app on my phone and the display was completely different to what I was seeing in the development environment and I was shown the filename as a tappable link which did open the PDF.  And the text box was correctly displaying the title that I had entered.  So that's all working absolutely fine.

Based on what I'm seeing when running the app in phone or table or in the web browser, the only thing I'd like to be able to do is to retain the original filename, perhaps just appending the RecordID to the end of it?  As an example, the filename of the file I uploaded was:

LG16HSL Work Carried out 58790 Miles.pdf  I'd like to keep that and add the RecordID to it like this.

LG16HSL Work Carried out 58790 Miles-b52fb0ea.pdf

If you could point me in the right direction for what formula would be required and what section that should go in that would be great, thanks.

[SkrOYC]

You can't control the filename, sorry.
Just some workarounds

More User Friendly Image names for images uploaded... - Google Cloud Community

[dsajones]

Wow, that's all very involved for what, on the face of it, seems a trivial requirement.

Given that one of your earlier replies discusses the best practise method of storing the files in a child table linked to the parent record, does this mean that to implement the meaningful filenames as described in the article, there would be a child table required for the files child table?

By that I mean:

Vehicle Service Record (Parent Table)
------ File Attachments (Child Table)
-------------- File Attachments File Names (Child Table)

Examples of actual records would be;

Vehicle Service Record
-- Record ID = a5Ze6tc
-- Reg Number = "AB11ABC"
-- Service Date = 02/08/2022
-- Service Type = "6k Service"

File Attachments
Record 1
-- Record ID = 9iKz3we
-- Parent Record ID = a5Ze6tc
-- FileName String = "AB11ABC-020822-6k Service"
-- Attachment Type = "Work Breakdown Report"
Record 2
-- Record ID = Hg3sq9P
-- Parent Record ID = a5Ze6tc
-- FileName String = "AB11ABC-020822-6k Service"
-- Attachment Type = "Invoice"

File Attachments File Names
Record 1
-- Record ID = lL7cRw3
-- Parent Record ID = 9iKz3we
-- FileName String = "AB11ABC-020822-6k Service-WORK BREAKDOWN REPORT"

Record 2
-- Record ID = Uth5Fnj
-- Parent Record ID = Hg3sq9P
-- FileName = "AB11ABC-020822-6k Service-INVOICE"

So the actual filenames created would be:
AB11ABC-020822-6k Service-WORK BREAKDOWN REPORT.lL7cRw3.pdf
AB11ABC-020822-6k Service-INVOICE.Uth5Fnj.pdf

Is that basically it or have I over complicated it?

[SkrOYC]

You could just change the way the key column's initial value expression is evaluated.

Instead of a UNIQUEID() you should make a unique-enough one so that you end up with a more descriptive filename since it will include the key value, which is how @Suvrutt_Gurjar explained his trick.

Another think to note that I'd consider a bug is that if the original filename has a "." on it's name, it will be added to the filename of the uploaded file (Like a filename of "ThisIsAFile.WithADot.pdf").

A drop-in change that I'd recommend you is that you should just change the UNIQUEID() Initial Value expression on the File Attachments table for something like this:

CONCATENATE(
 [FleName String],
 "_",
 [_THISROW]
)

Or

CONCATENATE(
 [FleName String],
 "_",
 TEXT(NOW(), "YYYYMMDDHHMMSS")
)

[dsajones]

Thank you for the additional suggestions.  I can see how that can work to create a slightly more meaningful filename.

Just to be clear on the question I posed in the latest reply, if I'm using a child table to the main Service Record table in which to store the attachments then I do need the child table to the attachments table to "create" the file names for those attachments.  Is that correct? 

Thanks, David

[SkrOYC]

The "File Attachments" is needed on your setup.

"File Attachments File Names" is not