Script to calculate percentage of ticked rows

Tap Forms – Organizer Database App for Mac, iPhone, and iPad Forums Script Talk Script to calculate percentage of ticked rows

This topic contains 7 replies, has 2 voices, and was last updated by  Sam Moffatt 4 hours, 43 minutes ago.

  • Author
    Posts
  • November 8, 2019 at 3:59 PM #37926

    Roy McKee
    Participant

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

    November 8, 2019 at 7:10 PM #37933

    Sam Moffatt
    Participant

    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
    • This reply was modified 2 days, 18 hours ago by  Sam Moffatt.
    November 9, 2019 at 7:35 AM #37955

    Roy McKee
    Participant

    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?

    November 9, 2019 at 11:05 AM #37961

    Sam Moffatt
    Participant

    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.
    November 10, 2019 at 7:23 AM #37992

    Roy McKee
    Participant

    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;

    November 10, 2019 at 12:18 PM #37995

    Sam Moffatt
    Participant

    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?

    November 11, 2019 at 3:46 AM #38003

    Roy McKee
    Participant

    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.

    November 11, 2019 at 9:13 AM #38004

    Sam Moffatt
    Participant

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

You must be logged in to reply to this topic.