Search Results for 'form.getRecords'

[Sam Moffatt]

Off the top of my head, where:

• players_linked_fieldId is the field ID of the “Link to Form” field in your Classes form.
• players_fieldId is the field ID of the Players field in your Classes form.
• players_linked_player_name_fieldId is the field ID of the players-linkedin field in your Players form.

Three fields: link field, source field and destination field (in destination form).

var players_linked_fieldId = 'fld-1234';
var players_fieldId = 'fld-4321';
var players_linked_player_name_fieldId = 'fld-1337';

for (record in form.getRecords())
{
  for (player in record.getFieldValue(players_fieldId).split(',').map(p => p.trim()))
  {
    let playerRecord = record.addNewRecordToField(players_linked_fieldId);
    playerRecord.setFieldValue(players_linked_player_name_fieldId, player);
  }
}
document.saveAllChanges();

Something like that should work. Outer loop gets all of the records and iterates over them. Inner loop splits the text field on a comma and also trims the string (that’s the map(p => p.trim()) piece). Inside the loop you add a new record to the “Link to Form” field and set the player name on the inside to be the player from the string.

None of this is tested, provided as is and it should work with a few tweaks. You’ll need to replace the field ID’s with your own values but apart from that it should be good enough to test. I’d recommend having Tap Forms duplicate the document and work from it first to make sure there aren’t any weird side effects.

[Bernhard]

@Sam: Sounds good – thanks for the advice!
For the record, here is how it works:

var customerRecordIds = [];

// Callback function of the Prompter call.
function chooseCustomerCallback() {
	createInvoice(customerRecordIds[choosenTitle]);
}

// Do something with the record after the user choose one.
function createInvoice(recordId) {
	var customerRecord = form.getRecordWithId(recordId);	
	...
}

// The entry function of the script
function Erstelle_Rechnung() {	
	var titleFieldId = 'fld-aa817f3bd885458883d3e25802fd4037';
 	var customersForm = document.getFormNamed('Kunden');
 	var customers = customersForm.getRecords();
	var companyTitles = [];
	
	for (var index = 0, count = customers.length; index < count; index++) {
		const title = customers[index].getFieldValue(titleFieldId);
		companyTitles.push(title);
		// Remember the Record ID of this customer record to be used later.
		customerRecordIds[title] = customers[index].getId();
	}

	var choosenTitle;

	let prompter = Prompter.new();
	prompter.addParameter('Kunde', 'choosenTitle', 'popup', companyTitles)
		.show('Message prompt', chooseCustomerCallback);

	return null;
}

Erstelle_Rechnung();

[Bernhard]

Sounds good – thanks for the advice!
For the record, here is how it works:

var customerRecordIds = [];

// Callback function of the Prompter call.
function chooseCustomerCallback() {
	createInvoice(customerRecordIds[choosenTitle]);
}

// Do something with the record after the user choose one.
function createInvoice(recordId) {
	var customerRecord = form.getRecordWithId(recordId);	
	...
}

// The entry function of the script
function Erstelle_Rechnung() {	
	var titleFieldId = 'fld-aa817f3bd885458883d3e25802fd4037';
 	var customersForm = document.getFormNamed('Kunden');
 	var customers = customersForm.getRecords();
	var companyTitles = [];
	
	for (var index = 0, count = customers.length; index < count; index++) {
		const title = customers[index].getFieldValue(titleFieldId);
		companyTitles.push(title);
		// Remember the Record ID of this customer record to be used later.
		customerRecordIds[title] = customers[index].getId();
	}

	var choosenTitle;

	let prompter = Prompter.new();
	prompter.addParameter('Kunde', 'choosenTitle', 'popup', companyTitles)
		.show('Message prompt', chooseCustomerCallback);

	return null;
}

Erstelle_Rechnung();

[Barry Shevlin]

I’m almost embarrassed to ask this given it’s probably such a basic question. However, I could well use the following script given as an example in the TF5 manual:

var records = form.getRecords();
var revenue_total = 0;

for (var index = 0, count = records.length; index < count; index++){
var theRec = records[index];
var movie_revenue = theRec.getFieldValue(‘fld-987c9aac738a4f0fa1d18395902b3fc1’);
if (movie_revenue) {
revenue_total += movie_revenue;
console.log(‘Revenue: ‘ + movie_revenue);
}
}

