Generating a file from a form with a bot and addin...

CANA-IT · 03-01-2025 05:44 AM

I feel like I am close... Just missing something I can't put my finger on.

I have created a data table, the columns are used in a form to Generate a File.
I have a column in another data table which call the file location.
But I cannot get the file to populate in the record.

Driving Cost Compare Data Table
The Form works perfect. Generates the File named:
CONCATENATE("https://drive.google.com/drive/folders/costcomparison", [Traveler],[Row ID])

Request Table
I have another column in another data table (Request Table) which I want the file to be added to when the user creates the request. The file is generated prior to creating the request.
But I don't think I am using the right formula expression.
CONCATENATE("https://drive.google.com/drive/folders/costcomparison", Driving Cost Compare[Traveler],[Row ID]) (Driving Cost Compare is the Data table used to initially create the file.)

Gustavo_Eduardo

Have you clicked "Test" to see if there is an error in the bot?

CANA-IT

The Bot doesn't error. The bot generates the file as it is supposed to.
I then need the file generated to be added to the record for the user.

Step 1. User fills out form which creates a file
Step 2. User files out a different form to create a request record.
The file in step one needs to be placed in the correct column for the correct user for the correct request record.
Alternatively - The file can be email to the user (I'm not sure how to do that either) so that the user can place the file in the correct section of the form.

AleksiAlkio

I assume you are using spreadsheet as a data source. You need to use relative file name and path starting from the folder where the spreadsheet is, not URL.

CANA-IT

I am not using a spreadsheet as the data source. I am using a database.

AleksiAlkio

Then the relative path should start from the default app folder.

CANA-IT

Ok... I don't understand that. I was just able to create the file with the form and then email it to myself. So.. that might be the solution.
I would still like to auto-add it to the record. But... emailing it to the user might be easier.

Could you explain this further "Then the relative path should start from the default app folder."

AleksiAlkio

You can find the default app folder from Editor > Settings > information > Default app folder. It's something like /appsheet/data/AppName-12345678.

When you generate the file with the Automation Bot, you need to specify the file name by yourself with a formula. When you save the record, you should save the same file name with the path where the file is saved in your Drive etc.

Let's assume you save the file with the Automation Bot to Root > appsheet > data > AppName-12345678 > Files. You have selected the key column as a file name. The file column needs then an expression where the result is /Files/Filename.pdf, something like "/Files/"&[KeyColumn]&".pdf"

CANA-IT

Still trying to get it dialed in.
This is the column expression. (Column is type "File")

Here is the expression: CONCATENATE("\appsheet\data\CTSRequest-4600424\Driving Cost Comparison Files\", Driving Cost Compare[Traveler],"Driving vs Flying Cost Comparison",[Row ID])

The file Generated is named: <<[Traveler]>>Driving vs Flying Cost Comparison<<[Row ID]>>

The File is not showing up in the designated column. It is only displaying..

So.. I know I am close.. but still doing something wrong...
This is the file path:
G:\My Drive\appsheet\data\CTSRequest-4600424\Driving Cost Comparison Files

AleksiAlkio

The expression needs to be something like:
CONCATENATE("\Driving Cost Comparison Files\",[Traveler],"Driving vs Flying Cost Comparison",[Row ID],".pdf")

CANA-IT

Getting closer:

        <html>
        <body style="margin:20px">
          <div>&#39;\appsheet\data\CTSRequest-4600424\Driving Cost Comparison Files\ , keadesng vs Flying Cost ComparisonnsyywsZFWMUaZ207lCtsCR&#39; not found</div>
          <div>Probable cause: N/A</div>
          <div><ul><div>
   <div>Path: Root</div>
   <div>Result: FAILURE</div>
   <div>Message: N/A</div>
   <details>
<ul>
<li><div>
      <div>Path: Root</div>
      <div>Result: FAILURE</div>
      <div>Message: N/A</div>
      <details>
<ul>
<li><div>
         <div>Path: Root</div>
         <div>Result: Success</div>
</div>
</li>
<li><div>
         <div>Path: Root</div>
         <div>Result: FAILURE</div>
         <div>Message: N/A</div>
         <details>
<ul>
<li><div>
            <div>Path: Root</div>
            <div>Result: FAILURE</div>
            <div>Message: N/A, Searching for &#39;\\appsheet\\data\\CTSRequest-4600424\\Driving Cost Comparison Files\\mDriving vs Flying Cost ComparisonnsyywsZFWMUaZ207lCtsCR&#39;, File entry not found</div>
            <details>
<ul>
</ul>
</details>
</div>
</li>
</ul>
</details>
</div>
</li>
</ul>
</details>
</div>
</li>
</ul>
</details>
</div>
</ul></div>
        </body>
        </html>

AleksiAlkio

What is your expression for the file name in the Bot? Also.. is it a pdf ?

CANA-IT

File is PDF

Bot File Name Expression:
<<[Traveler]>>Driving vs Flying Cost Comparison<<[Row ID]>>

AleksiAlkio

What is your default app folder in this app?

CANA-IT

\appsheet\data\CTSRequest-4600424\Driving Cost Comparison Files\

AleksiAlkio

And is this from the Editor > Settings > Information > App properties > Default app folder?

CANA-IT

Yes

Generating a file from a form with a bot and adding it to a record