Help with script

[Guillermo q]

Hello to all! I love this app, abd I hace discovered the scripts and this is amazing.

I would like to know if there is a way to do the following

– I have a form where every item has a date and a price. I have several items in every date.
– I would like an script that sums everything in every date and gives me a list of dates with total prices(example: 23-5-21 > 250; 22>5>21 > 100

Thank you very much

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

[Guillermo q]

Thank you for a so elaborated answer.
Sorry for this basic question: where is the show group summaries option?

Otherwise I will try the script. Thank you.

[Sam Moffatt]

On the Mac, it’s at the top of the record list next to the form name with a little menu sort of icon. On most views, it’s pretty close but the MCLV has it all the way to the right because of the extra stuff.

On iOS I don’t see a group summaries option, just the section headings. At that point I guess you’ll need to run the form script and check out the console log for the output.

[Guillermo q]

Thank you very much, the script works like charm!

Is there any possibilities to get a basic graph in that script??

Thank you very much.

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

[Guillermo q]

Thank you for your awesome work and help!! This is amazing, it worked!

I have another question. I have a field (price) that has a fixed value depending on the input of another field (group).
group 1= 20
group 2= 40
group 3 = 54

I tried with my absolute ignorance of programming creating a field with a script.

function pricetest() {

var group = record.getFieldValue('fld-9565e24e59c6412ca388144d04e0bd07');

if (group=1) {
	record.setFieldValue('fld-e59ea447a29b4c518bd307507e243f92', 20);
}

if (group=2) {
	record.setFieldValue('fld-e59ea447a29b4c518bd307507e243f92', 40);
}

if (group=3) {
	record.setFieldValue('fld-e59ea447a29b4c518bd307507e243f92', 54);
}

}

Pricetest();

Any help? Thank you so much.

• This reply was modified 3 years, 2 months ago by Guillermo q.

[Brendan]

Hi Guillermo,

After you set a field value, make sure you call form.saveAllChanges(); otherwise the values won’t be saved.

Also, the chart function has a Show group summaries option which will tell Tap Forms to plot the section group summary values instead of the values for every record. So that’s how you get your aggregates.

[Daniel Leu]

In Javascript, comparison is == not =. So you should use

if (group==1) {
   record.setFieldValue('fld-e59ea447a29b4c518bd307507e243f92', 20);
}

• This reply was modified 3 years, 2 months ago by Daniel Leu.

[Guillermo q]

Got it! thank you very much

• This reply was modified 3 years, 2 months ago by Guillermo q.

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

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]

Hey there. Me again. SOrry to bother you. I’ve been using this script since you wrote it.

I would like to add some data to the rollups, in order to manipulate the data in excel and make charts.

I would like to add 2 more variables to the rollups, so it would be like this:

Purchase date – price – person.

I Have declared the variable person, but i am lost with the rollups. Any help?

Thank you very much indeed =)

[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();

[Author]

Posts