Pulling multiple data stored in one table, for use in another

[scott johnson]

So what I’m looking to do is re-build an old formulation database i built years ago in Access – where i’d have a large ingredient table with hundreds of ingredients (sugar, fats, etc – and each one of those had all their nutritional attributes stored in that same record).

There was a second table then, which had a number of drop down fields that linked back to the ingredient table – allowing me to select each individual ingredient for use in the formulation. I’ve been able to get this far in TapForms on my own.

Where I’m stuck is here: When i pull – say – sugar – from the drop down list that’s linked back to the ingredient table – what i’d also like it to do is pull and display the information for, say, carbohydrates – that is associated with the sugar i just choose. Why? Because I will then use that in a series of calculated fields to determine the quantity of carbohydrates in the precise formulation i’m working on, and get real time updates. This is what’s got me stuck. I’m not a scripter – so that’s out of my depth. Anything obvious I’m missing to help me do this?

Thanks folks
SJ

[Brendan]

Hi Scott,

Well, you could either use a Link to Form field from your Recipe to your Ingredients form and just choose sugar as one of the ingredients. Tap Forms will then display all of the fields you’ve defined for your Ingredients form.

But if you need to change quantities of the ingredients per recipe, then I would recommend using a Table field instead of a Link to Form field. You would still have a separate Ingredients form that contains your Sugar value along with its attributes (e.g. carbohydrates), but then you would link that to your Table field that you put on your Recipes form. Then you would be able to select an ingredient from the Ingredients form and have the values copied to the Table field. This way you would then be able to add in a separate Quantity field onto your Table field which would then let you do a calculation that’s specific to that recipe.

It’s very similar to my Invoice Tracking example where you have an Order form and a Products form. The Order form has an Order Items Table field on it which has a bunch of things that it gets from the Products form. But it also has a Quantity field which you can type into and then Tap Forms will automatically update the total for you, but just for that order.

Here’s a link to the post that has the Invoice Tracking form you can download:

Invoice Tracking Template

I hope this wasn’t too confusing. Take a look at the Invoice Tracking form to see how that’s all put together.

Thanks!

Brendan

[scott johnson]

Thanks Brendan – I’ll def take a look at it today and see if i can make it work. I really appreciate the tip!

[scott johnson]

So it’s close – using either method, I can get it to pull in relevant ingredient info and display it, and calculate the total – but i can’t determine how to get the inserted calculated field to divide the user entered ingredient quantity (top red circle), by the system generated total (bottom red circle) – to calculate the % of the ingredient… thots on how i might accomplish this?

Attachments:

You must be logged in to view attached files.

[Brendan]

You would need to use a Script field for that and ask the form for the total of the field from the Link to Form field.

You would need to loop through all of the records of the Table field and total up the value of that column. This would be done whenever the value in your Quantity field changes.

[Sam Moffatt]

Script fields in tables seem bugged but I did this in a test and it worked in the script editor:

function Percentage() {
	var per_serving_amount = record.getFieldValue('fld-3fead523555c48f9a77377fd340e59f6');
	var total = record.parentRecord.getTotalOfLinkedFieldForField("fld-b9927271feeb4fe2b9c0cd32f36c7cd1", 'fld-3fead523555c48f9a77377fd340e59f6');
	return (per_serving_amount / total * 100);
}

Percentage();

Where per_serving_amount or fld-3fead523555c48f9a77377fd340e59f6 is a field inside my table and where fld-b9927271feeb4fe2b9c0cd32f36c7cd1 is the field ID of the table in the main form. It gives me the right value in the script editor but it feels like script fields are not being properly evaluated in table fields.

Alternatively if it was possible to project parent fields into the table calculations, I created a calculation field in the parent record with the total of the field int he table however I couldn’t readily get to it via a calculation in the table.

[scott johnson]

Oof – that may be exceeding my capabilities. I really appreciate the insight – let me play with it a bit today and see if a non-computer guy can figgure out scripting 8-)

[Sam Moffatt]

Well we think the script field execution in a table row is also bugged right now. I can get it to trigger when I edit a related field in the same row but it doesn’t recalc everything and when I run the recalc formulas button at the bottom of the record. So you end up with the situation where all of the other rows are wrong until you click into the record and hit enter to trigger the script field for that row.

The alternative that works consistently is to create a script field in the form that handles recalculating. I haven’t been able to get it to trigger consistently either but when I click “recalc formulas” it at least updates consistently for me.

Here’s the script:

function Populate_Percentage() {
	let total = record.getTotalOfLinkedFieldForField('fld-b9927271feeb4fe2b9c0cd32f36c7cd1', 'fld-3fead523555c48f9a77377fd340e59f6');
	let table = record.getFieldValue('fld-b9927271feeb4fe2b9c0cd32f36c7cd1');
	
	for (let index in table) {
		var per_serving_amount = table[index].getFieldValue('fld-3fead523555c48f9a77377fd340e59f6');
		table[index].setFieldValue('fld-9048ea3263164a739908f8add07b5ed7', per_serving_amount / total);
	}
	document.saveAllChanges();
}

Populate_Percentage();

Some definitions:

• fld-b9927271feeb4fe2b9c0cd32f36c7cd1 is the ID of the table field.
• fld-3fead523555c48f9a77377fd340e59f6 is the ID of the per_serving_amount field or for you this would be your quantity field.
• fld-9048ea3263164a739908f8add07b5ed7 is the ID of the percentage field or your ‘% of recipe’ field.

You should be able to replace the IDs with the ones from your form and it should more or less work.

• Decomposing the script, the let total line gets the total from the table field of the field in the table field.
• The let table line gets all of the records in the table field.
• The for line is the start of a loop that goes through all of the records.
• The var per_serving_amount gets the value of the per serving field from the table row number index.
• The setFieldValue is setting our percentage field for that row (the table[index]).
• The document.saveAllChanges() is required to tell Tap Forms you made a change and you want to keep it.
• Finally the Populate_Percentage() line triggers everything.

This should work if you create a new script field in your form, ensure your percentage field is a number field and then you update the ID’s to match. It does mean to update you will need to click on the “Recalculate formulas” button at the bottom of your record. You can hide the script field so that it doesn’t show up in your default layout as well.

[scott johnson]

Thank you Sam – i appreciate the effort you’ve shown on behalf of a complete stranger!

[Sam Moffatt]

No worries, hopefully it helps you and it may help someone else in the future who is looking for a similar answer.

[Author]

Posts