Generate a report via a button based on a date field

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I own a moving company and in QB I have a table called jobs. I have other tables for trucks and crews assignments etc. that all have relationships to the jobs table so I can assign crews and trucks to each job separately. In the job I have a move date field. I want to be able to click a button or link within the job that generates a report based on the move date I've entered for that job. So the button would open a report and show me the crew and truck assignments for all jobs that day, allowing me to see what availability I have. I have reports that already provide this information, so the button is to simplify the process as it looks up a report specific to the move date field on the job. Hope that makes sense. Appreciate the help :)

Photo of Patrick

Patrick

  • 90 Points 75 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 5,304 Points 5k badge 2x thumb
A good way to do this yourself is to build the report for a specific day.  Then use the More button to get to the "Show expanded URL" link.  This will give you the basis for the button.  If you post the expanded URL back here, then I can help show you how to modify it to encode the Date of your Job.
Photo of Patrick

Patrick

  • 90 Points 75 badge 2x thumb
Thank you very much for your assistance. Here's what I got from the More button regarding the URL.

The expanded URL for this form is:
?a=q&qt=tab&dvqid=15&query=({'10'.EX.'today'}AND{'38'.EX.'ACTIVE'})&clist=20.39.21.9.47.36.28.43.45.44&slist=47.43&opts=so-AA.gb-VV.&rcform=If(%0D%0A%5BJOB+-+Truck+ID%5D%3D%22NAL+2%22%2C%22%23ff962c%22%2C%0D%0A%5BJOB+-+Truck+ID%5D%3D%22NAL+3%22%2C%22%2365f665%22%2C%0D%0A%5BJOB+-+Truck+ID%5D%3D%22NAL+3%22%2C%22%23f1ff12%22%2C%22%22%0D%0A)%0A
Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 5,304 Points 5k badge 2x thumb
So your formula should be (and may need some refinement):

URLRoot() & "db/" & [_DBID_XXXXX] & "?a=q&qt=tab&dvqid=15&query=({'10'.EX.'" & [DATE FIELD] & "'}AND{'38'.EX.'ACTIVE'})&clist=20.39.21.9.47.36.28.43.45.44&slist=47.43&opts=so-AA.gb-VV.&rcform=If(%0D%0A%5BJOB+-+Truck+ID%5D%3D%22NAL+2%22%2C%22%23ff962c%22%2C%0D%0A%5BJOB+-+Truck+ID%5D%3D%22NAL+3%22%2C%22%2365f665%22%2C%0D%0A%5BJOB+-+Truck+ID%5D%3D%22NAL+3%22%2C%22%23f1ff12%22%2C%22%22%0D%0A)%0A"


You will need to lookup the DBID of the table where you are pointing the button to run the report.  To find this, click on Settings in that table; then click on Advanced Settings and scroll to the bottom of the form for the Table Alias.  Then your [Date Field] will the field that contains the Date from the record.  Note; that you will probably need to use this in View mode; since the [Date Field] will need to have saved to the record first.

I have created a simplified version of this to learn from in an application you can view here: https://laurahillier.quickbase.com/db/bjys7aexk
Photo of Patrick

Patrick

  • 90 Points 75 badge 2x thumb
That worked perfectly. No more opening a report and applying a filter, it pushes the report based on a specific date field. I see a lot more use for this in other areas of my setup and I think I understand how you adapted the url so I can apply it to other areas. Is there a way to open the report on a new tab or window so as not to navigate away from the job itself?
Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 5,304 Points 5k badge 2x thumb
Make sure your URL button settings are set to open the page in a new window