Using Formula-URL Button field to run a report, filter by Record ID# the button was selected from, and save as a spreadsheet.

  • 3
  • 3
  • Question
  • Updated 2 years ago
  • Answered

I'm currently downloading a report with a single record and saving as a spreadsheet in order to auto-populate a form (formulas in 'Sheet 1' reference cells copied into 'Sheet 2' from QB). What I'm interested in exploring is the ability to create a Formula-URL field that is a button on each Employee Record that will automatically run the "Excel Export" report and filter the report to display only the Employee Record and then execute the "Save as Spreadsheet". This is basically a work-around to avoid having custom API and coding implemented. Any chance this is possible?

Thanks

Photo of Evan

Evan

  • 20 Points

Posted 4 years ago

  • 3
  • 3
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 60,178 Points 50k badge 2x thumb
No problem.
Step 1.  Make an report filtered on Record ID equals <ask the user>. Set the output format near the bottom to be CSV format. Run the report and answer with a Record ID and observe the URL.  Now all we need to do is make a URL formula to replicate that URL.

Step 2.  Make a URL formula field.
URLRoot() & "db/" & dbid() & "?a=q&qid=99&nv=1&v0=" & ToText([Record ID#])

Just replace the 99 with your Report number.

The formula says
mycompany.quickbase.com/db/123456/. That is the path to your table.

Then it says action = Query (ie run a report)
Then and the qid (query id) is 99
and number of variables = 1. (Ie there was one ask the user question)
And the variable zero to answer the question is the Record ID. (Computers like starting to count at zero sometimes.
Photo of Evan

Evan

  • 20 Points
I have the report working properly, but where I'm running into problems is when I insert that syntax into the field properties. The error I'm encountering is "Unknown Field - This table does not contain a field called Record ID#. To see available fields, click the Fields & Function dropdown to the right of the formula box."

Thanks
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 60,178 Points 50k badge 2x thumb
Perhaps you renamed that field.  I suggest that you look at your field list and filter the list to see which one is the Record ID# field.  Then go back to the formula box and choose that field from the drop down list when you click on the helper field list at the right of the box.
Photo of Evan

Evan

  • 20 Points
Problem fixed. Thanks!
Photo of Evan

Evan

  • 20 Points
Currently, Quickbase saves the CSV file as "Employee.csv". I'm assuming that is because the record that is exported as the .csv file belongs to the "Employee" table. Is there a way to have Quickbase save the .csv file according to the contents of a text field (i.e. Full Name)? Thanks
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 60,178 Points 50k badge 2x thumb
I don't think so, but you could try posting that as a new question.
Photo of jenifferhomes

jenifferhomes

  • 0 Points
Run the report and answer with a Record ID and observe the URL.  Now all we need to do is make a URL formula to replicate that URL.
Thanks
Jeni
https://www.npmjs.com/~blog
Photo of Tamara

Tamara

  • 366 Points 250 badge 2x thumb
Could something similar to this be used with a Mailto: function  for a report that only contains one column of email addresses?  I'd like to put a button on the dashboard to Open an email with the mailto prepopulated to the emal addresses listed in a specific report.  I know the user could run the report him/herself by running the report from a report button on the dashboard and then scrolling down to the blue envelope, but the users don't want to go through the extra step of viewing the report and scrolling to the bottom.  They just want one button to click from the dashboard that does the same thing.  Would a variation of this work?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,664 Points 20k badge 2x thumb
> Is there a way to have Quickbase save the .csv file according to the contents of a text field (i.e. Full Name)?

Instead of a URL create a hyperlink and set a download attribute equal to the name of the file you want to save the URL as: <a href="..." download="file.csv">Click Me</a>