Extracting contents of a table field

[Victor Warner]

In a form I have a table field which contains a number of records.

I wish to export the contents of a number of the fields to a CSV (or XLSX) file. But the table field are not exportable.

Is possible to extract the contents of the table field into an another field through a script?

In the attached document the table field (called ‘Third party charges’) contains two records:

Legalisation agent (normal service) £24 1 £24.00
FCO – apostille (normal service) £30 1 £30.00

I would like to combine so they become in another field such as (for example):

1. FCO – apostille (normal service): £30 x 1 = £30.00 – 2. Legalisation agent (normal service) = £24 x 1= £24.00

Can a script do this and how would I do it?

Attachments:

You must be logged in to view attached files.

[Daniel Leu]

Hi Victor,

I created a new script field underneath the table and use following code:

function Third_Party_Charges_Csv() {

	// Fetch content from table
	var third_party_charges = record.getFieldValue('fld-021cfb3cc4f446ddbf5f9463c7b5d620');

	// Define field ids from table columns
	const item_id = 'fld-410bdf5d624a4651b8a803becd9c6578';
	const cost_per_item_id = 'fld-6cea4aa73ad44b6f91ba7f566e321790';
	const number_id = 'fld-95791fbc24bd4f3ca714756e5dd6a060';
	const cost_id = 'fld-a0be1b009c6a4a99a1784d36da134ee3';

	var txt = [];
	
	// Loop over all entries and fetch field content.
	for (var n = 0; n < third_party_charges.length; n++){
		var third_party_charge = third_party_charges[n];
		var item = third_party_charge.getFieldValue(item_id);
		var cost_per_item = third_party_charge.getFieldValue(cost_per_item_id);
		var number = third_party_charge.getFieldValue(number_id);
		var cost = third_party_charge.getFieldValue(cost_id);
	
		// Format field content as desired
		txt.push(n + ". " + item + ": £" + cost_per_item + " x " + number + " = £" + cost);
	}
	
	// Combine each formatted table row
	return txt.join(" - ");

}

Third_Party_Charges_Csv();

If you want to import this as a CSV into a spreadsheet, you might run into an issue with the ‘-‘ character. It is used as line separater and inside the FCO item.

Hope this helps!

[Victor Warner]

Danie,

Thank you very much!

Just one question. The output produces:

0. Legalisation agent (normal service): £24 x 1 = £24 – 1. FCO – apostille (normal service): £30 x 1 = £30

That is starting at ‘0’ rather than 1.

I assume the code is the following:

for (var n = 0; n < third_party_charges.length; n++){

but if I change this line to:

for (var n = 1; n < third_party_charges.length; n++){

the output is only:

1. FCO – apostille (normal service): £30 x 1 = £30

Is there a way to start the count at 1. and so both parts are outputted?

[Daniel Leu]

We can fix this with the output format:

txt.push((n+1) + ". " + item + ": £" + cost_per_item + " x " + number + " = £" + cost);

[Victor Warner]

Daniel,

Thank you again for your help.

[Daniel Leu]

Happy that it works for you, Victor!

[Author]

Posts