Split note field?

[Emil Mitchell]

Dear all,

I am rather new when it comes to technical database terms and I am hoping someone can help.

I have a note field with approximately 5 lines of text. I have come to the conclusion that it would be better to have each piece of text within the note field within its own field.

Is there a way to split the information within a field into separate fields using some technical scripting magic, rather than having to go back and edit them all manually?

Thank you for your time!

[Brendan]

Hi Emil,

This can be done with a script.

Here’s an example that takes the lines from a Note field and splits them into an array and then loops through the array of lines and writes each line to a separate field. I’ve made some assumptions here such as your field names have a pattern of “Field 1”, “Field 2”, “Field 3”, and so on.

function Split_Note() {

	var note_id = 'fld-c711358a858443c8805c8c98c71a03b4';
	var note = record.getFieldValue(note_id);
	var field_prefix = "Field ";
	
	if (note) {	
		var lines = note.split("\n");
		console.log(lines.length);
		var fieldIndex = 1;
		
		for (line of lines) {
			let field = form.getFieldNamed(field_prefix + fieldIndex);
			if (field) {
				console.log(field.name);
				let field_id = field.getId();
				record.setFieldValue(field_id, line);
			}
			fieldIndex++;
		}
	}
	form.saveAllChanges();
}

Split_Note();

You can see the result of running this script in my example screenshot.

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

On a similar note, I do something similar except the data I generally pull in is free form and I use a table field with a “Key” and “Value” fields inside it. It’s a similar sort of script except that you use fixed name for the key/value field names and an extra table field. There is an example of using a link to form/table fields and addNewRecordToField in the Get Movie Details from TheMovieDB API. If you know the keys are going to be unique, I also have a helper script on GitHub that scans a table field (also works with link to form fields) and expects a key/value field structure to then add only entries that are missing.

[Emil Mitchell]

Thank you very much for your replies. That is great that such a script can be formatted. It will save a lot of time.

I have attached an example of how my data is formatted within the note field in question.

The individual parts of the data that I would like to split into their own field are marked “Field 1” to “Field 16”.

As you can see, some parts of the data are separated by commas, some are not, and some are separated by being on the next line.

Is it still possible to split the note?

Thank you!

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

Without actual data it’s a little hard to give you a solution but something like this should make sense.

let input = `Field 1
Field 2
Field 3, Field 4 Field 5
Field 6, Field 7, Field 8
Field 9 Field 10

Field 11
Field 12
Field 13

Field 14
Field 15
Field 16`;

let lines = input.split("\n");
let fields = [];

fields[1] = lines[0];
fields[2] = lines[1];

let line2Pieces = lines[2].match("([^,]*), (Field .*) (Field .*)");
fields[3] = line2Pieces[1];
fields[4] = line2Pieces[2];
fields[5] = line2Pieces[3];

let line3Pieces = lines[3].split(",");
fields[6] = line3Pieces[0];
fields[7] = line3Pieces[1];
fields[8] = line3Pieces[2];

let line4Pieces = lines[4].match("(Field [0-9]*) (Field [0-9]*)");
fields[9] = line4Pieces[1];
fields[10] = line4Pieces[2];

fields[11] = lines[6];
fields[12] = lines[7];
fields[13] = lines[8];

fields[14] = lines[10];
fields[15] = lines[11];
fields[16] = lines[12];

console.log(fields);

I’ve used an array to store the various fields in to show how that should look parsed out. Instead of using the array (e.g. fields[1] = ...) you’d would directly set the field value (e.g. record.setFieldValue("fld-field1id", ...)).

At a high level we split things by line, then for the lines that are purely comma separated we split them by the comma and then for the lines that are a mix, I’m using a regular expression to extract out the pieces. This is where knowing the actual form of the data would help because you can build a regular expression to capture the format.

For example a US formatted location could be parsed this way:
console.log("Cupertino, CA 95014".match(/([^,]*), (.*) ([0-9]*)/));

[Emil Mitchell]

Thanks for the detailed explanation. Below is an example of the type of data:

17.08.1943
61 Squadron
Hamburg, Shot down by a nightfighter (100%)
Lancaster1, SN.ZX334, BA-
SqnLdr. Hewitt

1) RAF Bomber Losses

1) no comment.

