Create new field with referenced values

[Steven Daniel]

Hi – new to Tap Forms and no programming experience. But it seems like it should be simple to accomplish the following:

I have a form that I added a new text field (synonym). I want to populate all the records in this field with the same value in the related field (species) when the field plant_ID (a number field) value in matches the same plant_ID field in a different form.

I’m not sure I’m using the correct language. In the first screenshot here I have the form (Synonyms) with just two fields. In the next screenshot I have the new blank Synonym field, that I want to fill with the proper name when the plant_ID matches. So when the plant_ID = 66 it should fill Rhus toxicodendron in the new synonym field in the Atlas form.

A simple script that I can cut and paste would be appreciated.

One possible snag – sometimes there are multiple synonyms for the same species. Would it be possible to insert each synonym into the same field? Or how best to handle that.

Thanks!

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

Not quite copy paste but leveraging TF to do a lot of heavy lifting here.

If everything is keyed off plant_ID, create a new “Link to Form” field in the atlas and link it to your synonym form and plant ID fields. That should automatically create links to the synonym records as either new synonyms are added or new plants are added.

If you’re going to do it all programmatically, I’d swap the type of your synonym field from a “text” field to be a “script” field. Double click on the field name to open the script editor, delete the boilerplate code, select the “Scientific_Name” field on the left on the field list (it should show up once you create the “Link to Form” JOIN field) and then on the bottom left in the “Snippets” it should have “child records loop”. It’ll insert a new function called recordsLoop for you and it’ll have a line that says record.getFieldValue and if statement in the middle of it. After the line that was record.getFieldValue, put in a line like the following:

var entries = [];

My guess it should look something like this:

	var atlas_synonyms = record.getFieldValue(atlas_synonyms_id);
	var entries = [];

We’re going to update the if statement to look something like this:

		if (scientific_name) {
			entries.push(scientific_name);
		}

Lastly there will be a line that says return;, update it to say return entries.join(' '); to get a space separated list of synonyms or return entries.join(', '); to have a comma separated list.

—

If you want to keep the synonym field as a separate text field so you can add other values, then you’ll need to do this instead of the return:

record.setFieldValue('fld-synonymfieldid', entries.join(' '));
document.saveAllChanges();

Usually I guard this with a check for if that field has a value already so that it doesn’t overwrite existing values automatically, putting this at the top of the function:

