Search Results for 'form.getRecords'

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

[Daniel Leu]

It is not that complicated! This is a form script that loops through all records. It reads that value of your note field, modifies it, and then writes it back. Additionally, it checks that the note field content is defined, otherwise further processing is skipped.

The script currently replaces all line-breaks with a whitespace and removes all leading and trailing whitespaces. As you see, you can use your regex in JavaScript as well :)

The only change you need to make is define the note_id constant according to your form script. There are several ways to get the id. One way is to click on the field name in the script editor, then select and copy the ID string that is displayed underneath all the field names.

function Remove_Spaces() {

   const note_id = 'fld-xxx' // set note_id according to your form

   for (let rec of form.getRecords()){
	
      // get note field
      let note = rec.getFieldValue(note_id)
      console.log("Orignial note: " + note)

      // Check that note is defined
      if (note) {
         // modify note (replace line breaks with
         // whitespaces and remove whitespaces from
         // both ends of string
         let modifiedNote = note.replace(/\n/g, " ").trim()
			
         console.log("Modified note: " + modifiedNote)
	
         // save note field
         rec.setFieldValue(note_id, modifiedNote)
      }
   }
	
   document.saveAllChanges()
}

Remove_Spaces()

• This reply was modified 2 years, 10 months ago by Daniel Leu. Reason: replaced tabs with ' ' for better readability
• This reply was modified 2 years, 10 months ago by Daniel Leu.
• This reply was modified 2 years, 10 months ago by Daniel Leu.

[Sam Moffatt]

So the outer for loop to go through all of the records in the current form is required regardless of approach (first or second) though the inner for loop wouldn’t be required but the contents of the loop would still be required even if dealing with a single value. The variable names are slightly different between the two examples but you can see a similar pattern of form.getRecords then getFieldValue then creating the rec- prefixed ID, getRecordWithId to see if it exists, and if it doesn’t addNewRecordWithId to create the record and setFieldValue to set the value. The second one doesn’t use a JOIN so it needs to explicitly call addRecordToField to make the link to form field work properly.

I’ve added it to my todo list for the videos, let me see if I can sneak some time in tonight to work through the process in video form because it becomes a little easier I feel when you can see the pieces coming together and follow along (also why my videos are uncut to ensure that everything is visible step by step).

[Sam Moffatt]

The “OR” use case is a little different because you need to split out the individual values first. Thinking a little about this what might work is a form of email addresses and then a link to form M:M relationship. Each email address is split up and added to the other form and linked back. Since it’s M:M you can link multiple parent records and then count that way to unique email addresses.

I created a simple form called “Source Form” and added a name and email address field to it. I then added a second form called “Emails” and added a field called “Email” to it. In the “Source Form” I went back and added a Link to Form to the “Emails” form, set it to M:M and ticked “Show inverse relationship” (I do that last because there used to be a bug where if you changed the type from 1:M to M:M, the inverse field wouldn’t update properly, I think it’s fixed). Back in our “Emails” form, I added a script field that just returns the length of the Link from Field that was created (it’s as simple as this: record.getFieldValue('fld-2b0ad00a96bd4cf4b20dca95899a7a5a').length; where the field ID is the ID of the link from form field). Last piece is to grab the md5sum.js file and add it to the “Source Form” as a new form script and then create another form script that I called “Extract Emails” with this in the contents:

form.runScriptNamed("md5sum");

var source__email_id = 'fld-4e8071f8f3ce4a75a66954f6a3c636ef';
var emails__email_id = 'fld-7e880e79867345549fb04f377412fefd';
var emails_link_id = 'fld-f5d00c535c3c437a87a262f6d0f434e4';

var emailsForm = document.getFormNamed("Emails");

