Update all records in a specific report

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I have a payroll application that's being developed.  I need to take the records which have been approved and export them to be sent to the 3rd party payroll provider.  At some point before/after the export, I need to change the status of all the records in a given report from Approved to Submitted, and add a timestamp in one field.  Each manager would have their own report showing the Approved records, but I can't figure out how I can have all those records modified with some automation (I don't want to use grid edit or anything else that's manual).
Photo of Chris MacFarlane

Chris MacFarlane

  • 202 Points 100 badge 2x thumb

Posted 2 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 64,648 Points 50k badge 2x thumb
Do these records have anything in common? For example do they all fall within a payroll period?

A native ACTION may be xble to do this but the set of records to be updated need to be able to be defined by a report link.

Tell us more about the filters in the report.
Photo of Chris MacFarlane

Chris MacFarlane

  • 202 Points 100 badge 2x thumb
The report parameters would be:
Manager Name = Current User
AND
Status = Approved

There might also be a period ending date, but that's still up in the air depending on how some of the other development goes.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 64,648 Points 50k badge 2x thumb
This can be done with script, but that is coding.
It can be done with a formula URL button and using API_RunImport

But let's see if we can do this with no code.

Make a new table called Payoll Approval.    On that record put a user field (and Make the Key field be a User field),  and a Report Link field and a formula text field called [Link to my Payroll Entries for Approval]

For the [Link to my Payroll Entries for Approval] field make the formula

List("-",UserToName(User()), "Approved")

The effect of that will calculate to John Smith - Approved.

Then on the Payroll records, make a similar formula

List("-", [Manager name userid], "Status")

Use those two fields to configure the Report Link field.

Put the Report Link field on the form for the Payroll Approval and list the records right on the form.

You should see all the records waiting to be Approved for that manager User on the form.

So what we have done is to have created a Report Link field which defines the records to be updated.

Now, make a new checkbox field on the Payroll Approval record.

Now, make an ACTION on the Payroll Approval form that says when the checkbox is checked

EDIT the related records defined by that Report Link and change the value in the field [Status] to the value Approved.

Take a shot at that and we can tidy up the rough edges once you get the ACTION to fire.

Note that when you check the checkbox, you will need to refresh the record  to see the effect.  The effect should be that the report link field goes empty as a report, since there are no longer records waiting  to be Approved.
Photo of Chris MacFarlane

Chris MacFarlane

  • 202 Points 100 badge 2x thumb
Hi - Thanks for your help so far, this is working great.....

I'm up to "Now, make an ACTION on the Payroll Approval form that says when the checkbox is checked"

I don't have an option to Edit the Related records when the checkbox.  I've tried to make the dynamic rule both based on either the checkbox being checked or the record being saved AND the checkbox being checked, but I can't see where I'd instruct the application to edit the related records.  

Do I need to have a relationship between the tables, or is there something else I've missed?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 64,648 Points 50k badge 2x thumb
By Action, I am referring to an Action, which is quite different that a form rule.

https://help.quickbase.com/user-assistance/creating_a_quickbase_action.html
Photo of Chris MacFarlane

Chris MacFarlane

  • 202 Points 100 badge 2x thumb
Okay - I suspected that might be the case.

Let me work on that and I'll come back with (hopefully good) news.
Photo of Chris MacFarlane

Chris MacFarlane

  • 202 Points 100 badge 2x thumb
It worked beautifully!!!!

So now, as more records are created and approved, we would simply need to refresh that same record in the Payroll Approval table and repeat that process, and that we would need 1 record in the Payroll Approvals table for each manager.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 64,648 Points 50k badge 2x thumb
Good for you!  
Thx for letting me know.

Hurray for Low code / No Code.  In this case we managed to do mass updates with No Code.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,964 Points 20k badge 2x thumb
I may be too busy to reply but my robot informed me of your comment.
Photo of Chris MacFarlane

Chris MacFarlane

  • 202 Points 100 badge 2x thumb
I think the only improvement I need to make to it is to get a button to do multiple steps for the user, and come out of it with the Process for Submission checkbox unchecked again.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 64,648 Points 50k badge 2x thumb
I'm not sure what the "Multiple Steps" are but you can make a simple formula URL button to Toggle check the box and then trigger the ACTION when the checkbox is changed.

This should work to do that

// make a formula variable to be the opposite of the checkbox condition.
var bool NewValue = not [my checkbox field] 


var text URL =
URLRoot() & "db/" & Dbid () & "?act=API_EditRecord&rid=" & [Record ID#]
& "&_fid_55=" & URLEncode($NewValue);

"javascript:" &
"$.get('" &
$URL &
"',function(){" &
"location.reload(true);" &
"});" & "void(0);"

 Change the 55 to the field ID of you checkbox field.

Change the ACTION to trigger on any time the check box is checked.

Take the checkbox off the form and just show the URL button in View mode.
Photo of Chris MacFarlane

Chris MacFarlane

  • 202 Points 100 badge 2x thumb
I have the formula (I had to add ; after the [Process for Submission]. 
// make a formula variable to be the opposite of the checkbox condition.
var bool NewValue = not [Process for Submission] ;


var text URL =
URLRoot() & "db/" & Dbid () & "?act=API_EditRecord&rid=" & [Record ID#]
& "&_fid_9=" & URLEncode($NewValue);

"javascript:" &
"$.get('" &
$URL &
"',function(){" &
"location.reload(true);" &
"});" & "void(0);"

The button is on the form and when clicked it re-loads the page but the value of the checkbox doesn't change.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 64,648 Points 50k badge 2x thumb
The problem is probably not having supplied an application token.

you can test that by commenting out or temporarily removing that whole JavaScript block and just replacing it with

$URL

That will run the URL formula and expose the error message

If that is the problem, you can read the help on Application Tockens and decide if you want to use them.

If you don't, you can disable their need in Settings, for the Application, and App Properties.

Else the formula will need adjusting to add a line just before the semicolon

& "apptoken=xxxxxxxxxxxx";
Photo of Chris MacFarlane

Chris MacFarlane

  • 202 Points 100 badge 2x thumb
That worked, thanks so much for your help with this project, it's been great.