Search Results for 'script'

[Larry Stoter]

Hi,

I’m afraid I’m new to Tap Forms, although I am familiar with several other database applications – mainly ProVue’s Panorama.

I’m also a beginner at JavaScript. I’ve done quite a bit of coding in the past with languages like Basic and Fortran, and in Excel. I have used JavaScript at a very basic level in web page designs but that’s it so sorry if some of my questions are very basic.

How do I move the focus of a script between records? That is, what is the scripting equivalent of the menu commands Goto … Next Record/Previous Record/First Record/Last Record? I want to get a field value from one record and add/subtract to a different field in a previous record. I’m aware there is an issue regarding how the records might be sorted, which leads me to my second question …

How do I script sorting of records?

Thanks,

Larry

[Sam Moffatt]

CouchDB can host everything you need already, it has a built in web server with authentication capabilities and can sync with TapForms. Look up CouchApps as a process, it’s much like what Lotus Notes used to provide (all of these sorts of document stores feel like Notes). In theory you could leverage the CBL replication endpoint in TapForms but that’s read-only.

The challenge is more finding someone with the time and skill to re-implement all of TapForms in Javascript in a cost effective manner.

[Sam Moffatt]

Simplest example slightly modified from the prompter example on the JavaScript API page:

var output = function printOut(continued) {
	if (continued == true) {
		console.log("Make: " + make);
	} else {
		console.log("Cancel button pressed.");
	}
}

var make;

let prompter = Prompter.new();
prompter.cancelButtonTitle = 'Cancel';
prompter.continueButtonTitle = 'Continue';
prompter.addParameter('Make: ', 'make', 'picklist', 'Makes')
	.show('Select a vehicle make', output);

If you want to use pick lists then you can get started out by trying out the middle one with the pick lists, I’ve changed it slightly to avoid the case statement and just generate the pick list name based on the first item selected. You could modify it to generate pick list names for each tier assuming you have a unique key for each.

The final example shows how you could lookup a value from a field in a form with optional lookups for keys that make sense for you. It’s a large example but the core is leveraging getUniqueValuesFromField and getRecordsFromRecordsetMatchingCriterion to get the correct values.

Updated pick list example using the variable to get the next pick list name (just replace the start in the earlier example):

async function start(){
	try {
		let make = await promptPopup("What is the make?", "Make:", "Makes");
		console.log(make);
		
		let models = 'Models - ' + make;
		let model = await promptPopup("What is the model?", "Model:", models);
		console.log(model);
		
		let year = await promptPopup("What is the year?", "Year:", "Years");
		console.log(year);
	} catch (error) {
		console.log("Error: " + error);
	}
}

[tambor]

Hi Sam,
Thank you very much for your very useful explanation and for the link to your script which should be used with all the necessary precautions/caveats you mention.

I think for my purpose I’d better keep using my form with an (unfortunately) very very long list of Saved Searches.

[Wayne Powell]

Wow! Thank you! (SOrry for my delay in responding.)

In my particular case I am working on a complex sewing database rehash.

Sew, (pun intended) in this case I have only a two tier relationship: Pattern Category (Man’s Clothes, Women’s Clothes, Crafts, Accessories, etc.) and within each of those parent categories a set of children: Pattern Type (Shirts, Pants, Coats, etc.)

This is going to give me some meat to chew on for a while as I’m at the very beginning stage of my scripting journey (so I was hoping for the simplest solution possible). In fact I am having to re-learn how Form to Form relationships work again it’s been so long since I originally used Tap Forms that it’s all become fuzzy again.

I was hoping for a simple field script (again to help me understand the beginnings of scripting) that would simply look up the choice chosen in the Parent Field Pick List, and then present choices for the Child Field from a specific pre-existing Pick List along this logic (sorry I only understand the rudiments of JavaScript and so will use an over simplified object oriented example):

chosenCategory = Look Up value in field “x”

If chosenCategory == “a” then show choices from Pick List “a” else

If chosenCategory ==“b” then show choices from Pick List “b”

——

Maybe to sophisticate it up a bit:

chosenCategory = Look Up value in field “x”

Show choices where chosenCategory == namePickList (i.e. display related Pick List with the same Name in this field)

——-

I can also see building an entire array in the script to use manually, rather than use Pick Lists, but using existing Pick Lists gives me the advantage that they are user editable.

——-

To learn this from the ground up, if I could understand the script necessary to show and capture choices from a Pick List in the Script field, that would be the very first step. Then I could build out the script based on a “get” lookup.

I am a babe in the JavaScript woods. ;-)

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