function recordsLoop() {
	if (record.getFieldValue('fld-synonymfieldid')) {
		return;
	}

This prevents the script from overwriting the text field if there is a value in it already. Double clicking on the field name in the left on Tap Forms should populate the record.getFieldValue code for you (remove any var blah = bit).

Hope this helps! Not quite copy/paste but hopefully useful enough instructions to get it sorted.

[Steven Daniel]

Wow – way more complicated and involved than I expected. So I presumably made an error somewhere – please see attached scripts – both show same error message “can’t find variable entries” on line 23. The only difference in the two scripts is I added the last bit of code you suggested to be able to add other synonyms down the road in one, without it in the other.

I kind of, at a very basic level, get what you’re doing here, but really need to get my head around this scripting more. And really appreciate the help. I thought it would be something relatively simple like if Plant_ID (Atlas) = Plant_ID (Synonyms) enter Scientific_Name (from Synonyms) into the new Atlas Synonym field. Oh well – one step at a time. Thanks again!

Can you see what went wrong and how to fix it?

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

Yeah, I think maybe the calculation field could be improved to provide a way to do string concatenation of linked fields as an option but right now that’s not an option and scripting is kind of the only way. It’s a little verbose but does at least get the job done.

Ok, let’s try to get the basic script field returning a value. This should get things a little further along (I copied it out of the image so the OCR needed some tuning and I think I got everything):

function recordsLoop() {
	var link_to_form_id = 'fld-f835affdf6f34d9890a55d38f985d8cd';
	var scientific_name_id = 'fld-7c6e6899c8c2400b8127a82857cd619d';
	var link_to_form = record.getFieldValue(link_to_form_id);
	var entries = [];

	for (var index = 0, count = link_to_form.length; index < count; index++) {
		var scientific name = link_to_form[index].getFieldValue(scientific_name_id);

		if (scientific_name) {
			entries.push(scientific_name);
		}
	}
	return entries.join(', ');
}

recordsLoop();

That should work as a field, you can run it in the script editor and it should show you something.

———-

For the second one where you update a text field I can see you had a crack at that too. This line seems like a weird replacement or something:

		var atlas_synonyms = record.getFieldValue(atlas_synonyms_id);

It should have had an ID in it, try clicking on the ID button and it’ll insert something like:

		var atlas_synonyms_id = 'fld-1234';

Your line should be different and longer number but that’ll be the format. I do wonder if the snippet system did something odd (I ran into that the other day on iOS). Minor tweak to my original post to use this variable:

function recordsLoop() {
	var atlas_synonyms_id = 'fld-REPLACEME';

	if (record.getFieldValue(atlas_synonyms_id)) {
		return;
	}

	var link_to_form_id = 'fld-f835affdf6f34d9890a55d38f985d8cd';
	var scientific_name_id = 'fld-7c6e6899c8c2400b8127a82857cd619d';
	var link_to_form = record.getFieldValue(link_to_form_id);
	var entries = [];

	for (var index = 0, count = link_to_form.length; index < count; index++) {
		var scientific name = link_to_form[index].getFieldValue(scientific_name_id);

		if (scientific_name) {
			entries.push(scientific_name);
		}
	}
	var joined = entries.join(', ');
	record.setFieldValue(atlas_synonyms_id, joined);
	document.saveAllChanges();
	return joined;
}

recordsLoop();

Running that in the script editor should also result in the text field being updated as well plus returning the value for debugging. You do still need to insert the ID for the text field that you want to use at the top where it says fld-REPLACEME.

Let’s give those a spin and see what we get back :D

[Steven Daniel]

Thanks again. Not there yet. I tried both and they run BUT only return 4 names in the box at bottom left of the script page (attached) and there are no entries in the Synonym field in the Atlas Field…

Attachments:

You must be logged in to view attached files.

[Steven Daniel]

Oh wait – they are there as all synonyms for #801. I could have sworn that field was empty earlier…Anyway…the 4 are there, all separated by commas. So definitely progress.

[Sam Moffatt]

No this is good! So the box in the left is the result of the field script, if those are what you expect then we’re pretty close. Is you save out, you should see in your record where that field is the values you’re expecting. Again if you don’t need to override the values ever (e.g. it always comes from that other form), then instead of the text field you can just use the script field and it’ll auto-update for you.

For the text field, make sure the one you’re testing the value is empty and if that still doesn’t do the trick, you can disable the guard (/* starts a multi-line comment and */ ends a multi-line comment):

function recordsLoop() {
	var atlas_synonyms_id = 'fld-REPLACEME';

	/*
	if (record.getFieldValue(atlas_synonyms_id)) {
		return;
	}
	*/

	var link_to_form_id = 'fld-f835affdf6f34d9890a55d38f985d8cd';
	var scientific_name_id = 'fld-7c6e6899c8c2400b8127a82857cd619d';
	var link_to_form = record.getFieldValue(link_to_form_id);
	var entries = [];

	for (var index = 0, count = link_to_form.length; index < count; index++) {
		var scientific name = link_to_form[index].getFieldValue(scientific_name_id);

		if (scientific_name) {
			entries.push(scientific_name);
		}
	}
	var joined = entries.join(', ');
	record.setFieldValue(atlas_synonyms_id, joined);
	document.saveAllChanges();
	return joined;
}

recordsLoop();

By the way, don’t forget to replace fld-REPLACEME with the ID of the field ID of the text field you want to update.

[Steven Daniel]

Sorry – I’m a little dense. Yes – since I don’t think I’ll be overriding the values – just using the synonyms in the Atlas Synonyms file. So in that case I’ll just use the first script (keeping Atlas synonyms as a script field) you suggested that did return 4 values in one record? Is that Correct? Ignore the script for text field that you wrote above?

So, since the first script one is close and does return 4 results in one record – what to change next to update all the records in the entire file? I don’t understand why it only updated the single record.

[Sam Moffatt]

Yup! If you don’t want to override the values then the first script if it’s working for you will do the trick and you don’t need to worry about the other one.

There is a check box in the script editor for “Update records when saving” which will update all records in your form. The reason to keep it deselected is so it doesn’t update all records because you might save during development, it might not be the right result so you don’t want to have to wait for it to update every record before finishing. The other way is to use the refresh button at the bottom of either the record to refresh just the record or at the bottom of the list to refresh all of the records in the form. It’ll recalculate the calculation and script fields for you automatically. Once you’ve done this once, Tap Forms should keep things up to date each time you make a change but if it doesn’t for what ever reason you can just click the record refresh to force an update the to record in question (also Command+R).

[Steven Daniel]

I don’t understand this. Now the same script is giving no results. At first it had the same result (4 names for one record) – now zero (attached). I have tried quitting out, and starting from scratch and still nada.
Is this funkiness of TF? Or maybe I did something but I don’t think so.

Also – I did try refreshing from the bottom of the list when I did have the working script. It said it was going through all the records but nothing changed.

And for the life of me I cannot find the “update records when saving” checkbox in the script editor. Aargh!

Attachments:

You must be logged in to view attached files.

[Steven Daniel]

I just found the “update records” checkbox. It is in the Field Script Editor – I was working in the script editor under scripts, if you follow me. That checkbox is not in that script editor where I was working, as you can see in the last screenshot.

So now I’m still getting no results, but I wonder which script editor I should be using? Does it matter? I’ve tried it in each one and still getting zero results.

[Steven Daniel]

It just worked!!! It looks like all the synonyms have populated the field! I totally don’t get what happened but I am calling it a night. Thanks again!

[Sam Moffatt]

There are two types of scripts: field scripts and form scripts.

When you create a new field via the “Fields” tab and set the type to “script”, then that’s a field script. Field scripts are evaluated when ever a field they reference (via record.getFieldValue) changes and should automatically be re-evaluated. Field scripts are good for this sort of use case where you have related data that you need to store. These results of the scripts are cached with the records so that once they are evaluated, they don’t get re-evaluated unless something they depend on changes, you refresh the record (including all records) or you hit the “update records when saving” in the script editor for the field script. Ideally field scripts refer to information primarily in the record in question.

Form scripts are in the “Scripts” tab and are designed to be executed on demand. These can work on a single record to do an on demand action or can be used to work on multiple records. I use form scripts to do one of imports of data either through web services or parsing fields in a record, to do bulk imports and creation of records from external systems or to do one off data clean up work. The key is that form scripts have to be explicitly executed whilst field scripts will be implicitly updated by Tap Forms for new records or when referenced fields update.

Good to hear it’s finally working for you though and hopefully this explanation makes sense.

[Steven Daniel]

Your explanations are very helpful. Thank you so much for helping me work this out.

[Author]

Posts