Search Results for 'script'

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

Thanks, Daniel.
Your examples helped me get through the record looping, and things appear to be working as desired now. I ended up incorporating the original Fetch_Bgg_Information instead of calling it with form.RunScriptNamed(). When I get time for additional testing, I might pull it back out.

[Daniel Leu]

I treat form.runScriptNamed() like an include in other languages to define constants and functions. So at the beginning of my script, I’d have form.runScriptNamed(‘Fetch_Bgg_Information’); but without any local function calls.

Then in the loop over records, you would call whatever function you have and provide the current record id as an argument. Maybe something like this:

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

And then in you Fetch_Bgg_Information script you would have the function

function Fetch_Bgg_Information(myRecordId){
   let myRecord = form.getRecordWithId(myRecordId);
   .....
}

Or you could just define let myRecord = records[thisRecord] in the loop and then access myRecord in your function Fetch_Bgg_Information() without using an argument. I prefer to use function call arguments, specially when the function is defined in a different script.

Cheers, Daniel

---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks

[Brendan]

Hi Mark,

What does Fetch_Bgg_Information() do?

Can you upload your form template?

The form.selectRecord(record) function is generally just used as sort of the last thing in a script to have Tap Forms select a specific record in the user interface. It’s like if you added a new record in the script and wanted Tap Forms to select that new record after. At least that’s what I had in mind when I wrote that function.

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

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

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

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]

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]

So you need to get the field values via record.getFieldValue, join it into a comma separated string and then use record.setFieldValue and document.saveAllChanges to set and save the new value.

Here is a script that takes a street, city and state fields to then join them into comma separated single line and then sets a location field and also a website field. You will need to change the ID’s to match your own ID’s. How do you find the ID of the field? It’s underneath the description field of the form editor or you can use the ID button in the script editor to also get the field ID or copy it from below the field list when selecting that field.

function Location_Copy() {
	var fields = ['fld-b5b69ac18a584b9998908bc82f42d7a5', // Street
					'fld-5943325d2ee6480b89a1d044b8695e6c', // City
					'fld-105b4e8304524a0bb7710c2b507f0590', // State
				];
	var location_id = 'fld-4d374af0f2644f798ea1e6c3c434290a'; // Location type field
	var google_maps_link_id = 'fld-db45b343c30f4410aa78c690ad92b2ff'; // Website type field

				
	var pieces = [];

	for (var field of fields) {
		var item = record.getFieldValue(field);
		item ? pieces.push(item) : null;
	}
	
	var joined = pieces.join(', ');
	console.log(joined);
	record.setFieldValue(location_id, joined);
	record.setFieldValue(google_maps_link_id, <code>https://www.google.com/maps/search/?api=1&query=${encodeURIComponent(joined)}</code>);

	document.saveAllChanges();
}

Location_Copy();

Ok to take this apart a little. This first bit sets a variable called fields that has the list of fields in the order you want them to show up in the address. For my example I did street, city and state following your ask but you could add another line for country with the field ID of your country field.

function Location_Copy() {
	var fields = ['fld-b5b69ac18a584b9998908bc82f42d7a5', // Street
					'fld-5943325d2ee6480b89a1d044b8695e6c', // City
					'fld-105b4e8304524a0bb7710c2b507f0590', // State
				];

These next two lines are setting the field ID’s of our target fields, the location field and a website field. Again, you can select your version of this field and click “ID” to get an output that looks like this. The // is a comment that I added manually.

	var location_id = 'fld-4d374af0f2644f798ea1e6c3c434290a'; // Location type field
	var google_maps_link_id = 'fld-db45b343c30f4410aa78c690ad92b2ff'; // Website type field

This next part creates a list of pieces and then uses the list of fields to be able to get the field values of each of the fields (e.g. street, city, state) and then check if they’re set to a value (thats the item ? line) and if they are set, push them into the array of items (pieces.push) or do nothing (the null). This means that if a field is empty, it won’t be added and you won’t get a blank comma for example. Also means that you can add more fields by just adding the ID’s in the right order and everything will still work:

	var pieces = [];

	for (var field of fields) {
		var item = record.getFieldValue(field);
		item ? pieces.push(item) : null;
	}

This is where we join the pieces together to create our comma separated list. I also log this for debugging because it’s useful to see what is going on.

	var joined = pieces.join(', ');
	console.log(joined);

We then set the field value (record.setFieldValue) of the location field and the website field with a Google Maps link. The location field can just be set with the simple joined value however the website link needs some extra data to hook it up to Google Maps. We need to save the changes which is what document.saveAllChanges() does for us.

	record.setFieldValue(location_id, joined);
	record.setFieldValue(google_maps_link_id, <code>https://www.google.com/maps/search/?api=1&query=${encodeURIComponent(joined)}</code>);

	document.saveAllChanges();
}

Lastly we need to tell Tap Forms to call the function we just created:

Location_Copy();

[Martin Furse]

Hi,

being someone who doesn’t know Java Script, I have been trying to copy three fields and place them into a fourth field, which is a different field to where the script will reside. As it is Street, Suburb and State that I want copied I also need spaces between the three fields, so when pasted into the “Map location Address” it will return the google map location of the address. I’ve attached the relevant part of the form to hopefully make it clear what I am trying to do. I couldn’t find my question in the forum, so if it has has been covered previously, I apologies in advance.

Cheers

Trusty

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

Time field stores time in seconds (maybe milliseconds?) of the time since midnight of epoch, it’s in a sense a date/time field with a fixed date. It’s likely not a great use case for you here if your aim is data export. It’d be usable but you’d need to do some post processing on it.

In terms of managing this, I’d just have two fields, one for the action name (using a combo box picklist probably) and a second number field for the time duration in minutes. That should let you fill in your own action name and then the number field being duration in minutes can be set to what ever value you want. In terms of automation, I’d go towards having a script field which then sets the number field to the known values. Essentially you need to map the string value you set to a number and at the moment this is likely the easiest way:

function Fill_Duration() {
	var action = record.getFieldValue('fld-4807f45c4fd9403c8beef5edce917c6d');
	switch(action) {
		case "Action A":
			record.setFieldValue('fld-4b64c978546045ab944ea4c959d89953', 5);
			break; 
		case "Action B":
			record.setFieldValue('fld-4b64c978546045ab944ea4c959d89953', 10);
			break;
		case "Action C":
			record.setFieldValue('fld-4b64c978546045ab944ea4c959d89953', 20);
			break;
	}
	document.saveAllChanges();
}

Fill_Duration();

In my sample form, fld-4807f45c4fd9403c8beef5edce917c6d is the “Action” text field with combobox pick list and fld-4b64c978546045ab944ea4c959d89953 is my number field. I’d hide the script field because it doesn’t need to be visible. Then when you export the records to CSV, you’ll have a field with the name and a field with the duration, both of which you can override with custom values.

I’ve attached a quick form I did handling this, you should be able to do the same by replacing the form ID’s with the ones for your fields (either visible in the field editor underneath the description field or you can get it in the script editor by clicking “ID”).

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

Make sure you tick “show inverse relationship” to also have a Link From Form field created in the linked form as well.

Once you have a JOIN field up and running using two common fields between the records, you can use a script to copy the links from the JOIN field to your other 1:M Link to Form field.

[Martin Kendall]

I did try that after Daniel posted that comment. However I can’t get the refresh to do anything (I’ve pulled down a long way and nothing happens – see attached screenshot)

However, I have quit TapForms while the saved search is active, which means it is on when I return to that table, and it doesn’t show the script added 4 or 5 stars.

I could send a cut down example database if that would help.

Thanks

Martin

Attachments:

You must be logged in to view attached files.