Populate Pick List Field from another field value

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

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

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

[Sam Moffatt]

Here is an example of using a prompter to ask for different values utilising async which is an idea that I credit @daniel_leu with. This is a relatively simple example of using hard coded values:

var tempVar = undefined;

function range(start, end)
{
	let entries = [];
	for(let i = start; i <= end; i++)
	{
		entries.push(i);
	}
	return entries;
}

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 make = await promptPopup("What is the make?", "Make:", ['Ford', 'Holden']);
		console.log(make);
		
		let models;
		switch (make)
		{
			case 'Ford':
				models = ['Falcon', 'Fairlane'];
				break;
			case 'Holden':
				models = ['Commodore','Monaro'];
				break;
			default:
				throw new Error('Unknown make?');
				return;
		}
		
		let model = await promptPopup("What is the model?", "Model:", models);
		console.log(model);
		
		let year = await promptPopup("What is the year?", "Year:", range(2000, 2019));
		console.log(year);
	} catch (error) {
		console.log("Error: " + error);
	}
}

start();

A little later I’ll do another example that leverages multiple pick lists defined in Tap Forms and probably a third example using another form to source values for the pick lists.

[Sam Moffatt]

This is a version that uses pick lists as defined in the Tap Forms document preferences. For the example I made four pick lists, one for the makes (named Makes), two more pick lists for each model variant (named Models - Ford and Models - Holden) and a year list (named `Years). You could obviously expand this on what ever axis that makes sense for your data set as you go down the tree. I attached a quick screenshot of how that looks in the UI.

var tempVar = undefined;

function promptPopup(text, popupName, popupElements){
	return new Promise(function(resolve, reject) {
		tempVar = undefined;
	  	let prompter = Prompter	.new();
	  	prompter.addParameter(popupName, 'tempVar', 'picklist', 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 make = await promptPopup("What is the make?", "Make:", "Makes");
		console.log(make);
		
		let models;
		switch (make)
		{
			case 'Ford':
				models = 'Models - Ford';
				break;
			case 'Holden':
				models = 'Models - Holden';
				break;
			default:
				throw new Error('Unknown make?');
				return;
		}
		
		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);
	}
}

start();

Attachments:

You must be logged in to view attached files.

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

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

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);
	}
}

[Wayne Powell]

Thank you for this, I will give it a go this evening to try out!

[Mike Sturmey]

Hi Sam,

I would like to use your example for presenting a pop up list based on the selection from the first pick list but I am a complete novice and unsure where I should put the script – do I create script fields or do I create text fields using the pick lists and then put the script somewhere else?

kind regards
Mike

[Sam Moffatt]

These are powered by form scripts that you run on demand and it will prompt the interface for you to select the correct values. It has to prompt for each level due to how that interface works (only get results when dialog is submitted) but is flexible.

The source format of your pick list data should help define the pathway. If you have a few options, you could use the built in pick lists and create one for each of the levels. If you have a lot top level options or many layers then you might want to look into the third option.

[Mike Sturmey]

Hi Sam,

I have managed to get your script working (to some degree with next to zero Javascript knowledge) however I have got to a point that I am really stuck and wondering if you could assist – I have attached the form.
The issues are:
When I create a new record, the field is automatically populated with ‘[object Promise]’ – I would like it to be empty.
The script seems to run multiple times (for the number of records in the DB) instead of just once.
The script replaces all the values of all the records – not just the one I am editing.

If you could assist it would be great
cheers
Mike

Attachments:

You must be logged in to view attached files.

[Daniel Leu]

You are using Sam’s script as a field script, but I think you would want to use it as a form script.

Then in your custom layout, you can create a button to run that script.

And the field type for ‘Epiphytic cactus’ should be text.

[Mike Sturmey]

Thank you Daniel – Problem solved!! – it has taken me hours!
Do you happen to know if it is possible to change the Tap Form icon on the pop-up to a different icon e.g in this case a ‘plant icon’?

cheers
Mike

[Sam Moffatt]

Great to hear you got it figured out with some help from Daniel :)

Changing the icon sounds like an interesting feature request for Brendan, though I’m not sure where you’d store the image data (perhaps use a form icon?).

[Brendan]

Hi Mike,

Do you mean the Pick List button? If so, then no. The only place you can change the icon is for a form itself. Although you could technically use Emojis for field names too.

Thanks,

Brendan

[Sam Moffatt]

I think they meant in the prompter interface, it shows up with the TF icon on the left side. It would be cool to be able to change it some how (hence my statement about perhaps using a form icon, maybe even default to the current form with the option to use another form’s icon).

Attachments:

You must be logged in to view attached files.

[Mike Sturmey]

Thanks Sam & Brendan,

It all seems to be working well apart from one thing. If I add new items to the Picklist i.e add a new Genus and associated species, when I call the script from the button the new list of Genus does not show the new addition. I have tried quitting and relaunching but it does not help. It is a bit weird as the script does not require adding any specific data associated with the pick list. Is there a way of refreshing the list – it is almost as though it is cached,

kind regards
Mike

[Daniel Leu]

Just tried it here and it works like a charm. I guess you already double checked that there is no typo.

Does the new genus show at all?

[Mike Sturmey]

Sorry – Stupid me – there was a ‘Space’ before the word – all working now

[Sam Moffatt]

Odd that a space messed it up but good to hear it fixed it up.

[Brendan]

Sam, I just saw your comment about the application icon showing on the dialogue. This is because it’s an NSAlert panel being displayed. And by default macOS displays the application icon there.

But I’ve just added code to see if there’s a form icon to use that instead. The only problem is the resolution of form icons is not that great because they weren’t designed to fit in that size on screen. So actually I’m not sure if I should add that now.

[Sam Moffatt]

Fair enough, maybe a new feature in a later release to have high res icons to fill it :)

[robinsiebler]

I downloaded Vehicle-Data.tff, but I get an error when I try to open it:

Attachments:

You must be logged in to view attached files.

[Daniel Leu]

This is a template file. Try File -> Import -> Form Template

[robinsiebler]

Ok, I got the form loaded now (Thanks!), but I have no clue how to use this. I did not see any pick lists, so I created them based on the screen shot. Now what? I can see I have a lot of learning to do!

[Sam Moffatt]

I think based on the reply on the other thread you’re headed down a different path. Not entirely sure what was in the template but it might not have had the pick lists setup depending on how that was exported.

[robinsiebler]

Yeah, the other way is working for me. I will definitely want to explore some of the more advanced features, but…I don’t need to do it today.

Thanks, again, for all your help!

[Brent Willingham]

I think this thread pretty well describes what I am looking to do. I want to create a database that tracks activities and expenses related to Wildlife Management Practices that need to be done to maintain a wildlife exemption on a property. There are 7 different pre-defined main management practices and each has it’s own set of sub-practices. So a parent/child pick list situation – I think. There would be a Management Practice field that has a pull down pick list. Depending on which practice is chosen, it’s respective sub practice list would appear in the Sub Practice field.

Since I’m a novice and don’t have any experience with scripting, I think I’d be ok with Sam’s idea in Reply 43418: “If you have a few options, you could use the built in pick lists and create one for each of the levels.”

I don’t expect the categories or sub-categories to change so I don’t mind creating pick lists for each of the levels – I just don’t really know how to get started here.

Thanks,
Brent

[Brendan]

Hi Brent,

Cascading Pick Lists is something I’m working towards, but it’s not there yet.

But within a Script you can set a Pick List on a field based on some condition.

my_field.pickList = document.getPickList('Sub-Practice 1');

The tricky thing about cascading Pick Lists are there are really two different ways to go about them.

1. A simple hierarchical menu whereby you select the last level value and it gets entered into the field.
2. A more complicated scenario where you select a value from a Pick List in Field A, then Field B’s Pick List changes to list only those values that are associated with the selected value in Pick List A. For example, select a Country, then the Province field’s Pick List changes to those Provinces which are relevant only for the selected country.

[Brent Willingham]

Brendan,

Thanks for the prompt response. The more complicated scenario, scenario 2, is what I’m looking to do. I have no experience with scripting, but looks like a fun challenge and this may be a good little project to practice on.

Thanks again for the support, and I really love the App!

[Brent Willingham]

Ok, I’ve created a new database called Wildlife Exemption and a form called Wildlife. I’ve created all my fields, one of which is a Pick List field called “Management Practice”. I created a corresponding pick list also named “Management Practice”. There are 7 values in the “Management Practice” pick list. One of the values is “Habitat Control”.

I have created pick lists for each of those 7 values that I am referring to as “sub practices”. So, for example, I have a pick list called “Habitat Sub Practice”. In that pick list are things like Grazing Management and Brush Management.

The idea is that when the user is on the “Management Practice” field, they are presented with the list from the “Management Practice” pick list. They have 7 options, but let’s say the choose “Habitat Control”. The next field on the form is called “Sub Practice”. In this field, the pick list “Habitat Sub Practice” would be chosen because of the selection above.

The only way I can think of to do this is with a long nested IF THEN kind of routine:
IF Management Practice = “Habitat Control” THEN “Habitat Sub Practice”
ElSE IF Management Practice “Erosion Control” THEN “Erosion Sub Practice”
and do 5 more ELSE IF’s so that all 7 Management Practice options are covered.

I’m guessing that this is probably not the smartest way to go about it, and I’m sure I have the syntax way off. I’ve gone through T.L. Ford’s Java Scripting 101 and that has been very helpful, but my only programming experience is with BASIC decades ago in High School.

Would someone be able to get me kicked off with the start of a script here?

Thanks,
Brent

[Brendan]

Here’s a script that I was messing around with that will change the Province field’s Pick List depending on the value selected from the Country field’s Pick List:

function Province_Pick_List_Switcher() {

	var country_id = 'fld-de7985c881804154b037c68dc4c24414';
	var country = record.getFieldValue(country_id);
	var canadian_provinces_pick_list = document.getPickListNamed('Canadian Provinces');
	var us_states_pick_list = document.getPickListNamed('US States');
	var province_id = 'fld-b1458c16690744129ff145f7b9607c99';
	var province = form.getFieldWithId(province_id);
	
	if (country == 'Canada') {
		province.pickList = canadian_provinces_pick_list;
		
	} else if (country == 'United States') {
		province.pickList = us_states_pick_list;
		
	} else {
		province.pickList = null;
	}
	
}

Province_Pick_List_Switcher();

You could modify this script for your own needs. I added it as a Script Field and then hid the Script Field so it doesn’t show on the form, but it still works.

[Brendan]

Here’s the form template. The only problem with it is if you switch records, the Province pick list would not be synced with the Country selection until you actually changed the country.

FYI, I have already built a new cascade pick list feature into the next big version of Tap Forms I’m currently working on. No ETA though. And no scripting required for it.

Attachments:

You must be logged in to view attached files.

[Brent Willingham]

Thanks for the script Brendan. Glad to know about the new feature for a future release. If I can get this working, my goal is to share it with other land managers and ranchers in Texas for them to use. We all have to submit a report to the County at the end of each year and it can be a pain going through receipts and trying to remember everything. This could help keep things organized.

[Author]

Posts