revenue_total;

My question is this: how do I limit the revenue_total return to 2 decimal places (currency)? I am getting this sort of thing: £63.9300000000001.

Sorry if this time-wasting but I’m sure there’s a quick answer that would save me hours of fruitless futzing. Thanks.

[Brendan]

Hi Bernhard,

I’m not entirely sure how that would work for your situation other than to program Tap Forms specifically to allow a string / record ID key/value mapping. But then how does Tap Forms know to relate your title field to the record ID? Generic key/value mapping is one thing, but when it relates specifically to fetching a record, it’s tricky.

However, in the latest beta update I added a new search API that lets you do a generic text search on your records. Like typing into the main Search field. You can now do form.getRecordsForSearchTerm();. You’ll get back an array of records. If there’s only 1 result, you know you’ve got an exact match. Or you can just pull off the record from the first element of the array to get the first occurrence. And if you pass in double-quotes in your search, then Tap Forms will do an exact match search, but the result will still be an array of records. Just one record in that case.

Thanks,

Brendan

[Larry Stoter]

Hi Brendan,

Thanks – very helpful.

With regard to sorting, I would like to change the records sort order within the script. Or, more specifically, I would like to have a conditional branch within the script which returns different sort orders depending on the result of the conditional test.

I was thinking I could do this with form.getRecords() and then sort the array but there doesn’t seem to be an API function to write the sorted array back to the database. Instead, it looks as though this would need to be done with record.setFieldValues() for every record in the database …?

I guess part of the problem I have is that I see records and fields as more fundamental than forms and that forms are just a way to enter and display fields values for each record? So, I’m used to setting up forms/layouts as the final step in putting a database together, after I’ve got everything else working.

Perhaps the TapForms way to handle this is to set up several different forms, each with a different sort order and then use the scripts to move between forms ?

Happy New Year,

Larry

[Brendan]

Hi Larry,

You can’t directly access the Go To functions in Tap Forms from the Scripting engine.

But you can tell Tap Forms to select a record. However, it’s generally only used once in a script.

But instead of having the UI select different records, you can just get the records directly from your script.

Sorting is controlled by the Forms and Saved Searches. So whatever sort order you’ve specified on your Form or Saved Search is what will be used within the Script engine.

If you want to find out what your previous record is, you need to first get the list of records. Then you’ll need to pick out which record you want.

For example:

function Previous_Record() {
	
	var records;
	var previousRecord = record;

	try {
		if (search != undefined) {
			records = search.getRecords();
		} else {
			records = form.getRecords();
		}
		
		var indexOfCurrent = records.indexOf(record);
		
		if (indexOfCurrent > 0) {
			previousRecord = records[indexOfCurrent - 1];
		}
	
	} catch (e) {
		// no search selected, so just don't do anything
	}
	
	return previousRecord;
}

Previous_Record();

I had to stick in the try/catch there because search generates a ReferenceError when you don’t have a Saved Search selected if you try to reference it.

Anyway, so without having to manipulate the user interface, you can get the previous record using the above technique.

[Sam Moffatt]

Ok, so doing it from a form ended up a little more complicated in the sense that I pull in a few other scripts. I’m going to pull this one apart, full script at the bottom. I refactored this as I wrote this post so it might be a little inconsistent though I’ve tried to rectify it.

I’ve started in my script library leveraging a PARENT_SCRIPT variable whose existence I use to run tests in a standalone mode. To avoid the tests running, we need to define PARENT_SCRIPT at the top. The format I’ve been using has been Form Name::Script Name. This script leverages two other scripts as well: getRecordsFromFormMatchingSearch and getUniqueValuesFromField. I’ll talk about those a little later.

var PARENT_SCRIPT = 'Car Inventory::Select Vehicle Data (Form Based)';

form.runScriptNamed('getRecordsFromFormMatchingSearch');
form.runScriptNamed('getUniqueValuesFromField');

When working with different forms, it’s useful to have a complete list of all of the IDs. I ended up settling on this format that has the form name and then the field name with a suffix (formID or fldID) to make it clear what sort of object I’m dealing with. Since sometimes it’s hard to know what type a field is, that’s included in a comment at the end. This is generated by an updated version of the Form Logger script that generates Javascript variable output.