function Extract_Emails() {

	for (let currentRecord of form.getRecords()) {
		let emails;
		try {
			emails = currentRecord.getFieldValue(source__email_id).split(' ');
		} catch(e) {
			console.log("Error processing record: " + e);
			console.log(currentRecord.getUrl());
			continue;
		}
		
		for (let email of emails) {
			console.log(email);
			let recordHash = "rec-" + md5(email);
			let candidateRecord = emailsForm.getRecordWithId(recordHash);
			if (!candidateRecord) {
				candidateRecord = emailsForm.addNewRecordWithId(recordHash);
				candidateRecord.setFieldValue(emails__email_id, email);
			}
			currentRecord.addRecordToField(candidateRecord, emails_link_id);
			document.saveAllChanges();
		}
	}
}

Extract_Emails();

The first line imports the md5sum script we created. The next three var statement lines are the field ID’s for the “Email” field in the “Source” form, the field ID of the “Email” field in the “Emails” field and then the field ID of the link to form field in the “Source” form that links back to the “Emails” form. The emailsForm is getting the Tap Forms object representing the “Emails” form.

The function then iterates over all of the records in the current form (e.g. “Source” form), it then splits up the email field based on a space. You can change this to be a comma or any other character you want. If you need more than one separator you will need to use a regular expression but we’ll skip that. The try/catch is just in case there is something weird to trap the error there and keep processing since we’re dealing with a field value.

The second for loop is then iterating over the emails in the record and creating a new record ID using the MD5 of the email. This pattern is somewhat risky because we intentionally will create colliding document ID’s that generally Tap Forms will avoid. We do that here to be able to do the key lookup without having to build the index ourselves (TF will do that for us). We check to see if a record already exists using getRecordWithId and if it doesn’t we create it using that same ID using addNewRecordWithId and set he email address. We then have Tap Forms link the “Source” record we’re working with to the new email record and save all the changes.

The saveAllChanges is inside the loop because I’ve had issues with TF where the links behave weirdly if you manipulate too many of them via scripting without saving. There’s probably a bug somewhere but reproducing it is a challenge so this is my work around.

This should fill up the “Emails” form with the unique email addresses all linked to their relevant parent forms. I had to do a manual refresh of the form because the script field didn’t populate the links properly. Once I’d verified my duplicates were being detected, I created a saved search on the count of linked records being greater than one.

Attaching a form template with that in it as an example to play with. The earlier one I did a quick change based on an existing form so not so easy to share because it’s got a bunch of other fields in it but this is a relatively concise use case and should work to handle multiple email addresses as well.

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

Are you looking for exact duplicates (all fields identical) or duplicates on a primary or subset of key fields? If you want all fields identical, does this extend to link to form fields, table fields or photo fields and their contents?

I was working a while back on tooling to do merging of records through the scripting interface to handle when I end up with duplicate shipping records due to key mismatch (sometimes the tracking numbers are changed in transit) but I never got it finished because the UI experience wasn’t something I figured out.

If you’re after a subset of key fields, I did a quick POC where you create a new field in your source form with the key. I already had a composite key field built using a calculation field I use which looks like this (square brackets for field placeholders):

IFEMPTY([Marketplace];"No marketplace";
CONCAT([Marketplace], "/",[Store Name], "/",[Order ID]
))

This creates what should be a unique key for the record based on my own metadata (designed to handle direct sales and hosted marketplaces). I then created a new form called “Orders Dedupe” and put in it three fields: a *string* type field called “key”, *link to form* field called “Order Dedupe” and a script field which counts the entries in the order. The link to form field is configured as a JOIN type on the “key” field of the dedupe form and the calculation field in the original form. The script field looks like this (change your ID’s to match):

function Key_Match_Count() {
	var order_dedupe = record.getFieldValue('fld-fde68e7d2b384cb2a4452d3ae66bbab1');
	return order_dedupe.length;
}

Key_Match_Count();

In this form also create a new saved search that uses the script field and is set to look for values greater than one as those will be the duplicates.

Last step is to populate this form, go back to your base form and create a new form script. I wrote the script below to scan each record, use an md5sum implementation to create a hash of the key field and then look to see if that record exists in the dedupe form:

document.getFormNamed("Script Manager").runScriptNamed("md5sum");

