Total value of a table field

[Rob Naaijkens]

I have a series of records with a Table Field. I would need to use their total value in a second form. Is it possible with scripting? And if so, what is the way to do this?

[Sam Moffatt]

Do you mind expanding a little on your structure?

If you’ve got a link from the second form to the first, you can create a script field that traverses the link to form field (actually might be easier to do it as a link from form field) and then you can call linkedRecord.getTotalOfLinkedFieldForField(tableFieldId, fieldInTableId) to get a total of the field in the table (first ID is the ID of the table field itself, second one is the ID of the field you want to get the total of in the table). This actually becomes a one liner if you only have a 1:1 style mapping with the second form (link to form 1:M from the first to the second will give you a singular relationship in the second form). If there is a many relationship from the second form to the first, then you’ll need to get all of the linked records, loop over them, get the total and then sum it together.

If you’ve going to programatically populate the second form, then that’s a loop to create the record and the same getTotalOfLinkedFieldForField on the source record to calculate the total of the field in the table field.

Share a little about how the two forms are linked (or not) and how you interact with the two forms. The CRM video I did recently has a bunch of fun with linking forms together as well.

[Rob Naaijkens]

Hello Sam,

Thanx for the quick reaction! Is it possible to tell me the complete code of the script? I’m a beginner ;)

I like to try your solutions and will post the results later.

[Rob Naaijkens]

Maybe some more information on what I am trying to do.

First Form: (Assortiment wijnen) all unique records with a Table field “Verkocht” in that table there are 3 records at the moment. The total of the record with “Aantal” I would like to show-up in the second form.

I would like to use the table because these are wines and I would like to see the price per year of period.

Second Form: This is where I would like the total of all the “Aantal” records from the first form.

On your explanation I made the following script. First ID is the field “script” itself and the second is the ID of the field “Aantal” form the first form.

The script works, no errors but the result on both numbers and text is nothing or <empty>.

Hope this makes any sense ;)

Attachments:

You must be logged in to view attached files.

[Rob Naaijkens]

Hello Sam,

Your solution doe snot work unfortunately. The result is “0” (zero).

Do you have another solution or is it not possible to get data from a table?

[Sam Moffatt]

At the moment it doesn’t look like you’ve got any links between the two?

The function doesn’t work because first nothing calls it (you need a getTotalofLinkedFieldForField(); at the end of the script) and second linkedRecord is not defined.

Since there isn’t a link between the two forms, we’ll automate populating the other form. We’re going to use form.getRecords to get a copy of all of the records in the current form (there are a bunch of other examples on the forum as well), get the total and populate that into the other form. We’ll create this script inside the first form since it’s the source. You’ll need to swap in the field IDs from the place holders:

var firstForm_NaamFieldId = 'fld-naamfield';
var firstForm_tableFieldId = 'fld-4dde0c4712954541a69b18d602bfcb27';
var firstForm_tableField_subFieldId = 'fld-60216b72e69b4a9bab98a23c8a019ea9';

var secondForm = document.getFormNamed("Nieuw formulier");
var secondForm_NaamFieldId = 'fld-namefield';
var secondForm_NummerFieldId = 'fld-nummerfield';

function Create_Summaries() {
	// get all of the records from the current form (should be first form)
	for (let currentRecord of form.getRecords()) {
		// create a new record for it in the second form
		let linkedRecord = secondForm.addNewRecord();

		// copy the name across
		linkedRecord.setFieldValue(secondForm_NaamFieldId, currentRecord.getFieldValue(firstForm_NaamFieldId));

		// create the total value field
		linkedRecord.setFieldValue(secondForm_NummerFieldId, currentRecord.getTotalofLinkedFieldForField(firstForm_tableFieldId, firstForm_tableField_subFieldId));
	}

	// save all changes
	document.saveAllChanges();
}

Create_Summaries();

Though ideally you’d set up a link to form field to keep them together. If you create a link to form 1:M from your first form to your second form and then tick “show inverse relationship”, you can create a script field in your second form that looks like this:

var firstForm_tableFieldId = 'fld-4dde0c4712954541a69b18d602bfcb27';
var firstForm_tableField_subFieldId = 'fld-60216b72e69b4a9bab98a23c8a019ea9';
var secondForm_linkFromFormFieldId = 'fld-linkfromformfieldid';