// Car Inventory
var car_inventory_formID = "frm-08d1086a93ad4cba9e452a54f93dc8bb";
var car_inventory__registration_fldID = "fld-fc1a9affa1f241359cbbaa71638e32f0"; // text
var car_inventory__make_fldID = "fld-4c02791b8dce43b2a1e83cd8d3d43201"; // text
var car_inventory__model_fldID = "fld-fb6f72e380af4cfc83b7c90383e97b80"; // text
var car_inventory__year_fldID = "fld-5a2f376a5e814902a8a42c952f881196"; // number
var car_inventory__vehicle_data_fldID = "fld-42853582b83745b491aa0d3707e4e194"; // from_form
var car_inventory__vehicle_data_watcher_fldID = "fld-72a07265e2f147e192b18df718b0a2e9"; // script

// Vehicle Data
var vehicle_data_formID = "frm-bf801eaaf00249d289e5aa4286df92a8";
var vehicle_data__year_fldID = "fld-7a2dc2f2648e4aac8c5904fe54bb0c34"; // number
var vehicle_data__make_fldID = "fld-fb04e14a4a6040a9aada1f102c6c8bfd"; // text
var vehicle_data__model_fldID = "fld-cf8d446ce3d4487d9380b59b3ab00b8f"; // text
var vehicle_data__car_inventory_fldID = "fld-bfadf961632a417b83a9acd34c4663d1"; // form

This is our callback variable that we hand to the prompter. We put this in global scope to make sure we can get at it in our callback.
var tempVar = undefined;

The promptPopup I’ve used in all three examples with slightly different Prompter parameters. This is again borrowed from something @daniel_leu wrote with some other changes. Essentially we’re creating a prompter instance with a given dialog text, popupName for the popup field and popupElements as the values. It uses a Promise to return a value from the callback:

function promptPopup(text, popupName, popupElements){
	return new Promise(function(resolve, reject) {
		tempVar = undefined;
	  	let prompter = Prompter	.new();
	  	prompter.addParameter(popupName, 'tempVar', 'popup', popupElements)

  		prompter.cancelButtonTitle = 'Cancel';
		prompter.continueButtonTitle = 'Continue';
		prompter.show(text, ((status) => {
			if (status == true && tempVar){
				resolve(tempVar);
			} else {
				reject(text + " cancelled");
			}		
		} ));
	});
}

Now onto the meat. There are three prompts, the first is the make. I leverage getUniqueValuesFromField here to extract out the unique Makes from the Vehicle Data form. getUniqueValuesFromField is a helper function to make this look a little more elegant:

async function start(){
	try {
		let make = await promptPopup("What is the make?", "Make:", 
			getUniqueValuesFromField(
				document.getFormNamed('Vehicle Data').getRecords(), 
				vehicle_data__make_fldID
			)
		);
			
		console.log(make);

The next step is to display the models. Here I use a method called getRecordsFromFormMatchingSearch which is something I developed elsewhere to have a sort of key/value AND search for finding records. In this case I’m looking for all records that match that particular make. It again uses getUniqueValuesFromField to extract out the unique models for that make:

		let model = await promptPopup("What is the model?", "Model:", 
			getUniqueValuesFromField(
				getRecordsFromFormMatchingSearch(vehicle_data_formID, 
					[
						{'key': vehicle_data__make_fldID, 'value': make}
					]
				), vehicle_data__model_fldID
			)
		);
				
		console.log(model);
		

Lastly we need to find matching years. This one is similar to the last but adds model to the search parameters so that we find only years with matching makes and models.

		let year = await promptPopup("What is the year?", "Year:", getUniqueValuesFromField(
				getRecordsFromFormMatchingSearch(vehicle_data_formID, 
					[
						{'key': vehicle_data__make_fldID, 'value': make}, 
						{'key': vehicle_data__model_fldID, 'value': model }
					]
				), vehicle_data__year_fldID
			)
		);
		console.log(year);

Lastly we need to catch the error and log it plus we call the function to get the ball rolling:

	} catch (error) {
		console.log("Error: " + error);
	}
}

start();

The getUniqueValuesFromField method is a single line but it does a lot:

function getUniqueValuesFromField(recordset, fieldId)
{
	return Array.from(new Set(recordset.map(function (item) { return item.getFieldValue(fieldId); })));
}

