How to add a child table summary report to a parent exact form?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
I have the solution for displaying a filtered table report on an exact form, but I want to display a filtered summary report. I've created the summary report in the child table, but when added to the parent exact form with the below formula, it displays all records in the child table...instead of just the ones related to the parent. How do I fix this?

~=qdb.GetURL("bkhfdiuys", "API_GenResultsTable^qid=11^ts=" + new Date().getTime());~

bkhfdiuys = child table, 11= summary report number in child table

Thank you!

*Update - Problem figured out, screenshot is of the updated exact form.

Photo of Edgar_Solutions

Edgar_Solutions

  • 10 Points

Posted 3 years ago

  • 0
  • 1
Photo of Jack

Jack, Champion

  • 50 Points
Hi,

Here is an example query. I've always built the queries manually in exact forms as this seems/is more reliable:

~=qdb.GetURL("feufuwe3", "API_GenResultsTable^query={'63'.TV.'"+ field["Pri Key"]+"'}AND({'13'.XEX.''})&clist=13.12.11.10.6.2.17&options=sortorder-D.gb-X.nos.ned.nvw.phd");~




~=qdb.GetURL("feufuwe3", "API_GenResultsTable^query={'63'.TV.'"+ field["Pri Key"]+"'}AND({'13'.XEX.''})&clist=13.12.11.10.6.2.17&options=sortorder-D.gb-X.nos.ned.nvw.phd");~


This section is filtering on record ID

~=qdb.GetURL("feufuwe3", "API_GenResultsTable^query={'63'.TV.'"+ field["Pri Key"]+"'}AND({'13'.XEX.''})&clist=13.12.11.10.6.2.17&options=sortorder-D.gb-X.nos.ned.nvw.phd");~


Further Filter - So here you could add the word Bob or have multiple filters such as below

~=qdb.GetURL("feufuwe3", "API_GenResultsTable^query={'63'.TV.'"+ field["Pri Key"]+"'}AND({'13'.XEX.'Bob'})AND({'11'.XEX.'Amy'})&clist=13.12.11.10.6.2.17&options=sortorder-D.gb-X.nos.ned.nvw.phd");~

And then finally columns to be returned (fields you want on your report).

~=qdb.GetURL("feufuwe3", "API_GenResultsTable^query={'63'.TV.'"+ field["Pri Key"]+"'}AND({'13'.XEX.'Bob'})AND({'11'.XEX.'Amy'})&clist=13.12.11.10.6.2.17&options=sortorder-D.gb-X.nos.ned.nvw.phd");~



You may find these pages useful:

https://www.quickbase.com/api-guide/gen_results_table.html


This although its the api do query, it lists query string operators i.e. XEX, CT, TV ....etc

https://www.quickbase.com/api-guide/do_query.html
Photo of Edgar_Solutions

Edgar_Solutions

  • 10 Points
Jack,
Thanks for the response. Unfortunately this does not solve my problem. I have the solution for displaying a filtered table report on an exact form (exactly what you have listed above), but I want to display a filtered SUMMARY report. The SUMMARY report displays just fine when using the "API_GenResultsTable^qid=11^ts=", but it shows ALL records not ones related to the parent table. Can you provide any further help on this? Thanks.
Photo of Jack

Jack, Champion

  • 50 Points
Ok try this:

~=qdb.GetURL("bkhfdiuys", "API_GenResultsTable^qid=11&query={'63'.TV.'"+ field["RECORD ID FIELD"]+"'}^ts=" + new Date().getTime());~

{'63'.TV.'"+ field["RECORD ID FIELD"]+"'}

63 should be the field ID of the lookup field (which looks up the parent id) in the child record table (a lookup field)
RECORD ID FIELD should be the name of the record id or key field in the parent table.

This should mean that for example if the form is generated from the parent record ID 100.

Parent 100
Many child records with the parent id 100
Photo of Edgar_Solutions

Edgar_Solutions

  • 10 Points
I tried this and it isn't coming up with an error, but it isn't running this part of the code:

