How do I set particular records to be a threshold, and then a month later run a report for all records that were created after those thresholds?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered
I might be over thinking this one, but not sure.

Projects Table is parent to Time Details Table.

Let's pretend that there are 10 projects and that there are hundreds of Time Detail records that are created in a month's time for those projects.  One way or another, I want to "stamp" or otherwise "mark" the most recently created Time Detail record for each project using perhaps a button or a checkbox or whatever.  Then let's say a month later I want to run a report in the Time Details Table,  perhaps (ideally) the same report I used in the first place, and have it display all the records grouped by project that were created after the ones that I had previously "stamped".  While looking at this report I would then "stamp" the newest records, one for each project.  Then a month later, I would run the report again and it would display all the records created since the second "stamp".  Rinse and repeat as needed.   Suggestions?  

Thanks
Photo of Robin CC

Robin CC

  • 190 Points 100 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,944 Points 50k badge 2x thumb
I'm not sure if you want to stamp the Child records for one project at a time or for all of them, but the principle would be the same as to one way to do it.

The API_RunImport command is very powerful as a tool to mass update records.

Let's say that you want to look at a project and then push a button on the Project record to stamp all of its child records which have not been Reviewed with Today's date into a field call [Date Reviewed].

So, you could set up a Saved import, by going into import Export and building a table to table copy where the source table is the same child table you are trying to update. In other words you care copying the table into itself, and just updating one field for each existng Record ID#.  You map just one field. Set the rest to "do not update"  The merge field will be based on the Record ID and the only field to be mapped would be the field  [Date Reviewed]

Set up a formula field called [Today] with the formula Today()

Then set up the field mapping to load that Today field into the [Date Reviewed] field.

Set the filter on the saved Import to only update record where the [Date Reviewed] is blank.



 Then build a URL formula field to run that API and refresh the project record.


var text URLONE = urlroot() & "db" & [_DBID_ of the child Table] & "?act=API_RunImport&ID=10;

var text URLTWO = urlroot(0 & "db/" & "?a=dr&rid=" & [Record ID#];

$URLONE & &"rdr=" & URLEncode($URLTWO)


Then you can run a report of any detail records where that [Date Reviewed] field is blank.


The project record could have an embedded table of Child record which have not been reviewed. 

When the Project record is refreshed by pushing the button, that embedded table should be blank.
Photo of Robin CC

Robin CC

  • 190 Points 100 badge 2x thumb
Thanks Mark.  I believe I got this to work as you intended.  Every record in the Projects Table now has a "stamp" button, and no matter which button I press it stamps all the records in the Time Details Table that were not previously stamped.   

Now my question is: How do I set it up so that the button for a particular project only stamps the Time Detail records associated with that one project as opposed to all of them?     Do I need to modify the URLONE variable to be an IF statement?  Or do I need to modify the import somehow?
Photo of Robin CC

Robin CC

  • 190 Points 100 badge 2x thumb
.