Search Results for 'form.getRecords'

[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

[Daniel Leu]

Yeah, the Prompter() is asynchronous…

Maybe there is a better way, but I would first create an array where you identify the multiple stocks. Then you can process those with the prompter loop.
Here is a small proof of concept. I just us an array that contains all records of my form. In your case, this would be the result of your filtered stock. Then I call my processing function. And as callback, I use the same processing function. So now the prompter loops over all entries of my list.

var recs = form.getRecords();
var loop_count = recs.length;

var xyzFunc = function() {
	if (loop_count == 0) {
		return "done";
	} else {
		loop_count -= 1;
	}

	let prompter = Prompter.new();
	prompter.show('Message prompt. Loop Count ' + loop_count, xyzFunc);
};

xyzFunc();

Maybe this gives you a hint how you can approach your problem. Personally, I would love if the Prompter() is blocking, but I got to use what we have available.

[Sam Moffatt]

I had a situation where I wanted to automatically download a set of files from the web and store them as Tap Forms document. In my case I have two forms: one parent form that stores the URL I want to grab data from as a base and second form that I want put new records with more details keyed for me by source URL.

It takes a few parameters to operate (the last three are optional):

• formName: The name of the source form for the records; can also be a form ID (required).
• keyFieldId: The field ID of the key field in the record (required).
• keyValue: The value to match as the key (required).
• createIfMissing: Control if a record is created if none is found (default: true).
• earlyTermination: Control if function terminates as soon as a match is found (default: true).
• alwaysScanOnMiss: Control if we should assume concurrent access (default: false)

I use this with my Script Manager idiom, so I have something like this:

document.getFormNamed('Script Manager').runScriptNamed('getLinkedRecord');
let currentRecord = getRecordFromFormWithKey('Location Tracker', source_id, entry.href, true, false);

The early termination option is interesting and how I’d advise using it is dependent upon your use case. If you set early termination, when the script is iterating through all of the records it will return the first match it finds. If you only expect to call this function once, maybe twice, then this will work fine for you because depending on your ordering you will avoid a full traversal of your recordset.

If you are running this in a loop or some other construct where you expect to call the function multiple times then disabling early termination ensures that everything is indexed on the first pass. If you expect to have many cache hits, then this will improve performance significantly as subsequent calls will immediately return based on a direct lookup. Record creation is added as a method here so that newly created records can be immediately indexed in the system avoiding future cache misses.

The worst case for this method is a cache miss. After the first scan of the records, it assumes no other concurrent access. If you don’t want this behaviour, there is another flag to always scan if a miss isn’t found and the form has already been scanned.

Here’s the script:

// ========== getRecordFromFormWithKey Start ========== //
// NAME: Get Record From From With Key
// VERSION: 1.0

/**
 * Get a record from a form with a key field, optionally creating it if missing.
 * Note: assumes your key field is unique.
 *
 * formName           The name of the source form for the records (required).
 * keyFieldId         The field ID of the key field in the record (required).
 * keyValue           The value to match as the key (required).
 * createIfMissing    Control if a record is created if none is found (default: true).
 * earlyTermination   Control if function terminates as soon as a match is found (default: true).
 * alwaysScanOnMiss   Control if we should assume concurrent access (default: false)
 */
function getRecordFromFormWithKey(formName, keyFieldId, keyValue, createIfMissing = true, earlyTermination = true, alwaysScanOnMiss = false)
{
	// Check if our basic parameters are set.
	if (!formName || !keyFieldId || !keyValue)
	{
		throw new Error("Missing required parameters");
	}
	
	// Determine the target form (check if we were given an ID or else assume a name)
	let targetForm = undefined;
	if (formName.match(/frm-/))
	{
		targetForm = document.getFormWithId(formName);
	}
	else
	{
		targetForm = document.getFormNamed(formName);
	}

	// Check if our global variable has been setup.
	if (!indexedRecordIndex)
	{
		var indexedRecordIndex = {};	
	}
	
	// Flag for if we've indexed this form already.
	if (!indexedRecordState)
	{
		var indexedRecordState = {};
	}

	// Create a key for this form-field combination.
	// Form+Field is the key.
	let indexedRecordKey = targetForm.getId() + "_" + keyFieldId;

	// Check to see if this particular link field has been setup.
	if (!indexedRecordIndex[indexedRecordKey])
	{
		indexedRecordIndex[indexedRecordKey] = {};
	}

	
	// Short circuit if we have an exact match.
	if (indexedRecordIndex[indexedRecordKey][keyValue])
	{
		return indexedRecordIndex[indexedRecordKey][keyValue];
	}
	
	// No immediate match, check to see if we should scan everything.
	// alwaysScanOnMiss forces this code path each execution.
	// The check to indexedRecordState is if this has been indexed.
	if (alwaysScanOnMiss || !indexedRecordState[indexedRecordKey])
	{	
		// Brute force search :(
		let records = targetForm.getRecords();

		// Iterate through all of the records and look for a match.
		for (currentRecord of records)
		{
			// Set up a reverse link for this value.
			let recordKeyValue = currentRecord.getFieldValue(keyFieldId);
			indexedRecordIndex[indexedRecordKey][recordKeyValue] = currentRecord;

			// If this is a match and early termination is setup, return immediately.
			if (earlyTermination && recordKeyValue == keyValue)
			{
				return currentRecord;
			}
		}
		
		// Flag this record-field as being indexed.
		indexedRecordState[indexedRecordKey] = true;
	}

	// Check to see if we got a match here and return if the key exists.
	if (indexedRecordIndex[indexedRecordKey][keyValue])
	{
		return indexedRecordIndex[indexedRecordKey][keyValue];
	}
	else if (createIfMissing)
	{
		// If createIfMissing is set, create a new record.
		// Note: it's expected the caller will call document.saveAllChanges();
		let newRecord = targetForm.addNewRecord();
		// Set the key value to our search value.
		newRecord.setFieldValue(keyFieldId, keyValue);
		// Link this up to save us another lookup in future.
		indexedRecordIndex[indexedRecordKey][keyValue] = newRecord;
		// And now we return the new record. 
		return newRecord;
	}
	
	// If we didn't find anything, return undefined.
	return undefined;
}
// ========== getRecordFromFormWithKey End ========== //

[David Gold]

I’ve edited the above but am running into issues with the final copy to clipboard. Have I made an error I’m missing somewhere:

// get form of based on form name
var search = Utils.copyTextFromClipboard();
var myForm = document.getFormNamed('Travel Database');
// get all records
var records = myForm.getRecords();
// loop over all records
var rec;
for (rec of records) {
   // get value and compare
   if (rec.getFieldValue('fld-cf720b6ab4314f0bb5f47bc9bd61f0a9') == search) {
      // if match, do something
	Utils.copyTextToClipboard(rec.getFieldValue('fld-cf720b6ab4314f0bb5f47bc9bd61f0a9'));
      break;
   }
}

[Daniel Leu]

Maybe something like this:

// get form of based on form name
var myForm = document.getFormNamed('my form');
// get all records
var records = myForm.getRecords();
// loop over all records
var rec;
for (rec of records) {
   // get value and compare
   if (rec.getFieldValue(field_id) == 'expected value') {
      // if match, do something
      console.log("success");
      break;
   }
}

Please note that I didn’t run this code…. It might make sense to put this all in a function os it can be easily reused.

[Marcus]

Ya, got it.
I already created a bunch of helpful scripts,
I only was stuck hanging to automate with a script field rather than execute it manually.

One last question:
Is there a possibility to get filtered recordsets only ?

This returns ALL records:

var records=form.getRecords();