function TotalValue() {
	let linkedRecord = record.getFieldValue(secondForm_linkFromFormFieldId);
	return linkedRecord.getTotalofLinkedFieldForField(firstForm_tableFieldId, firstForm_tableField_subFieldId));
}

TotalValue();

Though for that to work you will need that link to form field setup. Give that a spin and see how it goes.

[Rob Naaijkens]

Hi Sam,

First, thank you very much for all this effort, appreciate that!

I had a link between the second and the first. But not between the first and second.

I used your script but got some errors. Now I still got an error on the script for the second form. Is there a possibility to copy my database and send it to you by e-mail (WeTransfer) so you can take a look?

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

You can drop a copy of the form template (File > Export > Form Template) on the forum which will have all of your fields but none of your data. If the forms are linked, Tap Forms will export both of forms but for this case try exporting a form template for each form to attach since I’m not sure how the links are setup right now.

The “undefined is not an object” means that it didn’t get something back, possibly because it’s the wrong field ID or the field isn’t set. If something is linked this would be unexpected but if something isn’t linked properly, this error would be expected (you’d get an empty result because nothing is linked).

Looking at the last screenshot there is a table rendered there which means the field to Nieuw formulier is a link to form 1:M or M:M rather than a link from form (created by ticking “show inverse relationship”) so we’d need to flip that to get it to work properly.

Shoot through the form templates and we’ll see what’s up.

[Rob Naaijkens]

Thnx for your help, Sam!

I removed the links and the scripts because my database was about 400MB with 142 records. Still my form/database is about 180MB witch is big I think.

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

It does seem a tad on the large side though there is a compact database option under preferences for the document. The links shouldn’t be too much and the scripts are generally tiny. If it’s just records, I don’t think it should be that large for 142 records. If you’ve got attachments or images embedded that can grow things. One thing to watch out for is that images in notes fields are stored inefficiently by Apple’s rich text control so if you have a lot of images in notes field internally macOS translates though to TIFF images with a very poor compression scheme.

Ok some tweaks to the form script to get the ID’s to align but this seems to work for the Verkocht table and Aantal subfield:

var firstForm_NaamFieldId = 'fld-f1bb4282fd05432b9d3205004508ee17';
var firstForm_tableFieldId = 'fld-b40eebf010de45f4ad4f2d0815cec963'; // was 'fld-4dde0c4712954541a69b18d602bfcb27'? 
var firstForm_tableField_subFieldId = 'fld-60216b72e69b4a9bab98a23c8a019ea9'; 

var secondForm = document.getFormNamed("Nieuw formulier");
var secondForm_NaamFieldId = 'fld-28cad0a0ea4d4177aecd20e6f63fe470';
var secondForm_NummerFieldId = 'fld-30f5df230f0b44479e53a83df9295e38';

function Create_Summaries() {
	// get all of the records from the current form (should be first form)
	for (let currentRecord of form.getRecords()) {
		// create a new record for it in the second form
		let linkedRecord = secondForm.addNewRecord();

		// copy the name across
		linkedRecord.setFieldValue(secondForm_NaamFieldId, currentRecord.getFieldValue(firstForm_NaamFieldId));

		// create the total value field
		linkedRecord.setFieldValue(secondForm_NummerFieldId, currentRecord.getTotalOfLinkedFieldForField(firstForm_tableFieldId, firstForm_tableField_subFieldId));
	}

	// save all changes
	document.saveAllChanges();
}

Create_Summaries();

Similarly adding a Link to Form from the first to the second form, ticking “Show Inverse Relationship” and hiding it linked it back appropriately:

var firstForm_tableFieldId = 'fld-b40eebf010de45f4ad4f2d0815cec963';
var firstForm_tableField_subFieldId = 'fld-60216b72e69b4a9bab98a23c8a019ea9';
var secondForm_linkFromFormFieldId = 'fld-1950d775d8774c38b39535f97d4c40a2';

function TotalValue() {
	let linkedRecord = record.getFieldValue(secondForm_linkFromFormFieldId);
	return linkedRecord.getTotalOfLinkedFieldForField(firstForm_tableFieldId, firstForm_tableField_subFieldId);
}

TotalValue();

