Filter a Saved Quickbase Chart From a URL (Without Turning It Into a Table)

A question in the Quickbase community, Dynamically filter a saved chart report, describes a wall a lot of builders hit. You have a nice saved chart, and you want a button on a form that opens that chart filtered to the record you're looking at.

The obvious URLs don't work:

• ?a=q&qid=117&query={10.EX.'2'} opens the chart, but ignores the filter.
• ?a=q&dvqid=117&query={10.EX.'2'} applies the filter, but the chart turns into a table.

The fix is a Quickbase feature that's been around a long time: ask-the-user filters, answered from the URL.

Why query= is ignored and dvqid becomes a table

A saved report (qid=117) runs with its own saved settings. It doesn't take extra filters through query=, so the parameter is dropped.

dvqid means "use report 117 as a starting point and build a new, unsaved report from it." Your query= is applied, but the unsaved report is shown as a table, so the chart type is lost.

What a saved report does accept from the URL is the answer to its own questions. If the report has a filter set to the value (ask the user), you can supply the answer in the URL and Quickbase runs the saved chart with it, no prompt.

The fix, step by step

The example uses two tables: Projects and Tasks. Each task has a Related Project (the reference field), a Status, an Assigned To (Sarah Lee, Mike Chen, Ana Lopez...) and Hours. The chart is a bar chart on Tasks: count of tasks by Status.

1. Open the chart report on the Tasks table and choose Edit (the report settings).
2. Under Filters, choose Show only some of the records.
3. Add a filter: Related Project is equal to, and in the value drop-down pick the value (ask the user).
4. Save the report. Note its report ID (the qid in the URL when you run it, 117 here).
5. Run the chart once by hand. Quickbase asks for a Related Project. Enter 2 and click Display. Look at the address bar: you'll typically see nv=1&v0=2 in the URL. That's the pattern your button will copy, and it's a quick way to check how Quickbase expects the answer to be written.

Filter on the reference field (a Record ID#) rather than on a name when you can. Numbers don't need encoding and two projects can't share one.

The formula URL button

On the Projects table, add a Formula - URL field (call it Task Status Chart) and put it on the form:

URLRoot() & "db/" & [_DBID_TASKS] & "?a=q&qid=117&nv=1&v0=" & ToText([Record ID#])

• qid=117 is the saved chart.
• nv=1 says how many answers are in the URL.
• v0 is the first answer. Quickbase counts from zero.

Click the button on the Website Refresh project and the chart opens with only that project's tasks, still a bar chart, without asking anything.

In the field's properties you can turn on Display as a button so it looks like one on the form.

Multiple filters: nv=2 and v1

Add a second ask-the-user filter to the chart (for example Assigned To is equal to the value (ask the user)) and send two answers:

URLRoot() & "db/" & [_DBID_TASKS] & "?a=q&qid=117&nv=2"
  & "&v0=" & ToText([Record ID#])
  & "&v1=" & URLEncode(User())

The answers go in the same order the ask-the-user filters appear in the report's filter list: the first one is v0, the second v1, and so on. If you reorder the filters, reorder the answers too. Running the report by hand once and reading the URL is the quickest way to confirm the order.

When the button lives on another table

Dbid() means "the table this formula is on." That's right when the button and the chart are on the same table. In the example, the button is on Projects and the chart is on Tasks, so the formula needs the Tasks table's DBID.

Use the table alias Quickbase creates for you, like [_DBID_TASKS] for a table named Tasks (find it under the table's Advanced settings, or in the formula editor's field list). An alias keeps working if you copy the app; a pasted DBID string doesn't.

You may notice Quickbase now redirects old-style db/<dbid>?a=q... links to the newer /nav/app/.../action/q?... address. The qid, nv and v values are carried across, so the formula above keeps working.

The Report Link alternative

If the chart only ever needs to show "records related to this one," you may not need a formula at all.

1. On the Projects form, add the Tasks report link field that the relationship created (or a Report Link field that matches Projects Record ID# to Related Project).
2. In the form element's properties, set the report to your chart.
3. Choose to show it embedded on the form, or as a link.

Quickbase adds the "related to this project" filter for you and the saved chart keeps its type. Use the URL button when you need filters a Report Link can't express, like a second answer or a value that isn't the relationship.

Troubleshooting

• The prompt still appears. The nv count doesn't match the number of ask-the-user filters, or a v value is missing. Count the filters and send one answer for each.
• The chart is empty. The answer doesn't match. Run the report by hand with the same value. For text fields, check spacing and spelling; for multiple-choice fields, send the choice exactly as it's stored.
• Values with spaces, &, or # break the link. Wrap each text answer in URLEncode() (numbers like Record ID# just need ToText()). Without it, an & in a project name ends the parameter early.
• Dates don't match. Send the date in the format you see in the URL when you run the report by hand, or filter on a Record ID# instead.
• It opens the wrong table. The DBID in the formula is the table the button is on, not the chart's table. Use the chart table's alias.
• Answers land in the wrong filter. The order of v0, v1... follows the filter order in the report. Check it after editing the report.

Quick checklist

• A saved chart with one ask-the-user filter per value you'll send
• A Formula - URL field: ?a=q&qid=<chart>&nv=<count>&v0=...
• The chart table's DBID alias, not Dbid(), when the button is on another table
• URLEncode() around every text answer

The original question is in the Quickbase community: Dynamically filter a saved chart report.