Search Results for 'form.getRecords'

[Sam Moffatt]

I’ll walk through this a little as this is my bulk scan script. It’s probably a bit much but it has a bunch of piece that might help.

document.getFormNamed('Script Manager').runScriptNamed('Prompter Functions');

This is pulling in my Prompter Functions via the Script Manager method. Basically Script Manager is a Tap Forms form named ‘Script Manager’ and then it uses ‘Form Scripts’.

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 carrier_id = 'fld-0950c430cb0c41f79c51d43a544b366b';
var zip_code_id = 'fld-4f73faa8937446a0a3b24e6dd4624d6b';
var shipper_id = 'fld-1789ae45fd1f4f588294eff0c2fb6045';

These are all of the field ID’s I care about, you’ll see them spliced through. Obviously your fields will be different.

function findRecord(targetTrackingNumber)
{
	var targetRecord = null;
	MainLoop:
	for(candidateRecord of form.getRecords())
	{
		if (targetTrackingNumber == candidateRecord.getFieldValue(tracking_number_id))
		{
			targetRecord = candidateRecord;
			break MainLoop;
		}
		
		
		for (alternateRecord of candidateRecord.getFieldValue(alternate_tracking_numbers_id))
		{
			if (targetTrackingNumber == alternateRecord.getFieldValue(alternate_tracking_number_id))
			{
				targetRecord = candidateRecord;
				break MainLoop;
			}
		}
	}
	
	return targetRecord;
}

This is a quick method to basically brute force scan all of the records and search for them. I mentioned earlier that Brendan is adding a function to make this a little less relevant however what it’s doing is getting a list of all of the records, checking if the tracking number matches (in your case it’ll be your box barcode or item barcode) and it also checks the values of a table field that is in my shipments form. Because there are two levels of loops in this, I use a trick to make sure I break out of them easily. Shipping is fun where a package might have more than one tracking number, hence the crazy setup.

