How to embed customized reports instead of Default report settings when embedding reports in an Exact Form

  • 0
  • 2
  • Question
  • Updated 5 months ago
  • Answered

Is it possible to produce customized reports in an Exact Form, or is Exact Forms limited to creating reports using the Default Reporting features, which are limited in sorting capabilities.

Thanks!

Photo of Michael

Michael

  • 20 Points

Posted 4 years ago

  • 0
  • 2
Photo of Daniel

Daniel

  • 0 Points
Hi Michael,

Thanks for reaching out via the QuickBase Online Community!

You can in fact use custom reports with sorting and grouping in Exact Forms.  Here's a our Help Article on how to customize Exact Forms, with formatting specifics towards the bottom: http://www.quickbase.com/user-assistance/Default.html#creating_exact_forms_tips.html

Daniel
Photo of Juan

Juan

  • 74 Points
That article doesn't state where to add the Report ID.

The code provided in the article is:

~=qdb.GetURL("DetailsTableDBID", "API_GenResultsTable^query={'DetailsFieldID'.EX.'"+ field["MasterSourceField"]+"'}^options=nvw.ned.phd.nfg.sortorder-A.groupby-V^slist=DetailsGroupFieldID^ts="+ new Date().getTime());~


And then it says to replace the "ReportID" with the ID of the report you want to embed. Where in the code is "Report ID"???
Photo of Bridgette Bearss

Bridgette Bearss

  • 62 Points
Juan did you ever get a response on this?  Trying to accomplish the same now.
Photo of Anthony Guillen

Anthony Guillen

  • 320 Points 250 badge 2x thumb
Any luck on anyone figuring this out??
Photo of Anthony Guillen

Anthony Guillen

  • 320 Points 250 badge 2x thumb
I never did figure out the Report ID code thing, but I did figure out how to essentially replicate the report I wanted by using "clist" and "slist" parameters.  Look at the link below, which explains the parameters of the API_GenResultsTable call reference article. I basically added "clist" parameter to specify which field I wanted, and replaced the "slist" parameter in the formula (which does subtotals) with a simple "slist" parameter specifying the field to use to do a simple sort.  

https://help.quickbase.com/api-guide/index.html#gen_results_table.html
(Edited)
Photo of Amanda Torrisi

Amanda Torrisi

  • 164 Points 100 badge 2x thumb
I am also trying to do the same...
I have been able to get the appropriate columns to show up using "clist" and "slist" parameters but I do not want the edit/view icons to appear on the exact form... I do not want to disable the edit/view feature on the default report since we use that function from the table home page... but I do not know how to get a specific report to show up... Has anyone received any insight on this?
Photo of Anthony Guillen

Anthony Guillen

  • 320 Points 250 badge 2x thumb
Amanda, on my exact form, it shows up as a simple table with no edit/view icons at all. See below.  Here is my query:

~=qdb.GetURL("tokenidhere", "API_GenResultsTable^query={'36'.EX.'"+ field["Work order ID#"]+"'}AND{'40'.LT.'20'}^clist=26.22.24.17.19^slist=40^ts="+ new Date().getTime());~

Again, I could never figure out how to call a specific report by ID because the example query doesn't have it and customer service could not help, and so I replicated the report. The first part of the query uses a boolean to match field 36 with the record ID (Work order ID# in my case) AND field 40 being less than the value 20.  Clist calls fields 26,22,24,17, and 19; which are the 5 fields you see below.  And slist sorts by field 40 which i don't even use in the table, but it works nevertheless. No edit/view icons show up.

Perhaps you were using a simple call for the related table like ~Orders~ where "Orders" is the report link field?  If I recall, that does give you a full table, but my problem with that was that it had all the columns and maybe it did have edit/view icons, too.  Which is why I had to use a custom query to show the table the way I needed it.
Photo of Amanda Torrisi

Amanda Torrisi

  • 164 Points 100 badge 2x thumb
Thanks for the quick response!, I compared your query to mine:

My query:
~=qdb.GetURL("bmvugbz6n", "API_GenResultsTable^query={'24'.EX.'"+ field["Record ID#"]+"'}^clist=6.7.49^ options=phd.nfg.&slist=6.sortorder-A."+ new Date().getTime());~

