Is it possible to use the API_RunImport to create a URL and automatically imports the data in a Summary Report in one table, to another table.
I have one table that has items and their weight, a summary reports sums up all the weight of each item.
I need to generate shipments in another table based on this summary report.
I can easily do it by opening the summary report, clicking import to other table, and importing.
But i would like the end user to do this daily by clicking one button. Is this possible?
I see that this can be done with a Table Report as the source, but I don't see how to do it with a summary report
Thanks,
Joe
I have one table that has items and their weight, a summary reports sums up all the weight of each item.
I need to generate shipments in another table based on this summary report.
I can easily do it by opening the summary report, clicking import to other table, and importing.
But i would like the end user to do this daily by clicking one button. Is this possible?
I see that this can be done with a Table Report as the source, but I don't see how to do it with a summary report
Thanks,
Joe
Posted 6 years ago
-
590 Points
Ⲇanom the ultimate (Dan Diebolt), Champion
-
30,184 Points
>I wish you could, but there is no API for that.
Come on now don't be ridiculous. Be the API you want to see.
This example below has two tables Source and Destination. The Source table has 20 records (which you can't modify) consisting of fields [Category], [Item], and [Cost]:
Source Records List All
https://haversineconsulting.quickbase.com/db/big4sysbf?a=q&qid=1
The Source table has this summary report:
Source Summary Report
https://haversineconsulting.quickbase.com/db/big4sysbf?a=q&qid=5
You can purge all the records in the Destination table by clicking on the appropriate link shown on the application dashboard.
Summary Report to Table Import Application Dashboard
https://haversineconsulting.quickbase.com/db/big2cgfyv
If you then click on the link labeled "Import Summary Source Report Into Destination Table" on the application dashboard, the data in the summary report will be newly imported into the Destination table.
Here is the code:
DoIt.js User Defined Page
https://haversineconsulting.quickbase.com/db/big2cgfyv?a=dbpage&pagename=DoIt.js
Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=225
In a nutshell here is what the script does: Using a utility function iframeLoaded(id, url) an <iframe> is loaded into the current page (the Source table dashboard) and is hidden from view. The url specified is the Source table summary report. This function returns a jQuery promise which will cause other code to execute when the promise resolves (ie when the <iframe> loads). When the promise resolves, the code reaches into the contents of the newly loaded <iframe> and extracts the tabular data from the summary report, converts it to a blob of csv data and posts it into the Destination table using API_ImportFromCSV. This is all done in about 50 lines of simple code.
If you grock what is going on, you can see that this same technique can implement any custom API you can dream up by ripping data out of any and all QuickBase pages on the planet that you have access to and returning the processed data in any format you want through the use of a promise.
Never let is be said that "there is no API for that". There is nothing but API.
Come on now don't be ridiculous. Be the API you want to see.
This example below has two tables Source and Destination. The Source table has 20 records (which you can't modify) consisting of fields [Category], [Item], and [Cost]:
Source Records List All
https://haversineconsulting.quickbase.com/db/big4sysbf?a=q&qid=1
The Source table has this summary report:
Source Summary Report
https://haversineconsulting.quickbase.com/db/big4sysbf?a=q&qid=5
You can purge all the records in the Destination table by clicking on the appropriate link shown on the application dashboard.
Summary Report to Table Import Application Dashboard
https://haversineconsulting.quickbase.com/db/big2cgfyv
If you then click on the link labeled "Import Summary Source Report Into Destination Table" on the application dashboard, the data in the summary report will be newly imported into the Destination table.
Here is the code:
DoIt.js User Defined Page
https://haversineconsulting.quickbase.com/db/big2cgfyv?a=dbpage&pagename=DoIt.js
Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=225
In a nutshell here is what the script does: Using a utility function iframeLoaded(id, url) an <iframe> is loaded into the current page (the Source table dashboard) and is hidden from view. The url specified is the Source table summary report. This function returns a jQuery promise which will cause other code to execute when the promise resolves (ie when the <iframe> loads). When the promise resolves, the code reaches into the contents of the newly loaded <iframe> and extracts the tabular data from the summary report, converts it to a blob of csv data and posts it into the Destination table using API_ImportFromCSV. This is all done in about 50 lines of simple code.
If you grock what is going on, you can see that this same technique can implement any custom API you can dream up by ripping data out of any and all QuickBase pages on the planet that you have access to and returning the processed data in any format you want through the use of a promise.
Never let is be said that "there is no API for that". There is nothing but API.
-
836 Points
Dan, I know this post was ages ago but: What is the code in the "Import Summary Source Report Into Destination Table" button itself?
(Edited)
Ⲇanom the ultimate (Dan Diebolt), Champion
-
30,184 Points
Its from 2013. Here it is:
Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=225
Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=225
Ⲇanom the ultimate (Dan Diebolt), Champion
-
30,184 Points
Hey you just gave me a great idea. I could easily write a Service Worker that detected the fetching of a Summary Report with an extra API argument and returned XML or JSON instead of HTML. In a nutshell we would just be extending the API to handle Summary Reports.
I will try to incorporate this into my Service Worker Travel Log - 30 Days in the Hole. The only problem is that my editorial schedule is set in stone and this would push it to 31 Day in the Hole. What a perplexing problem?
I will try to incorporate this into my Service Worker Travel Log - 30 Days in the Hole. The only problem is that my editorial schedule is set in stone and this would push it to 31 Day in the Hole. What a perplexing problem?
(Edited)
-
172 Points
Dan, this is great. I think I've got it really close but when I click the button to run the script, I am only creating 200 records at a time. If I click the button again, it creates 200 more, etc. Any idea why that may be?
Also, I turned off the app "use API" option to test this because I figured it might complicate matters. If I turn it back on, how do you insert the App Token into your script? Thx!
Also, I turned off the app "use API" option to test this because I figured it might complicate matters. If I turn it back on, how do you insert the App Token into your script? Thx!
(Edited)
Ⲇanom the ultimate (Dan Diebolt), Champion
-
30,154 Points
Bear in mind that this thread is five years old and if I had to do it over again I might use slightly different code.
>I am only creating 200 records at a time.
Since the demo copied rows in a displayed summary report to a table I find it a bit unusual that you have 200 rows in the displayed summary report. Without a lot of investigation it looks likely to me that you are copying a table report with more than 200 records and the report has paging (possibly displaying 200 records at a time with previous and next buttons). Just a quick guess on my part.
Also note that this demo scraped the data out of the displayed summary report - it did not use the API to query for the underlying data. As a result there could be minor differences between the displayed data and the underlying data (due to rounding / formatting differences).
It would be best to provide more details (such as your code) of your scenario and ask a new question.
>I am only creating 200 records at a time.
Since the demo copied rows in a displayed summary report to a table I find it a bit unusual that you have 200 rows in the displayed summary report. Without a lot of investigation it looks likely to me that you are copying a table report with more than 200 records and the report has paging (possibly displaying 200 records at a time with previous and next buttons). Just a quick guess on my part.
Also note that this demo scraped the data out of the displayed summary report - it did not use the API to query for the underlying data. As a result there could be minor differences between the displayed data and the underlying data (due to rounding / formatting differences).
It would be best to provide more details (such as your code) of your scenario and ask a new question.
