Run API_DoDuery using qid for a summary report that uses paramerters
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:
<qdbapi>
<action>API_DoQuery</action>
<errcode>2</errcode>
<errtext>Invalid input</errtext>
<errdetail>API_DoQuery only supports table-type queries.</errdetail>
</qdbapi>
My code for the query, which is chained to the api_EditRecord call, is:
var promiseQRY = $.get(dbidContractor , {
act: "API_DoQuery",
qid: "8",
clist: "1.2.3.4.5.6.7"
});
Any ideas on why I'm getting this error?
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:
<qdbapi>
<action>API_DoQuery</action>
<errcode>2</errcode>
<errtext>Invalid input</errtext>
<errdetail>API_DoQuery only supports table-type queries.</errdetail>
</qdbapi>
My code for the query, which is chained to the api_EditRecord call, is:
var promiseQRY = $.get(dbidContractor , {
act: "API_DoQuery",
qid: "8",
clist: "1.2.3.4.5.6.7"
});
Any ideas on why I'm getting this error?
