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?

1 76 10.6K
76 REPLIES 76

LeventK
Participant V

@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.

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.

2X_b_b09b556b8d8eab21d37e68e43d3dc9ec4c23a2e5.png

Tapping the button brings me to this screen:

2X_0_02de0cdb28eb8dee8ec0a443ff55adba47a0661d.png

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.

where did you store the โ€œcurrent invoiceโ€ data? at the parent table or the pdf report table?

when i test my file name function:

substitute(concatenate([ืคืงืข], โ€œ-โ€, day(today()), โ€œ-โ€, index(list(โ€œ01โ€, โ€œ02โ€, โ€œ03โ€, โ€œ04โ€, โ€œ05โ€, โ€œ06โ€, โ€œ07โ€, โ€œ08โ€, โ€œ09โ€, โ€œ10โ€, โ€œ11โ€, โ€œ12โ€), month(today())), โ€œ-โ€, year(today()), โ€œ-โ€, timenow()), โ€œ:โ€,"-")

it is working.

also when i test my file location function it is ok:

concatenate(โ€œappsheet/data/qctest-310438/reports/โ€,[ืคืงืข],"/",pdfreports[name] , โ€œ.pdfโ€ )

but at the data table i get this: โ€œappsheet/data/qctest-310438/reports/PRD00125638/.pdfโ€

what am i doing wrong?
both lines are executed at the action.

If your saving of the file is going to the folder you wish and you can open the file, then you do not need to look at the Save PDF File workflow. That part is good.

Could you provide screenshots of the following four things?

  1. Your โ€œDefault app folderโ€ setting - found under Info->Properties->App Properties.
  2. Column definition for the file column.
  3. Your column info captured in the sheet.
  4. The physical location of the file in your datasource - including path info.

From that we can piece together where the path construction is mis-behaving.

  1. Column definition for the file column:
    Image/File folder path: CONCATENATE(โ€œreports/โ€,[ืคืงืข])

App formula: concatenate(โ€œreports/โ€,[ืคืงืข], โ€œ/โ€, โ€œchangereportโ€,".pdf" )

3.Your column info captured in the sheet: reports/Prd43/changereport.pdf

  1. The physical location of the file in your datasource - including path info:
    the file is in google drive
    appsheet/data/qctest-310438/reports/

Images are 100% better to accurately convey info.

Reading what you typed, it seems you may have the โ€œreportsโ€ folder introduced twice.

EDIT: Sent this before I intended.

You can confirm in the error message screen what path it is trying to use. Look it over carefully to find the full path it is looking at.

I would try removing altogether the path info in the column definition.

i am limited by the site rules to attach more than one imageโ€ฆ
sorry

Understood!

According to what you posted you have the file path defined in the column as โ€œreports/Prd43โ€ but if you are also including it in the filename saved in the sheet as โ€œreports/Prd43/changereport.pdfโ€ then the app is likely looking for a file with this path:

โ€œappsheet/data/qctest-310438/reports/Prd43/reports/Prd43/changereport.pdfโ€

note that โ€œreports/Prd43โ€ is listed twice. You can confirm this by inspecting the error message screen you get for the path.

To fix, simply remove the folder path definition in the column since you are already building the full file path into whats saved in the sheet.

ONE OTHER NOTE: in #4 you mentioned the path is โ€œappsheet/data/qctest-310438/reports/โ€ but didnโ€™t include โ€œPrd43โ€ if your files are truly being stored inside of the โ€œreportsโ€ folder, then youโ€™ll need to remove the โ€œPrd43โ€ portion that is written to the sheet.

Hi,

This is really useful, but I wonder; would you know of a way to display the pdf directly within the app detail view?

I do not believe that is possible at the moment.

But Iโ€™m curious about your use case.

Are you wanting to be able to view the entire document in this manner?

I could see the benefit if there was some sort of thumbnail displayed to distinguish one doc from another when there are multiple

That is a shame, think it will be really useful and hope that it would behave in a similar way of when an image is captured via camera, maybe not immediately as the app will need time to create the document.
Is there a way of converting the pdf to a jpg or similar, and do you think we could work around this and display the converted pdf?

Donโ€™t forget about multi-page PDFโ€™s!

I am not familiar with a way to convert PDF to JPG. But seems like there could be something. It would need to be something outside of AppSheet. If you can get the doc converted to an image then you certainly can display it as an image in the app.

