Jump to content
  • 0

Table insert from results set



How can I write (insert) the rows in a DataPage report result set into a table?

Table of details, with multi-level rollup, is updated by many users with  login RLS.

Results change often and unpredictably.  Have a view that presents the correct data from several tables  for a rollup report that counts instances of an item by a grouping factor, rolled up through the levels.  A snapshot of the rollup report at  the end of a week is used to guide deliveries based on the counts as of that time.

Need to be able to write that snapshot (the rollup DataPage tabular report result set) into a history table with same columns.   Purpose is to have accurate history by week of those counts to derive accurate totals for period(s) and monitor trends over time etc.

I know I can export and then import append into a table, but was hoping for an automated method initiated via button or similar.

New to Caspio, but not afraid of code, at least to copy and paste :)  Thanks for any help.



Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0

Ok, here's more information on the scenario;  

School Counselors  from schools  across a county maintain demographic records (no personal info) of students at hunger risk.  We deliver weekend food packs to the schools on Fridays to ensure these kids have food over the weekend (school meals often their only sure meal).   The list changes  unpredictably as students dome and go from schools or in and out of the program (parental permission required).  The schools are in a county, the county is in a region.

On Friday morning a Datapage tabular  grouping report is run to show several bits of joined information, including the count of students per school as of that Friday morning - a snapshot of the current state of the counselor's detail lists (these are RLS protected).  This report is then used as guidance to a team of volunteers who deliver the count of food packs to the school on that Friday.    This is still in proof of concept mode but all testing indicates this is delivering the right results and accurately reflecting the counselors' entries, deletions, edits, etc.

What I want to add is a way to capture that snapshot, with date, to a history table so that over time, totals for time periods and trends, etc,, can be reported,  analyzed, etc.     Don't want the counselors to have to deal with dates, status switches, etc., just maintain their simple , current list - what you see is what is real at that moment.

I have a multi-table joined view that is behind the rollup tabular Friday report that shows all the information.  A way to write the history snapshot from the view would also work.  Ok if it is  done in the Bridge instead of web page.  Just need to capture the the snapshot of the weekly delivery info  into history week-to-week, with a date added (at authorized user discretion).

Thanks for responding.  Hope that helps to understand.   Open  to different approaches that achieve same end,   including a little raw SQL with some help on where and how.




Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...