Discussions

Expand all | Collapse all

API_RunImport with Summary table

  • 1.  API_RunImport with Summary table

     
    Posted 10-11-2013 15:33
    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


  • 2.  RE: API_RunImport with Summary table

    Posted 10-11-2013 22:11
    I wish you could, but there is no API for that.

    But here is a thought.

    You probably have a table of items in your app. Create a summary field on that table of Weight Shipped Today. Then make a saved Table to Table copy report to your Shipments Table from the Item Table and just map the fields across. Include a filter on the saved import

    where Weight today > 0

    so you don't export your whole item table! - just the ones that Shipped today.


  • 3.  RE: API_RunImport with Summary table

    Posted 10-14-2013 04:50
    >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.


  • 4.  RE: API_RunImport with Summary table

    Posted 06-06-2017 22:54
    Dan, I know this post was ages ago but: What is the code in the "Import Summary Source Report Into Destination Table" button itself?


  • 5.  RE: API_RunImport with Summary table



  • 6.  RE: API_RunImport with Summary table

    Posted 06-06-2017 23:15
    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?


  • 7.  RE: API_RunImport with Summary table

     
    Posted 11-05-2013 22:16
    thanks for both answers.  I like to stay in native quickbase when possible, this worked like a charm.  Thanks,
    Joe


  • 8.  RE: API_RunImport with Summary table

    Posted 10-16-2018 14:05
    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!



  • 9.  RE: API_RunImport with Summary table

    Posted 10-16-2018 14:41
    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.