var purchase_key_id = 'fld-3e49aaa5bc32429c8f0f0f234878356d';
var dedupfield__key_id = 'fld-c52906ee940142a0a54fac1a98346afd';
var dedupForm = document.getFormNamed("Order Dedupe");

function Extract_Purchase_Keys() {
	for (let sourceRecord of form.getRecords()) {
		let purchaseKey = sourceRecord.getFieldValue(purchase_key_id);
		if (!purchaseKey) {
			console.log("Missing purchase key for record: " + sourceRecord.getUrl());
			continue;
		}
		let purchaseKeyHash = "rec-" + md5(purchaseKey);
		let dedupRecord = dedupForm.getRecordWithId(purchaseKeyHash);
		if (!dedupRecord) {
			dedupRecord = dedupForm.addNewRecordWithId(purchaseKeyHash);
			dedupRecord.setFieldValue(dedupfield__key_id, purchaseKey);
		}
	}
	document.saveAllChanges();
}

Extract_Purchase_Keys();

This actually found me a dupe record that I hadn’t found in my orders form when I went back to look at the saved search. It’s a bit of a journey, might turn it into a video at some point when I get some more time.

[Sam Moffatt]

Purchase date to price to person doesn’t quite make sense to me but purchase date to person with price aggregated makes more sense, is that what you mean?

You have two options that you can do, one is to start building a composite key with each of the fields you want as commas, that gives you a flat list but if it’s in CSV outputting it looks somewhat transparent. The other is to continue to add another array but that means a bunch of work to validate it as we’ve got (basically the process we did to add marketplace).

I’ll do the first one because it lets you store an arbitrary number of roll ups at the cost of making it a little harder to extract later. Since you’re not using the roll up form and just the CSV this should be ok for you:

Adding extra fields should be a matter of creating the variable and adding them to the rollupKey line. This should work though I’ve not tested it:

function Aggregate_By_Date() {
	// 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.
	var dateFormat = { "day": "2-digit", "month": "2-digit", "year": "2-digit"};
	
	// this is where we're going to store the rollups per day.
	var rollups = {};
	
	// iterate to all of the records in the form
	for (var rec of form.getRecords()) {
		var marketplace = rec.getFieldValue('fld-c163aba17ae64c4d93b5a53819a139dc');
		var person = rec.getFieldValue('fld-personid');
		var purchase_date = rec.getFieldValue('fld-ccbd9a8f51d34246bebfb31aa4e397dd');
		var price = parseFloat(rec.getFieldValue('fld-08129d71ab0f4fa4a2749456281fca07'));

		// Skip entries that don't have a price or date set.
		if (!price || !purchase_date) {
			continue;
		}
		
		// format the date for use in the rollup.
		var formattedDate = purchase_date.toLocaleDateString("en-AU", dateFormat);

		var rollupKey = [formattedDate, marketplace, person].join(", ");

		// Rollup to this key, add to the existing value or set it if not set.
		if (!rollups[rollupKey]) {
			rollups[rollupKey] = price;
		} else {
			rollups[rollupKey] += price;
		}
	}
	
	// log to console the aggregated values.
	for (var rollupKey in rollups) {
		console.log(rollupKey + ",$" + rollups[rollupKey]);
	}
}

Aggregate_By_Date();

[Brendan]

Ok, so then it would be like this:

var field_id = 'fld-807fc883160d41ea802f26b7b0bd5af5';

function findAndReplace(find_text, replace_with) {
	
	var records = form.getRecords();
	
	for (var index = 0, count = records.length; index < count; index++){
		var rec = records[index];
		var website_url = rec.getFieldValue(field_id);
		if (website_url != undefined) {
		   website_url = website_url.replaceAll(find_text, replace_with);
		   rec.setFieldValue(field_id, website_url);
		}
	}

	document.saveAllChanges();
	
	return;
}

findAndReplace('viewer', 'ViewerForm.aspx');

You have to use your own value for field_id.

[Fernando DS]

Hi Brendan,

The script is as follows:

