What is the best way to retrieve and view files in app that are generated from templates?

I use workflows to generate and send customer pdfs from templates in Google. Currently, I use the archive option to save the generated result in the default location in Google with the current timestamp to ensure file name uniqueness.

When certain record info changes, a new file is generated with the updated details and saved as a new file. I would like to be able to retrieve the history of all saved files associated with a certain record and display them in the app - as a list or gallery.

What is the easiest/best way to be able to keep a file history, view that history and still know which file is most recent to send out to customers upon need/request?

@WillowMobileSystems
I believe the most appropriate option would be:

  • First create a table for that PDF reports, have a key column explicitly for that table, put another column for the customer records or any other you see fit to construct a REF and a file type column.
  • Create a behavior action from the parent table, that will add a row to this Reports Table. Refer to Praveen’s post here > New Action: Add a Row to a table
  • Add a second action to your workflow rule that is generating your PDFs which will trigger the behavior action above for creating a new file record each time the workflow is triggered
  • Construct the REF type column in this Reports Table and associate it with your parent table.

With this, you will be able to see all the PDF report history of the parent record as an inline view in the Detail View of any particular parent record. With the associated File Type column, you can open those PDF files directly within your app.

I believe that will be the most robust solution.

1 Like

@LeventK thank you for the quick reply!! Your suggestion is what I had in mind but I thought there might be something easier.

A follow-up question…how would you manage the filenames, remembering to maintain uniqueness? I would assume you want to generate a known name into the newly created Reports table and then use that name in the Workflow step that generates the new report file. The uniqueness part is probably the most uncertain portion for me.

Suggestions?

@WillowMobileSystems
Provided you don’t disable the timestamp suffix in the workflow rule, that will always maintain the unique name for the PDF files. When you enable it, AppSheet will append a system generated Timestamp in the attachment file name. The Timestamp contains the date and time at which the attachment was created. The date and time are formatted as yyyyMMdd_HHmmss_fff where fff is milliseconds. This will ensure the attachment file name is unique. For example:

MyAttachmentName20190207_133355_804.pdf

image
Check it out more from here:

@LeventK Thanks again! I think I may not have explained my concern well enough.

If you allow the filename to be created with timestamp in the Workflow rule that generates the file from the template, there is no way to know what that filename will be beforehand because of the timestamp. So there would be no way to accurately record the filename into the Reports table for later. As far as i can tell, there also is no way to retrieve the filename created AFTER the file has been generated from the template.

So it seems A solution is:

  1. Disable Timestamp so its not added to the filename when file is generated from template.
  2. Manually create the filename (can include Now() to add timestamp)
  3. Store the created filename into the main record as say “Current Filename”.
  4. As discussed before, use Action from Workflow to add new row to Reports table with filename as “Current Filename”.
  5. Generate file from template with name as “Current Filename”.

While these steps could add the correct filename to the Reports table for viewing in the app, the potential issue with this approach is that should the file generation fail (bad template, connection problem, etc), the app will reflect a file that does not exist.

It is better to create the file first and then store the filename into the data afterwards. But I don’t see a way that can be accomplished. In the end, the risk of an issue is minimal and acceptable.

So, what are your thoughts? Is there a more minimal approach?

@WillowMobileSystems
For sure, it could be very good to have a system variable or function i.e. IFERROR(“Workflow_Rule_Name”) which can return a boolean value, so that we could know if the result of the workflow is successfull or not but unfortunately we don’t have it. So I believe this option is the best to achieve the result. There is a [_ATTACHMENTFILENAME] built-in variable to retrieve the name, but it can only be used in a workflow body.

Hmmm. I was not aware of that built in variable. Since we now have multi-action Workflows, is the scope of that variable within the ENTIRE Workflow body or or just that single Action of the Workflow. In other words, if I generate the file as the first step in a Workflow and that sets [_ATTACHMENTFILENAME], will that variable value pass on to an Action called as the second step of the Workflow - where I could collect it and save it to table?

I suspect not but I’ll play and see.

One way is if you don’t use the system generated timestamp. One way is a version number in your file name. Of course it depends if there is lot of users and then the version number could be twice.

Yes, thanks! For the very reason you mentioned, I have opted to go with Date and Time stamps. I cannot use the Date() function directly since it contains “/” and that generates file paths I don’t want. It seems I need to use Year(), Month(), Day() function in a concatenate function. Is there a cleaner way to get the date in the YYYYMMDD format?

On a side note… It seems like there is not a way in an email workflow to re-attach an already generated file. I mean to pick up a file that had been previously archived. Is this correct?