I am scared to change it since it took me so long to get it to only show the related records.... I cant figure out which part is causing the add/edit icons to show up! I am wondering if it is just a setting somewhere! 
Photo of Amanda Torrisi

Amanda Torrisi

  • 164 Points 100 badge 2x thumb
Also.. I did find that you can add: 
  • ned to omit the edit icons in HTML table format

  • nvw to omit the view icons in HTML table forma

but i cannot get this to work in the above.... 
Photo of Amanda Torrisi

Amanda Torrisi

  • 164 Points 100 badge 2x thumb
Sorry.... LAST UPDATE- I had an extra space. This worked: 

~=qdb.GetURL("bmvugbz6n", "API_GenResultsTable^query={'24'.EX.'"+ field["Record ID#"]+"'}^clist=6.7.49^options=phd.nfg.ned.nvw.&slist=6.sortorder-A."+ new Date().getTime());~
Photo of Anthony Guillen

Anthony Guillen

  • 320 Points 250 badge 2x thumb
Interesting.. well, for whatever reason my query had those options off by default, but now that i look at the Options in the API guide, good to know they are there!
Photo of Mabel Liang

Mabel Liang

  • 72 Points
Hi all,

My comment keeps getting rejected, so I’ve split it up.

I was able to get some help from a couple of people at Quick Base support with this.  Some of what's below is quoted from them, some is my own commentary.

the Standard Embedded table code in Exact form:
~=qdb.GetURL("bji7iw9jh", "API_GenResultsTable^qid=32^ts=" + new Date().getTime());~

The Standard Embedded code uses the qid= "Report ID", in the code to specify the table report you want to use in the Exact form from the table you print from.

What surprised me is that this printed the entire report for the child table, not just the records that are related to the particular parent that the Exact Form is referencing.

When I filed another case asking how to limit the report to just the related records, I was advised to use a Report Link field.  

1. To specify that the exact form should only show records relating to the current parent record you could simply create a report link between these two tables and then call that field in your exact form code. For example, say the table you're creating an exact form for is called Invoices, and the report link field that connects to your Orders details table is called Orders. Within your exact form, the field code you'd type would be: ~Orders~. Then when you generate the Exact Form this would appear just as a report link would. 

Note: you can only use the default report with this.  I remembered that you can specify a report when using a Report Link, but it turns out that's only in the regular Forms interface.  If you want to do a custom report, you have to build it.

That uses the syntax:

Custom field list Exact Form Code:
~=qdb.GetURL("bjmzv9fnt", "API_GenResultsTable^query={'11'.EX.'"+ field["Record ID#"]+"'}^options=nvw.ned.phd.nfg.sortorder-A.groupby-V^clist=11.14.6.7.8^ts="+ new Date().getTime());~
Photo of Mabel Liang

Mabel Liang

  • 72 Points
Second part of comment:

2. I asked about adding more sorting and grouping compared to what was in the help article in the help article Daniel had mentioned:
http://www.quickbase.com/user-assistance/Default.html#creating_exact_forms_tips.html

To change the sorting and grouping options you would just need to alter your call to follow the guidelines laid out in the API_GenResultsTable API guidelines to reflect the correct combination of sorting and grouping options you'd like. These can be accessed here: https://help.quickbase.com/api-guide/index.html#gen_results_table.html%3FTocPath%3DQuick%2520Base%25...

I had to play around using the "Show the expanded URL for this report" to find that 
the number of "V"s in groupby-V  determines how many of the fields in the slist get grouped.  The API guide isn't that specific, but you can find things out by playing around. 

Hope this helps somebody!
Photo of Keith

Keith, Champion

  • 1,148 Points 1k badge 2x thumb
If Exact Forms doesn't provide you with all the flexibility you are looking for, take a look at Exact Forms Plus!

https://juicedtech.quickbase.com/db/bdht2xebx


Keith
Photo of Sara C

Sara C

  • 184 Points 100 badge 2x thumb
Thank you for this thread. I was able to get rid of the edit and view icons in the report using the "options" that Amanda mentioned above. Here's an example using options: 
~=qdb.GetURL("MYTABLEID", "API_GenResultsTable^query={'10'.EX.'"+ field["Record ID#"]+"'}AND({'6'.EX. 'New'}OR{'6'.EX. 'Approved'})^clist=3.7.6.42.62.28^slist=3^options=ned.nvw.nfg.phd^ts="+ new Date().getTime());~