Run API_DoDuery using qid for a summary report that uses paramerters

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
  • (Edited)
I have a table of contractor data with first name, last name, start and end dates, burden amount, ball rate and pay rate. I want to summarize the bill, pay and burden amounts and do other averages and calculations on that data as of a given date, i.e. all contractors whose start date is on or prior to June 1st and end date is on or after June 1st. Then, I want to copy the result from that summary into a historical table so I can report on data trends from month to month.

What I have done to date is to create a summary report prompting the user for the start and end date. The user then enters the same date for each prompt, e.g. June 1st, and the report runs successfully.  The user can then copy these records to the history table using the option on the report page under the More button. This issue with this method is that the "As Of Date", June 1st in this example, is not entered into the history table and the user needs to enter it into the copied records in the history table. This is not an efficient method.

To solve this, I want to use jquery and javascript to prompt the user to enter the "As of date", query the summarized data and then use API_AddRecord to write the rows into the history table using the correct "As of date". The issue with this solution is that I cannot run the summary report using the qid and pass in the "as of date" to summarize the proper set of records.  Is there another/easier method of solving this other than querying all the records within my "as of date" and writing all the calculations myself, prior to writing the records to the history table?

Since this post, I came up with the idea of relating a table that contains the  "as of date " to my contractor data table. This works as the related id will always be 1. I just update the date in the "as of date" table and the report does report the correct values when I run it on the contractor page.

However, when I run the API_DoQuery I get the following error:

<errtext>Invalid input</errtext>
<errdetail>API_DoQuery only supports table-type queries.</errdetail>

My code for the query, which is chained to the api_EditRecord call, is:

var promiseQRY = $.get(dbidContractor , {
act: "API_DoQuery",
qid: "8",
clist: ""

Any ideas on why I'm getting this error?
Photo of Robert


  • 214 Points 100 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of John Thomas

John Thomas

  • 1,082 Points 1k badge 2x thumb
Did you given the table id and apptoken?

Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
The API method API_DoQuery cannot return the XML associated with a summary report - it can only return the XML associated with a table report. What you have to do is (1) grab the XML associated with the query, (2) Convert XML to JSON, (3) using Underscore's groupBy() method to aggregate the data, (4) template the data to HTML using Mustache (or Underscore's template() method and/or POST the aggregated data to your history table.

It may sound complicated but the code is short because of the abstractions involved.
Photo of Robert


  • 214 Points 100 badge 2x thumb
Thanks. I did see other posts that said you could use qid with a summary report so I gave it a try. I'll change my approach as suggested.