Also attached a copy of the form template with the changes. You should be able to import it and it should update your document. The form script should create entries but the field script will need you to link an entry to get it to work.

Attachments:

You must be logged in to view attached files.

[Rob Naaijkens]

It works!!! THNX!

If I now want to extend it with the possiibility to add the “Datum” (from the first form – table filed) like 01/22 and get the total of “Aantal” + the names of all sold wines at that date 01/22. Is that much work?

I would like to use this database to know how many wines I sold in a particular month.

[Sam Moffatt]

So doing that you start to move into aggregation territory and that’s possible, I covered a version of that use case in an earlier thread on the forum. You can see some of the progression over there in how the scripts evolve and see similarities to your own script. You’d need to change some of the ID’s to match what you’re using but I think you can translate that across. The last one might be the easiest to work with though you have the complication that I think all of your values are inside the table field so you need to do two loops: one for each of the parent records and then a loop inside for the value of the table field. If you modelled the sales as a link to form relationship instead you could probably do that in a single pass but computationally probably not too different.

[Rob Naaijkens]

Hi Sam,

Is there maybe another solution to get the table fields in the first form so I can make a search?

Then I can make a search on 01/22 and also get al the records with that date.

I am looking for a simple way, my programming skills are not good and I don’t think I can implement your example, to heavy for me ;)

[Sam Moffatt]

I’d pivot from using a table field and instead use a link to form field with the records in a different form. Then you can create saved searches to filter content and also use the MCLV to get summary information as well. You could use scripting to copy the data over but I think what might actually work is copy and paste with two fields in the table view.

From the form you have currently a link to form field will look normally but it’ll also mean you can more easily do aggregations and searches on it’s contents using the other views.

Technically under the hood I believe table fields are modelled similar to link to form fields, it’s just not accessible outside of the parent record.

[Rob Naaijkens]

Thank you for the explanation. One more, simple question, is it possible for you the extend the script you made with an extra field? If possible the “Datum” (date) in the table field of the first form. I think if I have that I can use your the script you made. I would be pleased.

[Sam Moffatt]

Ok, so some changes. I ditched the table field and made it a new form and added a link to form field to connect it. In the new form, creatively labelled “Verkocht”, I added a form script (or three): Aggregate by Date, aggregateForm and md5sum. Attached is a copy of the template that should upgrade an existing document with the appropriate fields and scripts though let me know if it’s missing something.

Aggregate By Date is the script you’ll want to run to generate a report in “Nieuw formulier” grouped by date, name and the sum of aantal. If you look at it’s implementation, it looks like this:

form.runScriptNamed("aggregateForm");

function Aggregate_By_Date() {	
	let fieldMap = {
		['fld-cf72e8f115344d2fa33889757f9f19f0']: 'fld-28cad0a0ea4d4177aecd20e6f63fe470', // naam
		['fld-ac527a6647d049869d5b3b26f8ef6d1d']: 'fld-4f2a8e2e7d974fc08f012a1889760397', // datum
		['fld-ecae6c80bd38432abaaace22806dfb25']: 'fld-30f5df230f0b44479e53a83df9295e38', // aantal
	};
	
	// set our date format, see https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/toLocaleDateString
	// remove "day" from it and you'll roll up by month for example.
	let dateFormat = { "day": "2-digit", "month": "2-digit", "year": "numeric"};
		
	let translationMap = {
		['fld-ac527a6647d049869d5b3b26f8ef6d1d']: (inputValue) => { return inputValue ? inputValue.toLocaleDateString("sv-SE", dateFormat) : ""; }
	}
	
	let targetForm = document.getFormNamed("Nieuw formulier");
	return aggregateForm(form, targetForm, 'fld-ecae6c80bd38432abaaace22806dfb25', fieldMap, translationMap, {'logcsv':true, 'hashedrecord': true});
}

Aggregate_By_Date();

Ok, so we’re loading in the script “aggregateForm” at the start, that’s the utility/library file. Then we set up a field map, this maps the fields from the source form (in this case “Verkocht”) to the destination form (“Nieuw formulier”). Add extra to add more fields to the key/rollup or change up the ID’s to match.

We want to rollup by date so we nee dto format the date, that’s what this dateFormat line does, specify the format for your date. Check out the docs to learn more but that’s pretty straight forward.