Are you just trying to make the user workflow simpler without the extra taps to view the document?

Will have a look around and investigate.
It would be nice to have the doc as an image within but having the extra taps to display the pdf isnโ€™t too taxing for the user.

Another thing, the pdf location is saved within my table and can be clicked to view but the clickable text is just what the cell contains within the table.

2X_e_e8b8a4ef6a533be0c7723d5a7baa36f62b51c82b.png

How would I make this look better?
Maybe just show โ€˜Click here to viewโ€™ or something similar.

If you scroll up on this thread and look at my post on Sep 19, I gave some images on how I display my files. I create an action used in a Deck View to show a list of files and then select the one I want. If there is only a single file, this action could instead just open that file.

What view are you using to get to the Detail view?

As soon as I clicked reply I knew the answer to the question already.
Iโ€™ve created an action and itโ€™s viewed inline within the detail which is connected via a table.
Iโ€™m sure Iโ€™ve read somewhere that you can add the actions within the row of a table which may be a better option for me.

Actually, by default you already have a system generated Inline action created. If you simply show the PDF column in the table, the action will be displayed instead of the file path text.

In the table below, the solid icon is the system action. The other is my custom action.
2X_1_16179d3f4d9f60db0b9f818f32d3dd729a0b1dea.png

To create my custom Inline Action, I created a Virtual Column, defined it as Color type and then set the color = " " (a single space). Copy the system action, rename it and set the attached column to the Virtual Column.

In Detail View, Custom Inline Action shows with no text
2X_b_bcbe6d978ca4d3971902f9d592fd3821c2bf808e.png

Nice!
Thanks!

I know this post is old but it's possible with Apps script to create the Preview of the PDF and in this way present it inside the AppSheet, as shown in the image. anyone using Apps Script to generate the PDF easily adds a column with the image.chrome_2iR7jlK82a.gif

where is it? ๐Ÿ˜๐Ÿ˜

Interseting, can you please share some sample Apps script code? I need to view the pdf that my app just generated and saved on the drive. I'm not sure how to get the new generated file name and push it to Apps script but a good sample code will help me get started.

Thanks

Imad 

Could you help me do this? My company already has a folder system that contains many files in it. Inside those files are pdfโ€™s (invoice). So, I am not having the user input/download the pdf - they are already there.

My users want to be able to find those pdf (Invoices) and either:

View it in app
Print it
Email it

How do I do this?

I had been out on vacation. Did you get the answers/solution you were looking for on your questions? I see some posts after this one on the topic.

Hey. Thanks for the response. Sorry - now I was out

No - I am confused on how to be able to do this. I see post on finding the document (IF you placed it in a file/folder - using the app). But I am not putting files in folders using the app.

I need the app to be able to search a file/folder system and locate a pdf without having placed it in using the app.

Does this make sense?

@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

2X_6_6e0407cba0cedd0786babd9bed93c44048bc7b39.png
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.

Hello Willow, 

Could you send a photo how you set below steps ? 

  1. As discussed before, use Action from Workflow to add new row to Reports table with filename as โ€œCurrent Filenameโ€.
  2. Generate file from template with name as โ€œCurrent Filenameโ€.

I add an Action Add a  new row, and FILE column is the file name

But how do get that file now with Date & Time stamps to use for the next action Create a File ?

kvngo94_0-1651552800373.png

 

Create an action to use it inside the app, not a bot.

The action will trigger an add event on the bot you should use to create your file.

You have to make the filename of the file from the bot the same as the last part of your file column.

I can't add too much detail, I think there are enough posts about this

Hi SkrOYC, 

The bot I am trying to achieve is : 

- Send email, with attachment, but opt for SendAndNotArchive file

- Add a new row to a table, to record the new file, with the file path formulas as : CONCATENATE("REQUEST/ORDER/",[REQUEST #],TEXT(NOW(),"yyyyMMdd_HHmm"),".pdf")

- then add an action to create a new file in that Bot with the same file path

But the issue is I do not know how to get the exact file path of the Add a new row action, and use for Create a new file file path action. They will result in different in few ss as : .....23 and .....25 even they are in the same bot

 

Do not calculate both names using the same formula.

Calculate one of them (the row added) and then base the filename of the file made through the bot of the already made filename on the file field

That works! thank you

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โ€,"&tabl...โ€,[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.

Top Labels in this Space