Each of the above entries relates to a particular aircraft and its accidents, loss throughout its career, etc. Some records only contain one incident, but others can have up to 5 accidents and then a final loss. Some incidents have full details as per the entry above, but others may have less information depending on what details are known. So an example of a multiple entry field would look like this:

02.08.1942
12 Squadron
RAF Hawkinge, tire burst on landing (20%)
Lancaster1, SN.ZX334
SqnLdr. Conran

1) RAF Bomber Accidents

1) no comment.

02.08.1942
12 Squadron
RAF Detling, dmage while on sortie (50%)
Lancaster1, SN.ZX334, BA-
SqnLdr. Conran

1) RAF Bomber Damage

1) no comment.

17.08.1943
61 Squadron
Hamburg, Shot down by a nightfighter (100%)
Lancaster1, SN.ZX334, BA-
SqnLdr. Hewitt

1) RAF Bomber Losses

1) no comment.

[Sam Moffatt]

Ok, so if you’ve got multiple entries then I’d suggest creating a second form for the data and using a link to form field to store that data in or a table field. This allows you to model the multiple entries but link it back to the main record.

This script is a little longer than the last. I’ve put comments throughout it. You’ll need to fill in the blanks for the field ID’s which you can find via the script editor by clicking on the field and hitting ID or by looking at it’s ID in the field details panel.

The way I’ve constructed it is something that could be optimised but I use this technique to verify the object that is constructed looks “right” and then I add in the piece to addNewRecordToField and setFieldValue at the end which actually creates the records.

Here’s the script:

// fieldNames is a list of field names for each line.
const fieldNames = [
	'event_date',
	'squadron',
	'location',
	'aircraft',
	'pilot',
	'damage_type',
	'comments'
];

// aircraft details has the fields for the aircraft line.
const aircraftDetails = ['aircraft_type', 'aircraft_sn', 'aircraft_ext'];

// fieldMap maps the friendly field name to the Tap Forms field name.
// these are the fields IDs from the link to form or table field
const fieldMap = {
	"event_date": "fld-001",
	"squadron": "fld-002",
	"damage_location": "fld-003",
	"damage_description": "fld-004",
	"damage_percentage": "fld-005",
	"aircraft_type": "fld-006",
	"aircraft_sn": "fld-007",
	"aircraft_ext": "fld-008",
	"pilot_rank": "fld-009",
	"pilot_name": "fld-010",
	"damage_type": "fld-011",
	"comments": "fld-012"
};

// input is our sample data, in your script:
// let input = record.getFieldValue('fld-notefieldid');
let input = `02.08.1942
12 Squadron
RAF Hawkinge, tire burst on landing (20%)
Lancaster1, SN.ZX334
SqnLdr. Conran

1) RAF Bomber Accidents

1) no comment.

02.08.1942
12 Squadron
RAF Detling, dmage while on sortie (50%)
Lancaster1, SN.ZX334, BA-
SqnLdr. Conran

1) RAF Bomber Damage

1) no comment.

17.08.1943
61 Squadron
Hamburg, Shot down by a nightfighter (100%)
Lancaster1, SN.ZX334, BA-
SqnLdr. Hewitt

1) RAF Bomber Losses

1) no comment.`;

// lineIndex the index of the line we're processing.
let lineIndex = 0;

// parsedData is an object we're going to put the data in temporarily.
let parsedData = {};

// lines is each of the lines
let lines = input.split("\n");