async function start(){
	try {
		while (1)
		{

This is the start of the meat and it has three things to be interested in. The async keyword is required to get my prompter functions to work properly and enable you to do blocking calls without having to manage the callbacks all yourself. The try is a fallback for an exceptions that might escape and the while (1) is our infinite loop.

			let zipcode = undefined;
			let carrier = '';
			let barcode = '';
			let scanBarcode = await promptText("Scan Shipment", "Barcode:");

This sets up some variables, in my case I’m tracking zipcode for the package (USPS embed this into their tracking number), a carrier that I can automatically infer (USPS and UPS) and then obviously the core barcode. This uses the promptText function which is in that Prompter Functions I mentioned earlier. It has await prefixed to make it block until we get a response back.

			if (!scanBarcode)
			{
				break;
			}

This is pretty straight forward, if we don’t get anything back from the barcode then we abort the loop. This is our exit criteria.

			let matches = scanBarcode.match(/420(9[0-9]{4})(.*)/);
			if (matches)
			{
				carrier = 'USPS';
				zipcode = matches[1];
				barcode = matches[2];
			}
			else
			{
				barcode = scanBarcode;
			}

This isn’t relevant to you as much but it basically looks for a USPS barcode and extracts the useful information out of it. It resets the barcode to be what the label and tracking number actually is versus the full details that are encoded into the barcode. I’m in California so my zipcode is prefixed with a 9 which is why it looks the way it does.

			matches = scanBarcode.match(/^1Z/);
			if (matches)
			{
				carrier = 'UPS';
			}

This also isn’t as relevant but it looks for a UPS style barcode and sets it automatically. Depending on how your barcode generation is done, this might be something you can apply where you have different barcode prefixes for stuff (or not).

			console.log(barcode);
			
			let targetRecord = findRecord(barcode);

I logged the barcode because I wanted to see what it was in the console. This is useful for understanding when something weird happens. This is then fed into that findRecord method before to find a record that matches, or not.

			if (targetRecord)
			{
				// Flip confirmed flag but otherwise leave it alone.
				targetRecord.setFieldValue(confirmed_id, true);
				document.saveAllChanges();
				console.log("Updated existing record for " + barcode);
			}

For me I’m looking to confirm or create a record that I’m scanning. In this case if for some reason the shipping number already exists and I found a matching record, I just toggle a flag saying that I know it exists and move on.

			else
			{
				let payload = {
					[tracking_number_id]: barcode,
					[confirmed_id]: true
				};

Ok, the else case means this tracking number doesn’t exist already so we need to create it. I start to create a new record here. This syntax with the square brackets is to get the value of tracking_number_id instead of using tracking_number_id as the key.

				if (carrier)
				{
					payload[carrier_id] = carrier;
					payload[zip_code_id] = zipcode;
				}

If there is a carrier set then this gets set up as well including the zipcode (USPS).

				let shipper = await promptText("Enter Shipper Name", "Shipper: ");
				console.log(shipper);
				
				if (shipper)
				{
					payload[shipper_id] = shipper;
				}

I ask for the shipper name in case that’s obvious, again with the promptText method. That’s useful for knowing where something is from if I want to add it in.

				console.log(JSON.stringify(payload));

				let newRecord = form.addNewRecord();
				newRecord.setFieldValues(payload);
				document.saveAllChanges();

I log out what I’m about to create to see what it is during debugging. I then create a new record, use setFieldValues to set up the values and then save the changes. Too easy!

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

start();

This is closing everything out and then triggering the script to begin with. The catch is the follow up to the try and is a fail safe to log the message and go from there. It’s closing out the loop so assuming you enter in a valid barcode, it’ll keep looping until it’s done.

I thought I had another script that handled a little closer to what you were doing but I can’t find where I put it.

Here’s the script in full:

document.getFormNamed('Script Manager').runScriptNamed('Prompter Functions');

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 carrier_id = 'fld-0950c430cb0c41f79c51d43a544b366b';
var zip_code_id = 'fld-4f73faa8937446a0a3b24e6dd4624d6b';
var shipper_id = 'fld-1789ae45fd1f4f588294eff0c2fb6045';

function findRecord(targetTrackingNumber)
{
	var targetRecord = null;
	MainLoop:
	for(candidateRecord of form.getRecords())
	{
		if (targetTrackingNumber == candidateRecord.getFieldValue(tracking_number_id))
		{
			targetRecord = candidateRecord;
			break MainLoop;
		}
		
		
		for (alternateRecord of candidateRecord.getFieldValue(alternate_tracking_numbers_id))
		{
			if (targetTrackingNumber == alternateRecord.getFieldValue(alternate_tracking_number_id))
			{
				targetRecord = candidateRecord;
				break MainLoop;
			}
		}
	}
	
	return targetRecord;
}

async function start(){
	try {
		while (1)
		{
			let zipcode = undefined;
			let carrier = '';
			let barcode = '';
			let scanBarcode = await promptText("Scan Shipment", "Barcode:");
			
			if (!scanBarcode)
			{
				break;
			}

			let matches = scanBarcode.match(/420(9[0-9]{4})(.*)/);
			if (matches)
			{
				carrier = 'USPS';
				zipcode = matches[1];
				barcode = matches[2];
			}
			else
			{
				barcode = scanBarcode;
			}
			
			matches = scanBarcode.match(/^1Z/);
			if (matches)
			{
				carrier = 'UPS';
			}
			console.log(barcode);
			
			let targetRecord = findRecord(barcode);
				
			if (targetRecord)
			{
				// Flip confirmed flag but otherwise leave it alone.
				targetRecord.setFieldValue(confirmed_id, true);
				document.saveAllChanges();
				console.log("Updated existing record for " + barcode);
			}
			else
			{
				let payload = {
					[tracking_number_id]: barcode,
					[confirmed_id]: true
				};
				
				if (carrier)
				{
					payload[carrier_id] = carrier;
					payload[zip_code_id] = zipcode;
				}

				let shipper = await promptText("Enter Shipper Name", "Shipper: ");
				console.log(shipper);
				
				if (shipper)
				{
					payload[shipper_id] = shipper;
				}
				
				console.log(JSON.stringify(payload));

				let newRecord = form.addNewRecord();
				newRecord.setFieldValues(payload);
				document.saveAllChanges();
			}
		}
	} catch (error) {
		console.log("Error: " + error);
	}
}

start();

[Sam Moffatt]

I have recently been working on something like that to scan a bunch of items into boxes and similar. There are a couple of ways of handling it.

I’m going to rephrase some of your questions:

Can I pull up a box by scanning it’s barcode?

This one is relatively straight forward application of the search functionality, that’s easy out of the box regardless of what platform you’re on. On the desktop you’ll need a barcode scanner to make that work but on iOS the built in barcode scanning functionality will make that work for you there.

Can I scan barcodes of items as I place them into the box?

The way I did this in January when I was doing a lot of box packing for moving was to create a second form called “Box Items” that is linked from my main “Box” form and then created child records. I was taking pictures of the item and adding some extra metadata in this case so I just used the built in Tap Forms barcode scanner to scan items and used voice to text to quick enter titles (worked enough times to save me having to type).

Now if you invest in a barcode scanner, you can take it a little bit further and create a prompter loop to scan barcodes and automatically create new child records. You can also pivot and if you’re confident an item will only be in a single box, you can make it’s box barcode a property of the item. I have a prompter loop I use for bulk scanning shipments, I’ll post that on the Script Talk forum later.

Can I checkout of a box?

If you go with the Link to Form and enable show inverse relationship (or what ever that checkbox is called), then when you scan in your item in Tap Forms using the search feature it will show you the record and the “Link From Form” will show you the details of the box that it thinks it should be in. In Tap Forms on the desktop you can select this and change it to search for a new box which you should be able to do by barcode for your checkout box or just have a box named “checkout”. By changing the value of the Link from Form field parent will register it to a new box. The iOS version has a similar interaction here but I feel that it isn’t quite as smooth as the desktop more due to the limitations of iOS rather than Tap Forms (the desktop shows a popover which makes it much easier to interact with).

If you go for the field based approach, you just need to update the field to where ever you put the item.

In both cases you can script it to handle it easier. Scripting the Link to Form/Link from Form is a little more difficult because in the current production release of Tap Forms there is no search by text via the script interface. Brendan mentioned that he’s adding a form.getRecordsForSearchTerm() to the next beta which would make that easier because the JavaScript API for the link fields works with records. Until then, it’s a little easier to update a simple text field based on a search.

I have a dedicated barcode scanner for the desktop, I bought a NETUM Bluetooth scanner that has both a USB cable for input and can pair via Bluetooth. I’m not sure I’d recommend it, it works fine in Bluetooth mode but when I plug in the USB cable it mangles the numbers some how (it’s like it’s pressing on the keypad numbers upside down). On the Mac I use Karabiner Elements whose mere existence seemed to make the scanner behave properly.

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