The translationMap basically says to take the field fld-ac527a6647d049869d5b3b26f8ef6d1d (which should be a date) and turn it into a string representation of the date. If we don’t do this then we get big long date strings, not fun.

Last lines say to get the Nieuw formulier form and then run this aggregateForm thing. It takes a source form (which is the current form selected; you could make this search too), it has a target form to create records in (or not if unset), the field ID of the field to aggregate, the two maps we talked about earlier and then the configuration options (logcsv creates a CSV in your console log and hashedrecord means that it creates and updates the same record).

The aggregateForm script looks like this:

form.runScriptNamed("md5sum");

function aggregateForm(sourceForm, targetForm, aggregateField, fieldMap, transformerMap = {}, options = {}) {
	for (let defaultKey in defaults = {
		'logcsv': false,
		'hashedrecord': false,
		'returncsv': false,
		'returnjson': false,
	}) {
		options[defaultKey] = options[defaultKey] !== undefined ? options[defaultKey] : defaults[defaultKey];
	}

	// check we have a source form, a field to aggregate and a mapping field.
	if (!sourceForm) {
		throw new ReferenceError("Unset source form");
	}
	
	if (!aggregateField) {
		throw new ReferenceError("Unset aggregate field");
	}
	
	if (!fieldMap) {
		throw new ReferenceError("Unset field map");
	}
	
	if (fieldMap.length < 2) {
		throw new ReferenceError("Field map must have at least two entries (aggregate field and key)");
	}
	
	let rollups = {};
	let destField = fieldMap[aggregateField];

	// iterate to all of the records in the form
	for (var rec of sourceForm.getRecords()) {
		//console.log(rec.getId());

		let keyFields = [];
		let aggEntry = 0;
		let keyEntries = {};
		
		for (let srcField in fieldMap) {
			//console.log(srcField + " => " + fieldMap[srcField])
			let value = rec.getFieldValue(srcField);
			if (transformerMap[srcField]) {
				//console.log("Transforming...");
				value = transformerMap[srcField](value, rec);
			}
			//console.log(value);
			
			if (srcField == aggregateField) {
				aggValue = value;
			} else {
				keyEntries[srcField] = value;
				keyFields.push(value);	
			}
		}

		var rollupKey = keyFields.join(",");

		// Rollup to this key, add to the existing value or set it if not set.
		if (!rollups[rollupKey]) {
			rollups[rollupKey] = {};
			for (let srcField in fieldMap) {
				rollups[rollupKey][fieldMap[srcField]] = keyEntries[srcField];
				rollups[rollupKey][destField] = aggValue;
			}
		} else {
			rollups[rollupKey][destField] += aggValue;
		}
	}
	
	let retval = [];

	// log to console the aggregated values.
	for (let rollupKey in rollups) {
		if (options['logcsv']) {
			console.log(rollupKey + "," + rollups[rollupKey][destField]);
		}
		
		if (options['returncsv']) {
			retval.push(rollupKey + "," + rollups[rollupKey][destField]);
		}
		
		if (targetForm) {
			let destRecord;
			if (options['hashedrecord']) {
				let targetKey = "rec-" + md5(sourceForm.getId()+targetForm.getId()+rollupKey);
				destRecord = targetForm.getRecordWithId(targetKey);
				if (!destRecord) {
					destRecord = targetForm.addNewRecordWithId(targetKey);
				}
			} else {
				destRecord = targetForm.addNewRecord();
			}
			destRecord.setFieldValues(rollups[rollupKey]);
		}
	}
	document.saveAllChanges();
	
	if (options['returnjson']) {
		return JSON.stringify(Object.values(rollups));
	}
	
	if (options['returncsv']) {
		return retval.join("\n");
	}
	
	return rollups;
}

Nothing to customise there, it’s all parameterised. I’ll put it up in the script manager over the weekend.

Attachments:

You must be logged in to view attached files.

[Rob Naaijkens]

Thank you Sam! I gonna take a look at it and hope I understand what I have to put where. Did you mean by the last sentence that you put this script into my export?

[Sam Moffatt]

I have a set of forms I call the Script Manager that has a combination of various scripts that can be relatively easily downloaded and imported into Tap Forms for re-use.

[Author]

Posts