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).

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.

The report parameters would be:Manager Name = Current UserANDStatus = ApprovedThere might also be a period ending date, but that's still up in the air depending on how some of the other development goes.

This can be done with script, but that is coding.It can be done with a formula URL button and using API_RunImportBut 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 formulaList("-",UserToName(User()), "Approved")The effect of that will calculate to John Smith - Approved.Then on the Payroll records, make a similar formulaList("-", [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 checkedEDIT 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.

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?

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

Update all records in a specific report

14 Replies

QuickBaseCoachD
Qrew Captain
8 years ago
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.
ChrisMacFarlane
Qrew Trainee
8 years ago
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.
QuickBaseCoachD
Qrew Captain
8 years ago
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";
ChrisMacFarlane
Qrew Trainee
8 years ago
That worked, thanks so much for your help with this project, it's been great.

Forum Discussion

Update all records in a specific report

14 Replies

Related Content

Nesting API Button Help (Add record and update another)

Duplicate Record Checker

Custom Record Titles

Record Details

Using Grid Edit to Edit Multiple Records at Once