function Buscar_Y_Modificar() {

function Replace_Metal() {
var fieldId = ‘fld-fieldid’;
for (var record of form.getRecords()) {
var fieldValue = record.getFieldValue(fieldId);
if (fieldValue.includes(“rock duro”)) {
record.setFieldValue(fieldId, fieldValue.replace(“rock duro”,
“heavy metal”));
}
}
form.saveAllChanges();
}

Replace_Metal();

Replace fld-fieldid with a field ID from either the script editor or
find it in the field editor underneath the description field.

Sorry, my english is very poor. I don’t understand exactly what you mean with curly and straight quotes.

[Fernando DS]

This is how the script is redacted:

function Replace_Metal() {
var fieldId = ‘fld-fieldid’;
for (var record of form.getRecords()) {
var fieldValue = record.getFieldValue(fieldId);
if (fieldValue.includes(“rock duro”)) {
record.setFieldValue(fieldId, fieldValue.replace(“rock duro”,
“heavy metal”));
}
}
form.saveAllChanges();
}

Replace_Metal();

Replace fld-fieldid with a field ID from either the script editor or
find it in the field editor underneath the description field.

[Sam Moffatt]

For something like that, a new form script like the following should do it:

function Replace_Metal() {
  var fieldId = 'fld-fieldid';
  for (var record of form.getRecords()) {
    var fieldValue = record.getFieldValue(fieldId);
    if (fieldValue.includes("rock duro")) {
      record.setFieldValue(fieldId, fieldValue.replace("rock duro", "heavy metal"));
    }
  }
  form.saveAllChanges();
}

Replace_Metal();

Replace fld-fieldid with a field ID from either the script editor or find it in the field editor underneath the description field.

[Sam Moffatt]

This one is a fun one, TF doesn’t track users but devices. It has a modified by device field but that can change if someone modifies it (which makes sense). You can do a ‘created by device’ or ‘record author’ by doing the following.

First step is to add a new field of type ‘Modified by Device’. You’ll need it’s field ID from below the description field. The second step is to create a new text field to store the ‘created by device’ or maybe ‘record author’. The reason to use text field is so you can override it later, especially as your old records will be likely wrong. The third field is a script field that looks something like this:

function Set_Record_Author() {

	var record_author = record.getFieldValue('fld-471ac88b92364a228d9fec90d9a5347b');
	
	if (!record_author) {
		var deviceToAuthor = {
			"pancake": "Sam",
		};

		var device = record.getFieldValue('fld-0bbeb9cfa1024c11a624ed63cc8200e0');
		
		record.setFieldValue('fld-471ac88b92364a228d9fec90d9a5347b', deviceToAuthor[device] ? deviceToAuthor[device] : device);
		document.saveAllChanges();
	}
}

Set_Record_Author();

• fld-471ac88b92364a228d9fec90d9a5347b is the ID of the Modified By Device field
• fld-0bbeb9cfa1024c11a624ed63cc8200e0 is the ID of the Record Author field

This creates a one time copy of the modified by field to our field though if you delete the contents of the text box it will reset. It also maps the device name (in my case my laptop is called pancake because it was thinner than my older laptop) to a human name as well but falls back to device name if there isn’t a match.

function Aggregate_By_Date() {
	// 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.
	var dateFormat = { "day": "2-digit", "month": "2-digit", "year": "2-digit"};
	
	// this is where we're going to store the rollups per day.
	var rollups = {};
	
	// iterate to all of the records in the form
	for (var rec of form.getRecords()) {
		var marketplace = rec.getFieldValue('fld-c163aba17ae64c4d93b5a53819a139dc');
		var purchase_date = rec.getFieldValue('fld-ccbd9a8f51d34246bebfb31aa4e397dd');
		var price = parseFloat(rec.getFieldValue('fld-08129d71ab0f4fa4a2749456281fca07'));

		// Skip entries that don't have a price or date set.
		if (!price || !purchase_date) {
			continue;
		}
		
		// format the date for use in the rollup.
		var formattedDate = purchase_date.toLocaleDateString("en-AU", dateFormat);

		// Rollup to this date, add to the existing value or set it if not set.
		if (!rollups[formattedDate]) {
			rollups[formattedDate] = { [marketplace]: price };
		} if (!rollups[formattedDate][marketplace]) {
			rollups[formattedDate][marketplace] = price;
		} else {
			rollups[formattedDate][marketplace] += price;
		}
	}
	
	// log to console the aggregated values.
	for (var month in rollups) {
		for (var marketplace in rollups[month]) {
			console.log(month + ", " + marketplace + ": $" + rollups[month][marketplace]);
		}
	}
}

Aggregate_By_Date();

First change is to get the extra field, I used marketplace since I was testing against my products document but replace it with the field ID we just created.

Second change is to how we do the rollup to add multiple dimensions (in this case, marketplace). For each dimension we need to check if it exists and create it if it doesn’t. If both date and marketplace are set, the else handles adding values together.

The last change is to the output loop to iterate over each day and each “marketplace”.

You’re using the script that doesn’t hit the rollup form but if you want to use it, you’ll need to add a new field to it and then where there is the addNewRecord line add an extra setFieldValue after it with the new field ID:

function Aggregate_By_Date() {	
	// this is where we're going to store the rollups per day.
	var rollups = {};
	//var marketplaces = new Set();
	
	// iterate to all of the records in the form
	for (var rec of form.getRecords()) {
		var marketplace = rec.getFieldValue('fld-c163aba17ae64c4d93b5a53819a139dc');
		var purchase_date = rec.getFieldValue('fld-ccbd9a8f51d34246bebfb31aa4e397dd');
		var price = parseFloat(rec.getFieldValue('fld-08129d71ab0f4fa4a2749456281fca07'));

		// Skip entries that don't have a price or date set.
		if (!price || !purchase_date) {
			continue;
		}
		
		// format the date for use in the rollup.
		var formattedDate = purchase_date.getFullYear() + "-" + purchase_date.getMonth() + "-" + purchase_date.getDay();
		
		//marketplaces.add(marketplace);

		
		// Rollup to this date, add to the existing value or set it if not set.
		if (!rollups[formattedDate]) {
			rollups[formattedDate] = { [marketplace]: price };
		} if (!rollups[formattedDate][marketplace]) {
			rollups[formattedDate][marketplace] = price;
		} else {
			rollups[formattedDate][marketplace] += price;
		}
	}
	
	// Get the rollups form.
	var rollupsForm = document.getFormNamed("Rollups");

	// Delete previous roll up records.
	rollupsForm.getRecords().forEach(rollupRec => rollupsForm.deleteRecord(rollupRec));
	
	var lines = [];
	
	// log to console the aggregated values.
	for (var month in rollups) {
		for (var marketplace in rollups[month]) {
			var line = month + ","  + marketplace + "," + rollups[month][marketplace];
			console.log(line);
			lines.push(line);

			var rollupRec = rollupsForm.addNewRecord();
			rollupRec.setFieldValue("fld-cd1d454672c84bce8103a4267507ca03", month);
			rollupRec.setFieldValue("fld-4c9f208bb5ed406489a54a76d4b6cd18", marketplace);
			rollupRec.setFieldValue("fld-9eeeff7120db401b830ccec4e06f2bc3", rollups[month][marketplace]);
		}
	}
	
	document.saveAllChanges();	

	Utils.copyTextToClipboard(lines.join("\n"));
}

Aggregate_By_Date();

[Guillermo q]

Sorry to bother you again guys.

With this code you gave me:

function Aggregate_By_Date() {
	// 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.
	var dateFormat = { "day": "2-digit", "month": "2-digit", "year": "2-digit"};
	
	// this is where we're going to store the rollups per day.
	var rollups = {};
	
	// iterate to all of the records in the form
	for (var rec of form.getRecords()) {
		var purchase_date = rec.getFieldValue('fld-ccbd9a8f51d34246bebfb31aa4e397dd');
		var price = parseFloat(rec.getFieldValue('fld-08129d71ab0f4fa4a2749456281fca07'));

		// Skip entries that don't have a price or date set.
		if (!price || !purchase_date) {
			continue;
		}
		
		// format the date for use in the rollup.
		var formattedDate = purchase_date.toLocaleDateString("en-AU", dateFormat);

		// Rollup to this date, add to the existing value or set it if not set.
		rollups[formattedDate] ? rollups[formattedDate] += price : rollups[formattedDate] = price;
	}
	
	// log to console the aggregated values.
	for (var month in rollups) {
		console.log(month + ": $" + rollups[month]);
	}
}

Aggregate_By_Date();

I have a field depending on who added this field (my wife or me). I would like this report by day, but with XX or XY depending on who added the field and the price.

Thank you very much, you helped me a lot.

[Sam Moffatt]

If you’re on iOS/iPadOS, I’d just copy the values out and paste into Numbers (Apple’s spreadsheeting tool) or Excel (free for phones, I think a charge exists on iPads). If you change the console output to use commas, it should paste fine.

A small change could also directly put this on your pasteboard:

	var lines = [];
	// log to console the aggregated values.
	for (var month in rollups) {
		var line = month + "," + rollups[month]
		console.log(line);
		lines.push(line);
	}

	Utils.copyTextToClipboard(lines.join("\n"));

The Mac version of Tap Forms has some charting functionality, I’m not entirely sure if it supports aggregation or not but that would be the first place I’d look. Supporting aggregation seems like a reasonable feature ask if it doesn’t exist but that can only be answered by the Keymaster.

If you’re on the Mac, then an alternative could be to just write it to a new form and then chart it. To do this is going to be a bit more of a rewrite so that we can sort things easier

	
function Aggregate_By_Date() {	
	// this is where we're going to store the rollups per day.
	var rollups = {};
	
	// iterate to all of the records in the form
	for (var rec of form.getRecords()) {
		var purchase_date = rec.getFieldValue('fld-ccbd9a8f51d34246bebfb31aa4e397dd');
		var price = parseFloat(rec.getFieldValue('fld-08129d71ab0f4fa4a2749456281fca07'));

		// Skip entries that don't have a price or date set.
		if (!price || !purchase_date) {
			continue;
		}
		
		// format the date for use in the rollup.
		var formattedDate = purchase_date.getFullYear() + "-" + purchase_date.getMonth() + "-" + purchase_date.getDay();

		// Rollup to this date, add to the existing value or set it if not set.
		rollups[formattedDate] ? rollups[formattedDate] += price : rollups[formattedDate] = price;
	}
	
	// Get the rollups form.
	var rollupsForm = document.getFormNamed("Rollups");

	// Delete previous roll up records.
	rollupsForm.getRecords().forEach(rollupRec => rollupsForm.deleteRecord(rollupRec));
	
	var lines = [];
	
	// log to console the aggregated values.
	for (var month in rollups) {
		var line = month + "," + rollups[month]
		console.log(line);
		lines.push(line);

		var rollupRec = rollupsForm.addNewRecord();
		rollupRec.setFieldValue("fld-cd1d454672c84bce8103a4267507ca03", month);
		rollupRec.setFieldValue("fld-9eeeff7120db401b830ccec4e06f2bc3", rollups[month]);
	}
	
	document.saveAllChanges();	

	Utils.copyTextToClipboard(lines.join("\n"));
}

Aggregate_By_Date();

Change to match the form name and field IDs but that should populate a new form with the records in the right format. Then you can use the chart feature on the Mac to visualise it.

Major changes in the full script, the formatted date is done by getting each value out:

		// format the date for use in the rollup.
		var formattedDate = purchase_date.getFullYear() + "-" + purchase_date.getMonth() + "-" + purchase_date.getDay();

This produces a format that is easier to sort on but not zero padded, doing so would require grabbing the sprintf implementation via the Script Manager so I’ve left it out for simplicity sake.

Next big change is our loop, I’ve incorporated the above pasteboard change but the core is as follows:

	// Get the rollups form.
	var rollupsForm = document.getFormNamed("Rollups");

	// Delete previous roll up records.
	rollupsForm.getRecords().forEach(rollupRec => rollupsForm.deleteRecord(rollupRec));
	
	var lines = [];
	
	// log to console the aggregated values.
	for (var month in rollups) {
		var line = month + "," + rollups[month]
		console.log(line);
		lines.push(line);

		var rollupRec = rollupsForm.addNewRecord();
		rollupRec.setFieldValue("fld-cd1d454672c84bce8103a4267507ca03", month);
		rollupRec.setFieldValue("fld-9eeeff7120db401b830ccec4e06f2bc3", rollups[month]);
	}
	
	document.saveAllChanges();	

We’re getting a form named “Rollups”, we’re going to clean it out each script run and then as we go through the rollups, we’ll add a new record and set a text field and a number field. We need to tell Tap Forms to save the changes as well which is the last line of that snippet. As with previously, replace the field ID’s with your own.

When doing this roll up, if in your main form you add a new script field (I called mine “Purchase Date Simplified”) and put this content in it:

function Purchase_Date_Simplified() {
		var purchase_date = record.getFieldValue('fld-ccbd9a8f51d34246bebfb31aa4e397dd');
		if (!purchase_date) {
			return;
		}
		
		// format the date for use in the rollup.
		return purchase_date.getFullYear() + "-" + purchase_date.getMonth() + "-" + purchase_date.getDay();
}

Purchase_Date_Simplified();

Then in your rollup form you can create a new “Link to Form” field, link it to the original form, then you set it to the JOIN type and you can match the text field to the script field. Then you can easily go from a roll up record to see which items were in the rollup.

[Mark Robbins]

I have a need to loop through each record in a table and run a script for that record. I wrote the script below, but it does not seem to work. I’m new to Tap Forms, and a bit new to javascript, so my understanding may be at fault. I know the called script works well when I run it from the Form window for a record, I just can’t get it to run against each record.

Thanks.

function Process_All_Records() {

var records = form.getRecords();
var field_bggid = “fld-ce86e98498894969839b4d574c754511”
var value_bggid = 0

for (thisRecord in records)
{
// go to the record and run the function;
form.selectRecord(records[thisRecord]);
console.log(“BGGid: ” + records[thisRecord].getFieldValue(field_bggid));
console.log(“Index: ” + thisRecord);
form.runScriptNamed(‘Fetch_Bgg_Information’);
}
}

Process_All_Records();

[Sam Moffatt]

You can do that by sorting by that date field, enabling “show group summaries” (this will also enable section headings) and then in your form settings, set the calculation to be total and the price field. Then you’ll get a summary at the bottom of each section. If you use the multicolumn list view, it’ll give you the option to display a calculations row and control per field aggregations as well.

In terms of a script, you’ll need to loop over all of the records in the form. Something simple like this could do it:

function Aggregate_By_Date() {
	// 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.
	var dateFormat = { "day": "2-digit", "month": "2-digit", "year": "2-digit"};
	
	// this is where we're going to store the rollups per day.
	var rollups = {};
	
	// iterate to all of the records in the form
	for (var rec of form.getRecords()) {
		var purchase_date = rec.getFieldValue('fld-ccbd9a8f51d34246bebfb31aa4e397dd');
		var price = parseFloat(rec.getFieldValue('fld-08129d71ab0f4fa4a2749456281fca07'));

		// Skip entries that don't have a price or date set.
		if (!price || !purchase_date) {
			continue;
		}
		
		// format the date for use in the rollup.
		var formattedDate = purchase_date.toLocaleDateString("en-AU", dateFormat);

		// Rollup to this date, add to the existing value or set it if not set.
		rollups[formattedDate] ? rollups[formattedDate] += price : rollups[formattedDate] = price;
	}
	
	// log to console the aggregated values.
	for (var month in rollups) {
		console.log(month + ": $" + rollups[month]);
	}
}

Aggregate_By_Date();

If you want to do more with the date formatting you’d have to pull out the fields from the date object. This will also be sorted in the order you retrieve them from the form which I think will be the default form ordering, so you’ll want to make sure that your first sort field is set to the date field to get the script output in the format you need.