Need URL formula link that displays a report from Table #1 while viewing records in Table #2

  • 3
  • 2
  • Question
  • Updated 3 months ago
  • Answered
Is this possible?
Photo of Robin CC

Robin CC

  • 220 Points 100 badge 2x thumb

Posted 5 years ago

  • 3
  • 2
Yes, absolutely possible.  If you can describe your actual tables and if they are related or how the URL button would know what report  and how to filter the results.  It may also be possible to simply embed a table of results on the Table 2 record using a "Report Link field".
Photo of Robin CC

Robin CC

  • 220 Points 100 badge 2x thumb
The tables in question are not related.  As long as it can display the proper report, I don't need it to be further filtered.  In other words, the report itself is already filtered.  The report I want to link to is called "Expenses for Burn Rate" in the Expense table and is report ID # 26 in that table.   Do you need any other info?
Well then, thats pretty easy.

Low tech:
1. Run the report and copy the URL to your clipboard
2. Create a URL formula field called "Expenses for Burn Rate" and paste in the URL in quotes.
e.g. "https://mycompany.quickbase.com/db/xxxxxxx?a=q&qid=23";

but the "right way" is to do it like this.

urlroot() & "db/" & [_DBID_EXPENSES] & "?a=q&qid=23"

The stuff in the [ ] comes from the Advanced Properties of your Expenses table. Doing it the right way will keep it from breaking if you ever make a copy of the app.

Once its working Give the URL formula field a label (half way down the field properties)
Photo of Robin CC

Robin CC

  • 220 Points 100 badge 2x thumb
worked perfectly.  Thanks.  By the way, what "language" is the URL formula? Is that Javascript?
No, that is not Javascript.  You don't need to be a real programmer to do some simple URL formula buttons.  I have an example in the "Exchange" which gives some example of how to do URL formula buttons. Try searching on "Dummies" and it should come up   "URL formuals for Dummies".  Its meant to explain them in easy to understand terms.
Photo of Robin CC

Robin CC

  • 220 Points 100 badge 2x thumb
Ok.  Thanks.  I'll look into that.
Photo of Polina

Polina

  • 0 Points
Hi Mark, thank you for the guidance. With your advice I managed to display a link to a report, but is there a way to display a prefiltered report? I have two tables: Project and Projects tasks. On the Project side I would like to have a link to all currents tasks for this project. I need to make sure that Project code from the Project table is equal to the Related project code from Tasks table.

Update: I'm using the formula URL
URLRoot() & "db/" & [_DBID_RISK_MITIGATION_STEPS] & "?a=q&qid=10" & "&query={'28'.TV.'[Project Code]'}"

And the URL I get is
https://mycompany.quickbase.com/db/bj6z1bijy?a=q&qid=10&query={%2728%27.TV.%27[Project%20Code]%27}

This link leads to the report I need, but instead of searching for the project code value, it thinks that [Project Code] is the needed value. It doesn't recognize it as a field, just as symbols. Hope that make sense...

Thank you in advance,
Polina
Photo of Jeff

Jeff

  • 2 Points
Remove the %20 and leave it as just a space
Photo of Robert Kreibich

Robert Kreibich

  • 172 Points 100 badge 2x thumb
Mark thanks for your great community support.  How can the URL specific report link above be adjusted to show a report in another related child table filtered to the Related Project.  Projects is the mother table and Sites is the child table w/ Project ID as the key text field in Projects and Related Project in the Sites child table = to Project ID of course
Robert,
Are you looking to create a report link field to display an embedded report on a form?
Photo of Robert Kreibich

Robert Kreibich

  • 172 Points 100 badge 2x thumb
Thanks for the fast reply.  Just need to jump to a specific (not default) filtered report in the child table listing all the sites that are associated w/ the mother Projects table where the URL jump link will be.
If you want to launch a report off a record  and want it to be filtered on some criteria that the record knows, you can do that easily.

1. Make your perfect report, and have an <ask the user> for any criteria.  Let's say there is just 1 criteria for this example.

2. Run the report and answer the question manually.

3. Observe the URL.  That is what we need to replicate in the URL

Make the Formula URL field.

Urlroot() & "db/" & [_DBID of target table from its advanced properties] & "?a=q&qid=99&nv=1&v0=" & ULREncode([field from the record you are launching form]

replace the 99 with the qid of your report.
Photo of Robert Kreibich

Robert Kreibich

  • 172 Points 100 badge 2x thumb
Thanks Mark. I will try it out later today
Photo of Robert Kreibich

Robert Kreibich

  • 172 Points 100 badge 2x thumb
OMG!  Works perfectly.  Thanks so much
It's a real handy trick.

The nv=1 means and number of variables (ie questions =1), and the v0 means "and here is the answer to the 0th question" - its starts numbering at zero,

so if there were two <ask the user> questions it would read

&nv=2&v0=" & urlencode([field1])& "&v1=" & urlencode([field 2])

  
Photo of Robert Kreibich

Robert Kreibich

  • 172 Points 100 badge 2x thumb
Thanks so much again for your insights.  You are the Champ!
Photo of Karen Packwood

Karen Packwood

  • 102 Points 100 badge 2x thumb
What if I have 2 <ask the user> questions in the report (Project Number and Fiscal Year) but the user still have to specify the Fiscal Year.

It is work if I set Fiscal Year  with a specific value (ex: FY-2020) ...
URLRoot() & "db/" & [_DBID_EXPENDITURES] & "?a=q&qid=7&nv=2&v0=" &  URLEncode([Project-ID]) & "&v1=" & URLEncode("FY-2020")

But if Fiscal Year is still an <ask the user> question, what have I to do? 

Thanks,
Karen



There may be solutions with increasing levels of complexity to set up, but how about landing the user on a report with just the project pre-selected by the URL and allowing them to just use a Dynamic Filter to select a fiscal year?  basically that is the same effort as having to answer an <ask the user> question, it's just off to the side.

The report description can Remind them tell them to do that.  The report can also sort and group by Fiscal year.