We’ll unpack it backwards:

– the inner function basically is doing getFieldValue on each record in recordset.
– map executes a function over each element of the array recordset.
– recordset is the set of records from either form.getRecords() or some other array of records.
– new Set is used to create a set of unique values.
– Array.from converts what we want to return into an array.

This makes things a little neater elsewhere and is a slight abuse of Javascript.

Here is what getRecordsFromFormMatchingSearch looks like:

/**
 * Get a record from a form matching a given key/value criteria.
 *
 * recordset        The form name or form ID to get the records from.
 * criterion        An array of key/value pairs of criteria to match.
 *
 * return           Array of matched records.
 */
function getRecordsFromRecordsetMatchingCriterion(recordset, criterion)
{
	// Check if our basic parameters are set.
	if (!recordset || !criterion)
	{
		throw new Error(`Missing required parameters ${recordset}/${criterion}`);
	}
	
	//console.log(JSON.stringify(criterion));
	
	profiler.start(`getRecordsFromRecordsetMatchingCriterion: ${recordset}/${JSON.stringify(criterion)}: start`);
	
	let matchingRecords = new Set();
	let candidateRecord = null;
	
	for (candidateRecord of recordset)
	{
		let match = true;
		let criteria = null;
		for (criteria of criterion)
		{
			//console.log(JSON.stringify(criteria));
			let candidateValue = candidateRecord.getFieldValue(criteria['key']);
			//console.log(`Test: "${candidateValue}" vs "${criteria['value']}"`);
			if (candidateValue != criteria['value'])
			{
				match = false;
				break;
			}
			//console.log('Matched!');
		}
		
		if (match)
		{
			matchingRecords.add(candidateRecord);
		}
	}
	profiler.end();
	return Array.from(matchingRecords);
}

It’s a little more complicated, but the meat is in the middle. We start by iterating over the recordset then setting the match and criteria variables. I use let here to make sure they don’t escape into global scope inadvertently.

	for (candidateRecord of recordset)
	{
		let match = true;
		let criteria = null;

The next step is to iterate through the criterion to see if the criteria matches. These are key/value pairs where the key is the field ID and value is what we want to match against. If the candidateValue doesn’t match, we flip the match value and escape from the inner loop. I’ve left in the debugging statements that I had put in as well so you can see how that works.

		for (criteria of criterion)
		{
			//console.log(JSON.stringify(criteria));
			let candidateValue = candidateRecord.getFieldValue(criteria['key']);
			//console.log(`Test: "${candidateValue}" vs "${criteria['value']}"`);
			if (candidateValue != criteria['value'])
			{
				match = false;
				break;
			}
			//console.log('Matched!');
		}

The last step here is check if there is a match and add it to the matchingRecords.

		if (match)
		{
			matchingRecords.add(candidateRecord);
		}
	}

There is one other piece of code which takes the unique set and turns it back into an array to be returned:

	return Array.from(matchingRecords);

Here is the full script as one block:

var PARENT_SCRIPT = 'Car Inventory::Select Vehicle Data (Form Based)';

form.runScriptNamed('getRecordsFromRecordsetMatchingCriterion');
form.runScriptNamed('getUniqueValuesFromField');

// Car Inventory
var car_inventory_formID = "frm-08d1086a93ad4cba9e452a54f93dc8bb";
var car_inventory__registration_fldID = "fld-fc1a9affa1f241359cbbaa71638e32f0"; // text
var car_inventory__make_fldID = "fld-4c02791b8dce43b2a1e83cd8d3d43201"; // text
var car_inventory__model_fldID = "fld-fb6f72e380af4cfc83b7c90383e97b80"; // text
var car_inventory__year_fldID = "fld-5a2f376a5e814902a8a42c952f881196"; // number
var car_inventory__vehicle_data_fldID = "fld-42853582b83745b491aa0d3707e4e194"; // from_form
var car_inventory__vehicle_data_watcher_fldID = "fld-72a07265e2f147e192b18df718b0a2e9"; // script