I don’t think you can do this with a simple calculation field because the calculation field can really only work with the data that is in the record. A running total, as an abstraction, would require you to know an original “opening balance” for the record to start from (or assume zero, but when?).

I think I have an example elsewhere of using a form script to calculate a bank balance though it was doing subtraction rather than addition. This was implemented as a form script to recalculate value based on a search, you could reformat it to work with your own form.

The one challenge with this would be is that the ordering the script field works on is based on the ordering in Tap Forms. You would need to make sure that your record ordering in Tap Forms is set correctly when running the script to recalculate the values.

In theory you could do a field script to calculate this as well however you’d have to be careful because field scripts aren’t run when a record is created so it is possible that it could become inconsistent.

[Sam Moffatt]

I did one change to the script that was imported to change line 26 which had apikey=BanMePlz or similar. I changed it to apikey=${apiKey} and it was able to import both of those links you provide.

You need to get an OMDB API key and set it at the top of the script and it looks like the source link that was posted is correct: https://gist.githubusercontent.com/rjames86/d29ae0de333a863c831a8f7379c973e3/raw/d5d6e39dc0f7177037ca0e2fe5e43f5cba37d76d/tvShows.js

If you copy and paste that in and then change the first line to include the API key from http://www.omdbapi.com like this:

var apiKey = 'YOURAPIKEYHERE';

I did try importing The Orville but it didn’t fully import properly for some reason (looks like the data is missing from the API). I also tried Star Trek Enterprise and that seemed to work properly.

[Sam Moffatt]

A query based pick list could be interesting if it mapped onto an underlying form. With the prompter you can define a list of elements manually and you could maintain all of that within either the prompter script or create individual predefined pick lists for each of the combinations.

In thinking about this though you could turn it on it’s head a little and just use a Link From Form field in your target form and in the source have the different combinations. Without an example of your underlying data set, I went with your usual sort of three tier field which is car data. I imported it and each of the three combinations (make, model and year) were individual records. Then using a simple Link to Form in the vehicle database to an inventory form I could do a quick lookup.

If you are using a 1:M field then the Link From Form field will display a view similar to the list view with the number of fields previewed. In the case I was trialling I made it three to display those three fields.

You could also use this sort of flat form approach to build a prompter with multiple pick lists in order, I have a script that gives you back unique sets of results for a given key in a form.

Do you mind sharing a little more what sort of data you have?

Attachments:

You must be logged in to view attached files.

[Wayne Powell]

Also, I guess as an example for the second option I am asking how one could script a field to define it’s own pick list selection array.

[Wayne Powell]

Essentially I would like to expand on cascading Pick Lists using scripting, since it appears not possible otherwise. It is a pretty typical database record scenario.

So the task would be to pre-populate a second (child) Pick List Field choices based on a selection in a first (parent) Pick List Field (first setting the default value if defined).

It would be cool if you could define a field based on the value found in another field. I can imagine two ways to do it, one would be as I describe above based on pre-existing pick lists (which has some advantages). The second more complicated way would be to have the script define a new pick list internally based on arrays defined within the script.

I am just wondering if either is already possible or a simple example exists that I could follow as a starting base?

In my case, the parent pick list could contain values that are the titles of the related child pick lists.

[Nacho Man]

I’m trying out Tap Forms 5 on MAC (Catalina), Still in the trial period. I imported this template but am unable to run it successfully.

When I run the script, it creates a new record (titled ‘No’), but it is blank. No information about the show is imported.

When I run the script again the script console gives this error “series already exists undefined”

When I run the script a third time with a different show URL I get the same error again. “series already exists undefined”

1st URL Provided: https://www.imdb.com/title/tt8712204/
2nd URL Provided: https://www.imdb.com/title/tt3107288/

Do anybody have any suggestions?

[Brendan]

If you tap on the Fields button at the top of the keyboard on the Script Editor, you’ll see a list of fields in your form. There’s a segmented control button that lets you have Tap Forms generate a getFieldValue() command or to generate a variable with the field ID. Tapping on the field will insert the code into your script.

There’s the Script API in the docs and also there’s the Script Talk forum where you can ask questions about scripting plus see the scripts others have posted.

Script Talk

https://www.tapforms.com/help-mac/5.3/en/topic/javascript-api

[Wayne Powell]

I also looked but can’t seem to find a reference as to how to determine the Field ID address when using Tap Forms iPad app to programs script. Is there a built in way, or a Form script to run to print out a list to screen?

[Wayne Powell]

Thank you! Extremely useful. I guess I really am going to need to learn JavaScript to some extent.

Is there a library of pre-made script examples that I have missed located in a Github or some other place?