query={'30'.TV.'"+ field["Dealership ID#"]

30 is the field ID# for the "Related Dealership" in the child table. It's the same field ID that works in this normal filtered table report that is working fine on the exact form, so that's the right field, correct?

~=qdb.GetURL("bkhfdiuys","API_GenResultsTable^query={'30'.EX.'"+ field["Dealership ID#"]+"'}AND{'6'.CT.'Interior'}AND{'43'.EX.'1'}^clist=6.7.16.8.24.12.34.35.37.20.38^options=nvw.ned.phd.nfg.sortorder-A^slist=7")~

I also tried, query={'30'.EX.'"+ field["Dealership ID#"] and it didn't work either. Am I doing something wrong here?
Photo of Jack

Jack, Champion

  • 50 Points
Try this:

~=qdb.GetURL("bkhfdiuys","API_GenResultsTable&qid=11&query={'30'.TV.'"+ field["Dealership ID#"]+"'}AND{'6'.CT.'Interior'}AND{'43'.EX.'1'}&clist=6.7.16.8.24.12.34.35.37.20.38&slist=7&options=nvw.ned.phd.nfg.sortorder-A");~
Photo of Jack

Jack, Champion

  • 50 Points
You probably actually only need this:
~=qdb.GetURL("bkhfdiuys","API_GenResultsTable&qid=11&query={'30'.TV.'"+ field["Dealership ID#"]+"'}&ts=" + new Date().getTime());~
Photo of Edgar_Solutions

Edgar_Solutions

  • 10 Points
~=qdb.GetURL("bkhfdiuys","API_GenResultsTable&qid=11&query={'30'.TV.'"+ field["Dealership ID#"]+"'}&ts=" + new Date().getTime());~

This is the one I've tried and it doesn't work. It's like QB doesn't care about the query.
Photo of Jack

Jack, Champion

  • 50 Points
Can I just check what your using to call the exact form is it the dealer ID as shown below?

URLRoot() & "db/" & Dbid() & "?a=dbpage&pagename=" & URLEncode("exactform.html") & "&clist=a&rid=" & [Dealership ID#]
Photo of Edgar_Solutions

Edgar_Solutions

  • 10 Points
URLRoot() & "db/" & Dbid() & "?a=dbpage&pagename=" & URLEncode("Proposal.html") & "&clist=a&rid=" & [Dealership ID#]
Photo of Jack

Jack, Champion

  • 50 Points
Very strange. Ok try creating a new lookup via your relationship and then amend 30 to the field ID of that lookup.
Photo of Edgar_Solutions

Edgar_Solutions

  • 10 Points
Just added a new lookup field of the Dealership ID# (fid 46) and it still doesn't work in either formula below:

~=qdb.GetURL("bkhfdiuys","API_GenResultsTable&qid=10&query={'46'.TV.'"+ field["Dealership ID#"]+"'}&ts=" + new Date().getTime());~

 ~=qdb.GetURL("bkhfdiuys", "API_GenResultsTable^qid=10&query={‘46’.TV.'"+ field["Dealership ID#"]+"'}^ts=" + new Date().getTime());~
Photo of Jack

Jack, Champion

  • 50 Points
I think it might be best to log a support case with Quickbase. If you go to help and manage cases and log a case there they should be able to advise on this. From what I can see from all the guidance available the query is correctly structured.

The only thing i can think of is possibly that because summary reports are based calculated based on record values loaded into the table on call is that the exact form doesn't have the ability to generate them.
Photo of Edgar_Solutions

Edgar_Solutions

  • 10 Points
I will try that, thanks for trying. The exact form will display the summary report if you just use the report id (below), but it won't filter! I don't want to see all of them! I tried some different filters in the actual report, but couldn't get any of them to work.

~=qdb.GetURL("bkhfdiuys", "API_GenResultsTable^qid=11^ts=" + new Date().getTime());~
Photo of Edgar_Solutions

Edgar_Solutions

  • 10 Points
I figured it out!!

On the QID 10 report in the childs table, I put a filter for Related Dealership is equal to Ask the User. Then I used this formula:
~={qdb.GetURL("bkhfdiuys", "API_GenResultsTable^qid=10^options=phd.nfg.^nv=1^v0="+ field["Dealership ID#"]);}~

It displays a nice summary that is filtered for only this Dealership. Thanks for your time, it helped me work through this.
Photo of Jack

Jack, Champion

  • 50 Points
No problem, sorry I wasn't more help. I haven't come across the issue before, as I usually use filtered table reports which definitely do work as mentioned above. Thanks also for posting the resolution myself and others will find this really useful.