How do I use a field outside a tables in a calculation field inside a table?

[Calion]

I’m building a recipe database, and want to be able to change the number of Servings and have the Ingredient quantities update accordingly. The ingredients and ingredient quantities are in a Table; the Servings field is outside of the table. How do I access the field outside the table in the calculation field inside the table?

[Daniel Leu]

I don’t know if this is possible, but you could do this calculation in a script.

[Sam Moffatt]

I concur with Daniel that you can’t do this with a calculation inside the table but you can do it in a script outside of the table. I tried quickly trying to get a script field to work in a table but I couldn’t get it to execute at all.

Doing it outside of the table you just need to get the value of your servings field, get the records from the table and then iterate through them to set a total amount field that multiplies the servings by each rows per serving amount.

var servings = record.getFieldValue('fld-5cb7ea19fa144112be17f4fb52699102');
var table = record.getFieldValue('fld-b9927271feeb4fe2b9c0cd32f36c7cd1')
for(line of table) {
	var per_serving_amount = line.getFieldValue('fld-3fead523555c48f9a77377fd340e59f6');
	line.setFieldValue('fld-e561de624cb1448a83399ab94a1b9e00', per_serving_amount * servings);
}

document.saveAllChanges();

As a script field that did the trick for me, replace your field names to match the servings side, the table field IDd, the per serving amount and the total serving required field (or how ever you name it).

[Brendan]

Also, on a script you can get record.parentRecord to get the parent record for that child record. It will give you back a record object the same as record which you can then call getFieldValue() on.

[Daniel Leu]

record.parentRecord: Ah, another hidden feature. Cool! Could this be documented, and the editor isn’t aware of it either. Thanks!

But there is an issue: It looks like the return value of a field script isn’t propagated to the table script field. Here is my test code:

function ScaledValue() {
	const quantity_id = 'fld-xxx'
	let quantity = record.parentRecord.getFieldValue(quantity_id)
	return quantity

}

ScaledValue()

[Sam Moffatt]

I’m seeing the same thing, the results just aren’t showing up in the table. Here’s my test script:

"Hello, World";

Works fine in the script editor but never shows up in the table. I do believe we’ve found ourselves a bug!

Quick idea: have the script editor show the parent fields like a link to form style relation but instead automatically inserting the record.parentRecord.getFieldValue as the code completion.

[Author]

Posts