// Vehicle Data
var vehicle_data_formID = "frm-bf801eaaf00249d289e5aa4286df92a8";
var vehicle_data__year_fldID = "fld-7a2dc2f2648e4aac8c5904fe54bb0c34"; // number
var vehicle_data__make_fldID = "fld-fb04e14a4a6040a9aada1f102c6c8bfd"; // text
var vehicle_data__model_fldID = "fld-cf8d446ce3d4487d9380b59b3ab00b8f"; // text
var vehicle_data__car_inventory_fldID = "fld-bfadf961632a417b83a9acd34c4663d1"; // form

var tempVar = undefined;

function promptPopup(text, popupName, popupElements){
	return new Promise(function(resolve, reject) {
		tempVar = undefined;
	  	let prompter = Prompter	.new();
	  	prompter.addParameter(popupName, 'tempVar', 'popup', popupElements)

  		prompter.cancelButtonTitle = 'Cancel';
		prompter.continueButtonTitle = 'Continue';
		prompter.show(text, ((status) => {
			if (status == true && tempVar){
				resolve(tempVar);
			} else {
				reject(text + " cancelled");
			}		
		} ));
	});
}

async function start(){
	try {
		let vehicleDetails = document.getFormNamed('Vehicle Data').getRecords();
		let make = await promptPopup("What is the make?", "Make:", 
			getUniqueValuesFromField(
				vehicleDetails, 
				vehicle_data__make_fldID
			)
		);
			
		console.log(make);
		
		let model = await promptPopup("What is the model?", "Model:", 
			getUniqueValuesFromField(
				getRecordsFromRecordsetMatchingCriterion(vehicleDetails,
					[
						{'key': vehicle_data__make_fldID, 'value': make}
					]
				), vehicle_data__model_fldID
			)
		);
				
		console.log(model);
		
		let year = await promptPopup("What is the year?", "Year:", getUniqueValuesFromField(
				getRecordsFromRecordsetMatchingCriterion(vehicleDetails,
					[
						{'key': vehicle_data__make_fldID, 'value': make}, 
						{'key': vehicle_data__model_fldID, 'value': model }
					]
				), vehicle_data__year_fldID
			)
		);
		console.log(year);
	} catch (error) {
		console.log("Error: " + error);
	}
}

start();

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

Copy of the script:

var clipboard = Utils.copyTextFromClipboard();

if (clipboard)
{
	clipboard = clipboard.split("\n")[0];
}

var tracking_number_id = 'fld-c487390743c947969cbe661cff596855';
var received_date_id = 'fld-e3e3539ee04f4cc7971c7098c572104d';
var confirmed_id = 'fld-2adb9ba8cdd048bbbb614d46b415ada5';
var alternate_tracking_numbers_id = 'fld-cf8718051bea4cc2aba0069ae76f32b7';
var alternate_tracking_number_id = 'fld-7342203d8f36415191bf8419fb6f70dc';

var callbackFunction = function(continued) {
	if (continued)
	{
		let newRecord = form.addNewRecord();
		newRecord.setFieldValue(tracking_number_id, clipboard);
		newRecord.setFieldValue(received_date_id, new Date());
		newRecord.setFieldValue(confirmed_id, true);
		document.saveAllChanges();
	}
};

function findRecord()
{
	var targetRecord = null;
	MainLoop:
	for(candidateRecord of form.getRecords())
	{
		if (clipboard == candidateRecord.getFieldValue(tracking_number_id))
		{
			targetRecord = candidateRecord;
			break MainLoop;
		}
		
		
		for (alternateRecord of candidateRecord.getFieldValue(alternate_tracking_numbers_id))
		{
			if (clipboard == alternateRecord.getFieldValue(alternate_tracking_number_id))
			{
				targetRecord = candidateRecord;
				break MainLoop;
			}
		}
	}
	
	if (targetRecord)
	{
		targetRecord.setFieldValue(received_date_id, new Date());
		targetRecord.setFieldValue(confirmed_id, true);
		document.saveAllChanges();
form.selectRecord(targetRecord);
		return "Updated existing record for " + clipboard;
	}
	else
	{
		let prompter = Prompter.new();
		prompter.cancelButtonTitle = 'No thanks';
		prompter.continueButtonTitle = 'Create record';
		prompter.show("Unable to find matching record for " + clipboard, callbackFunction);
		return "Unable to find matching record for " + clipboard;
	}
}

findRecord();

[Sam Moffatt]

It’ll require a bit of customisation, I use this with shipping records to mark them delivered and confirmed (unconfirmed shipment delivery would be sync from automated systems). This is the one that grabs from the clipboard, looks for a candidate matching record and then updates it’s state:

var clipboard = Utils.copyTextFromClipboard();

if (clipboard)
{
	clipboard = clipboard.split("\n")[0];
}

var tracking_number_id = 'fld-c487390743c947969cbe661cff596855';
var received_date_id = 'fld-e3e3539ee04f4cc7971c7098c572104d';
var confirmed_id = 'fld-2adb9ba8cdd048bbbb614d46b415ada5';
var alternate_tracking_numbers_id = 'fld-cf8718051bea4cc2aba0069ae76f32b7';
var alternate_tracking_number_id = 'fld-7342203d8f36415191bf8419fb6f70dc';

function findRecord()
{
	var targetRecord = null;
	MainLoop:
	for(candidateRecord of form.getRecords())
	{
		if (clipboard == candidateRecord.getFieldValue(tracking_number_id))
		{
			targetRecord = candidateRecord;
			break MainLoop;
		}
		
		
		for (alternateRecord of candidateRecord.getFieldValue(alternate_tracking_numbers_id))
		{
			if (clipboard == alternateRecord.getFieldValue(alternate_tracking_number_id))
			{
				targetRecord = candidateRecord;
				break MainLoop;
			}
		}
	}
	
	if (targetRecord)
	{
		targetRecord.setFieldValue(received_date_id, new Date());
		targetRecord.setFieldValue(confirmed_id, true);
		document.saveAllChanges();
		form.selectRecord(targetRecord);
		return "Updated existing record for " + clipboard;
	}
	else
	{
		return "Unable to find matching record for " + clipboard;
	}
}

findRecord();

This is a similar form script that uses the Prompter to ask for input and then creates a record:

// Order: Shipment Field ID
var shipments_id = 'fld-db2fcdb4d79c466ea09671c47d2ae645';

// Order: Ship Date 
var ship_date_id = 'fld-6ab700ccc11d418fbd27d8899d00c7a9';
var ship_date = record.getFieldValue(ship_date_id);

// Shipments: Record Field ID's
var tracking_number_id = 'fld-c487390743c947969cbe661cff596855';
var carrier_id = 'fld-0950c430cb0c41f79c51d43a544b366b';
var shipping_date_id = 'fld-1aa32f17e059424fb4e24bf894b34fdf';

var callbackFunction = function() {
	if (tracking_number && carrier)
	{
		var data = {
			[tracking_number_id]: tracking_number,
			[carrier_id]: carrier,
		};
		
		if (ship_date)
		{
			data[shipping_date_id] = ship_date;
		}
		else
		{
			data[shipping_date_id] = new Date();
		}
		
		console.log(JSON.stringify(data));
		
		var shipmentRecord = record.addNewRecordToField(shipments_id);
		shipmentRecord.setFieldValues(data);
		document.saveAllChanges();		
	}
};

let prompter = Prompter.new();
prompter.addParameter('Tracking Number', 'tracking_number', 'text')
	.addParameter('Carrier', 'carrier', 'picklist', 'Carrier - Shipments')
	.show('Message prompt', callbackFunction);

The Prompter stuff uses a callback mechanism which means it’s not blocking so it takes a little bit of work to turn that into a continuous loop but it would be possible to do.

Creating a checkin/checkout flow is certainly possible though, not the most elegant but definitely possible.

[Sam Moffatt]

Use the script editor to grab the two fields ID’s and their values. For your old field, if it has a set of values then you need to translate them to the new value. I’d use a switch statement here on your old field to map it over, something like:

let oldValue = record.getFieldValue('fld-1234');
let newValue = record.getFieldValue('fld-4321');

switch(oldValue)
{
  case 'Swimming':
     record.setFieldValue(newValue + ', swi1', 'fld-4321');
     break;
}

Then for your new field, if you have a multipick list then get the current value and then use JavaScript to append “, <new value>”. It’ll look a little weird ofr empty fields but shouldn’t hurt anything. If it is a single value pick list, then you can just reset the field value.

If you create a form script, you can use form.getRecords to get all of the records and then process them one at a time. A pattern I use is to create a function script that accepts a record field and then label it as currentRecord to avoid getting confused in the scope. Then I can test using a single record or later on put it in a loop.

[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]

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]

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]

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