My research shows suggestions to create a URL to the archived file and include that in the email body. Is this the suggested way to send an archived file (without going through a 3rd party webhook)?

Well there are many ways to create that date value but the formula are more or less the same.

Yes that’s true. You can’t add it in the same email. You can send it with the same Workflow but it’s two different emails/actions. But as you were thinking, you can add a link to that PDF.

CONCATENATE(“https://www.appsheet.com/template/gettablefileurl?appName=",“YourAppName-YourAccountIDnumber”,"&tableName=",“YourTableName”,"&fileName=”,[PdfColumnName])

Ok, thanks! I think I read that if a File type data is referenced in the email body, AppSheet will automatically generate the URL for us?

Yes that’s true. You can use for example <<_ATTACHMENTFILE_URL>> in the body. I was thinking actually a file column rather than the Workflow/PDF itself.

@Aleski - I was indeed referring to column of type File. I read this:

"Constructing an Attachment File URL

"YourAttachmentFileName" must specify the Attachment Folder Name followed by the Archive File Name. Omit the Default app folder name. Calls to “www.appsheet.com/template/getappfileurl” automatically prepend the Default app folder name."

I took this to mean that I can use my column File type and AppSheet will add the necessary bits for the URL.

This also leads me to a question…What is expected to be written in terms of path + filename+ extension to the column of File Type? When I write only the “filename.pdf”, and then try to view that file, I get an error that file cannot be found (see pic below).

Only when I update the File column text to “appsheet/data/ServiceInsightDemo-526414/Files/Estimate_p7fudBEC_2019820_10_43_33.pdf”, is the file able to be viewed from the app. Bu this is WITH the default app folder which seems to contradict the paragraph above.

The other puzzling thing is that in the error it seems it is trying to use appName variable = “ServiceInsightBase-526414” to create the URL. I have the Default App Folder as “/appsheet/data/ServiceInsightDemo-526414”. The files are written to the Default App Folder as expected. But even copying files to a folder for appName (under appsheet/data), the file is not found.

This is exactly what I am looking for, but I cannot figure out how to create the correct file name (or the link) to open the PDF files in the app. Can you help?

I have this working in my app now. You may need to play around with paths and filename creation to get it right and understand what is going on.

When I generate a file from template, I send it out in a Workflow step as an attachment only on an email. I use a second step to generate it again and save the file. This seems to give me better control over the file name (though I don’t like generating a doc twice).

To start, I built an Action that first creates the filename prefix and saves it in a column that I later use when creating the file (see Workflow step below). This allows me control to use the SAME filename used in both the attachment as well as the writing of the file while still using a timestamp that ensures no dup file names.

In the Workflow step that creates an attachment to an email, I only need to give the filename prefix since it doesn’t archive the file.

The path and filename are actually generated in the Save File Workflow step in 4 parts:

  1. The default app path setting
  2. File Folder path set in the Workflow
  3. File name prefix which uses my saved filename created by the Action.
  4. File suffix determined by the HTTP Content Type setting.

I wanted to take this a step further by keeping a file history of each file sent out. To do that I record the path and filename of the written file so I can retrieve it later. I created a table and use an Action to add a new record that saves some info and re-creates the filename utilizing the previously saved filename prefix column and known defaults. The table looks like this:

Then in my app I have a deck view for Orders with a button to navigate to the file list. Its the doc icon with magnifying glass.

08%20PM

Tapping the button brings me to this screen:

05%20PM

Tapping on one of the file records opens the file. The file is opened in a browser window and looks like the below on a desktop:

I hope this helps! Feel free to reach out if you have questions.

3 Likes

I’m really struggling with this one! Can you send a screenshot of your Action set up to create the filename?

Also, having created a specific folder for these pdf’s to go into, it has decided to put them in another folder which I had to dig around and find as it had created multiple layers of folders.

Sure! As I mentioned above i create the filename in 2 parts.

  1. I save into a column the file prefix name. This allows me to use the same name for the multiple steps I have AND include a TimeStamp to ensure uniqueness:

The below expression creates a name like "Invoice_c98a86fb_20190914_11_38_01"

Note the use of SUBSTITUTE(). I found that the Workflow steps replace the “:” in the time with slashes. This is why you are seeing layers of folders most likely.

  1. When I add the file path+name to the table, I use the expression below to build this complete name:

In this expression, the text “appsheet/data/ServiceInsightDemo-526414/” is based on your App Default Path. When writing the actual files, the app default path is used. However, when the files are retrieved, the default path is NOT used so you have to include in the built file path+name.

53%20PM

Digest this and let me know what further questions you have.

2 Likes