Script to calculate percentage of ticked rows

[Roy McKee]

Does anyone have a sample script to calculate the number of ticked check box rows as a percentage of the total number of rows?

[Sam Moffatt]

A form script like this should do it (replace check_mark_id with your field ID):

var check_mark_id = 'fld-acaa569a30294a02a26e6fb116781718';
var records = form.getRecords();
var checked = 0;
for (record of records)
{
	checked += record.getFieldValue(check_mark_id);
}

checked / records.length * 100

[Roy McKee]

Hi Sam
Thanks for the script. I have created it as you say, replacing the check-mark-id and field ref, thus:

function Script_Google_No_1_S() {

// My Code
var Google_No_1_id = ‘fld-4cbc05636709431a8305cfb7739a9bc5’;
var records = form.getRecords();
var checked = 0;
for (record of records)
{
checked += record.getFieldValue(Google_No_1_id);
}

checked / records.length * 100

Script_Google_No_1_S();

but nothing happens. Have I done something wrong?

[Sam Moffatt]

If you’re using a form script, then you’ll want to use something like Utils.alertWithMessage to display it if you aren’t sending it somewhere else, e.g.:

Utils.alertWithMessage('Calculation Result', checked / records.length * 100);

Or to put that as the full script using your field ID:

var check_mark_id = 'fld-4cbc05636709431a8305cfb7739a9bc5';
var records = form.getRecords();
var checked = 0;
for (record of records)
{
	checked += record.getFieldValue(check_mark_id);
}

let percentage = checked / records.length * 100;
Utils.alertWithMessage('Calculation Result', percentage);
percentage;

You can paste that into the script editor without having to define a function and it will work. The default Tap Forms script sample includes a function because otherwise some of the flow control keywords like return don’t behave normally.

Also in the script editor when you run it, it should give you the output on the left or any errors n on the right.

Attachments:

You must be logged in to view attached files.

[Roy McKee]

Thanks Sam, the script now works although it provides an impossible result. There are currently 44 records (there will be hundreds when I have finished the DB). I am trying to work out the number of Google No 1’s out of the 44 records. There are 32 ticked, which is more like 75%. I see from your result that there were 33%, which was probably accurate at the time you did it. I can only assume something has changed i the script but cant see anything wrong.

This is how I have copied it:

var Google_No_1 = ‘fld-4cbc05636709431a8305cfb7739a9bc5’;
var records = form.getRecords();
var checked = 0;
for (record of records)
{
checked += record.getFieldValue(Google_No_1);
}

let percentage = checked / records.length * 100;
Utils.alertWithMessage(‘Calculation Result’, percentage);
percentage;

[Sam Moffatt]

When you run the form script, what value does it show for you? The screenshot was from my form so is obviously going to be different for you and your form. Could you attach a screenshot of what you see? What makes it impossible?

[Roy McKee]

My mistake, Sam, I had inadvertently entered the wrong field ID. Sorry about that. I confirm the script is working fine. Thank you very much for your help, much appreciated.

[Sam Moffatt]

Great news! These things happen but glad we got there in the end :)

[Author]

Posts