// process each line...
for (let line of lines) {
	// trim the line of extra whitespace
	line = line.trim();

	// ignore empty lines.
	if (line.length == 0) {
		continue;
	}

	// handle each line we're working on
	switch (lineIndex) { 
		// these lines pass thru the value 1:1
		case 0:
		case 1:
		case 5:
		case 6:
			parsedData[fieldNames[lineIndex]] = line;
			break;
		case 2:
			// damage line is field1, field2 (field3) so let's parse that:
			let damagePieces = line.match(/([^,]*), (.*) \((.*)\)/);
			if (damagePieces && damagePieces.length > 2) {
				parsedData["damage_location"] = damagePieces[1];
				parsedData["damage_description"] = damagePieces[2];
				parsedData["damage_percentage"] = damagePieces[3];
			}
			break;
		case 3:
			// aircraft line appears to have two or three entries so we split
			let aircraftPieces = line.split(',').map(elem => elem.trim());
			// then we work through each piece of the split line
			for (let aircraftIndex in aircraftPieces) {
				// and put it in the relevant field
				parsedData[aircraftDetails[aircraftIndex]] = aircraftPieces[aircraftIndex];
			}
			break;
		case 4:
			// pilot appears to be a simple space split field with two values.
			let pilotPieces = line.split(' ').map(elem => elem.trim());
			parsedData["pilot_rank"] = pilotPieces[0];
			parsedData["pilot_name"] = pilotPieces[1];
			break;
		default:
			console.log("Unknown Line");
			console.log(lineIndex);
			console.log(line);
	}

	// increment lineIndex to change which line we think we're processing
	lineIndex++;

	// if lineIndex becomes 7, we're at the end of this stanza
	if (lineIndex > 6) {
		// reset the lineIndex back to zero
		lineIndex = 0;

		// log out our parsed object.
		console.log(JSON.stringify(parsedData));

		// create a new entry in our link to form field
		// remove the /* and */ lines after testing
		/*
		let newEntry = record.addNewRecordToField("fld-linktoformfieldid");
		for (let fieldName in parsedData) {
			// set the field value of the field to the value we parsed.
			newEntry.setFieldValue(fieldMap[fieldName]), parsedData[fieldName]);
		}
		*/
		// reset the parsedData object to empty.
		parsedData = {};
	}
}

// save all the changes we made!
document.saveAllChanges();

For the sample data, the console log looks something like this:

{"event_date":"02.08.1942","squadron":"12 Squadron","damage_location":"RAF Hawkinge","damage_description":"tire burst on landing","damage_percentage":"20%","aircraft_type":"Lancaster1","aircraft_sn":"SN.ZX334","pilot_rank":"SqnLdr.","pilot_name":"Conran","damage_type":"1) RAF Bomber Accidents","comments":"1) no comment."}
{"event_date":"02.08.1942","squadron":"12 Squadron","damage_location":"RAF Detling","damage_description":"dmage while on sortie","damage_percentage":"50%","aircraft_type":"Lancaster1","aircraft_sn":"SN.ZX334","aircraft_ext":"BA-","pilot_rank":"SqnLdr.","pilot_name":"Conran","damage_type":"1) RAF Bomber Damage","comments":"1) no comment."}
{"event_date":"17.08.1943","squadron":"61 Squadron","damage_location":"Hamburg","damage_description":"Shot down by a nightfighter","damage_percentage":"100%","aircraft_type":"Lancaster1","aircraft_sn":"SN.ZX334","aircraft_ext":"BA-","pilot_rank":"SqnLdr.","pilot_name":"Hewitt","damage_type":"1) RAF Bomber Losses","comments":"1) no comment."}

To get this to work, fill in the field ID’s (e.g. fld- prefixed items) and remove the /* and */ lines to actually create a new field in the link to form field. Might need some testing but you can see roughly what it’s doing.

[Emil Mitchell]

Hello Sam,

Thank you very much for your comprehensive answer and time. I appreciate it!

Kind regards,

Emil

[Sam Moffatt]

I turned this into a video using the above sample data and script as a basis including building out two forms to track the data. I’m going to do a follow up video on how to handle the multiline fields which isn’t currently handled for next week.

[Brendan]

Another awesome video Sam! I’ve been watching it in-between trying to figure out some SwiftUI stuff. Apple makes it look so easy, but when you get down to the nitty gritty, I don’t know if it’s any quicker than UIKit.

[Sam Moffatt]

Thanks! I did one more follow up covering handling multiple lines as well.

Apple and Swift is awkward, they’re pushing it but it’s got a lot of issues. I hated how they nerfed iOS widgets for complications on the watch and hope they’ll improve that later.

[Emil Mitchell]

Gents, you are magicians! Thank you!

[Sam Moffatt]

Also uploading the archives. I need to get better at doing this as well.

The TFF is a Tap Form Form Template, import it via File -> Import -> Form Template in a document. Form Templates are just the form definition, fields and form scripts without the data.

The TFARC is a Tap Forms Archive, import it via File -> Import -> Tap Forms Archive in a document. A TFARC has all of the same data as the form template but also includes the records, in this case the sample data used in the example here.

Attachments:

You must be logged in to view attached files.

[Author]

Posts