Search Results for 'script'

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

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

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.

[Sam Moffatt]

So on iOS, you can get to the script editor and run script from the list of record but you need to tap into a record to select it before running the script if that makes sense. When you’re in the list view, you usually get the error that record is not defined because you’re not in a 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.

[Stig Widell]

What do you medan with:
Make sure on iOS you are in the record before using the
script editor or running the script?
I made the run of script in IOS

[Sam Moffatt]

It looks like it’s working? I imported your template over my existing database. Make sure on iOS you are in the record before using the script editor or running the script but I did a quick test and it seemed to work for me.

Attachments:

You must be logged in to view attached files.

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

[Sam Moffatt]

This is a bit of a hack but you can use a script field to do the ordering. Create a new script field and then you’ll need to put in something that looks like this:

var keyfield_id = 'fld-id';
var picklist_name = 'Picklist name';

function PicklistID() {
	let key = record.getFieldValue(keyfield_id);
	let picklist = document.getPickListNamed(picklist_name).values;
	for(let offset in picklist) {
		if (picklist[offset].value == key) {
			return offset;
		}
	}
}

PicklistID();

In the script above, edit the first two lines and change the field ID (the fld-id text) and the pick list name (the Picklist name) to be the ID of your Dénomination field and the name of the pick list it uses.

Here is an example I used where I put in the field ID and the pick list was called “Marketplace”:

var keyfield_id = 'fld-fa37906add2942c88bce3b500561c42d';
var picklist_name = 'Marketplace';

function PicklistID() {
	let key = record.getFieldValue(keyfield_id);
	let picklist = document.getPickListNamed(picklist_name).values;
	for(let offset in picklist) {
		if (picklist[offset].value == key) {
			return offset;
		}
	}
}

PicklistID();

That will give you an ID starting from zero I believe in order of the pick list that you can then use as a sort field. It’s not as elegant as having the ability for the text field to use the pick list ordering but it’s something you can do right now.

[Stig Widell]

Ok I entered the ”Kalva” in the Script in form Rekryteringsdjur but I didnt run it. Now when I did I got enclosed error

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

So in the Rekryteringsdjur record, you need to create a new form script called “Kalva” and then run that form script for it to create and link the records together because the script does validation that not only you set the bull but also that the bull with that ID exists if that makes sense.

[Stig Widell]

OK Sam,
I imported the Script in your ScriptManager …tfarc into the document Djur successfully (I hope)
and I copied and pasted your Kalva into Scripts in the form Rekryteringsdjur also successfully (I hope) but I havent tried it yet (too late in the evening). How do I start an entry of a new calf, with a new calf record or with an identified mother record? You discussed both alternatives above.

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

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

[Sam Moffatt]

You need to import the Script Manager TFARC to get two extra forms in your own document and run it locally, it imports my library of scripts automatically. The script I created above references one of those scripts to operate, in particular getRecordFromFormWithKey, which scans for records that have a particular value and returns the first one it finds. This is how I map the ID’s back to make the links work.