Table row index number

[Stephen Abshire]

I have a script field that is contained within a table field. Since there are ‘n’ rows in this table and the script executes within each row how do I get the index number of the current row so that I can use it within the script?

[Sam Moffatt]

The challenge with both table fields and the many side of link to form fields is that the ordering can change and is dependent upon the sort settings in the UI. What I ended up doing with my order items form (line items in an order) is use a script to set the line number:

var title = record.getFieldValue('fld-39ca9564ef2347ac93f933bc9a2316ac');

var order = record.getFieldValue('fld-c3a6725d7d9446da92bbb880ffe90a9e');
var order_items = order.getFieldValue('fld-9db0c7698499435ab6b18b7eb420e0ae');

var quantity = record.getFieldValue('fld-39379cdff743496f9a1ccbdc1ae56297');
var line_number = record.getFieldValue('fld-f95b68d488cb4b058bbf3de84e1a7c3b');

var note = record.getFieldValue('fld-d0cfab9ec09d497294fbd8b5b52caf16');

if (!line_number)
{
	record.setFieldValue('fld-f95b68d488cb4b058bbf3de84e1a7c3b', order_items.length);
	console.log('Setting line number to ' + order_items.length);
}

This generally works ok, it’s not perfect, but sets a field in the form that I can then refer to later. I use this in particular to ensure that I can always get the correct order of line items for display.

[Stephen Abshire]

Thanks for the reply you are quite helpful. While I do have a development background TapForms and JavaScript are new to me. Let me try with a photo so it may help out a bit.

Looking at the attached image with a table field named ‘Timeline’ I want to do the following:

Cost = Minutes * Hourly Rate (not pictured)

The ‘Timeline’ field seems to be an array of records (rows) and what I need to figure out how to do is get the array index for the currently selected row. JavaScript I believe has zero-based arrays so the index for the current row would be something like Timeline[7]. What I don’t know how to do is get the array index for the current row so that I can get the values to do the math. A crude pseudo-code may be like this:

Timeline[index].Cost = Timeline[index].Minutes * Hourly_Rate

So how do I determine what ‘index’ is or is there a different way to do this?

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

Ok, so I had to read this a few times and I’m still not sure I understand correctly but I did do some testing. I think you’re trying to calculate a cost based on the value in the table field and a value in a field outside of the table field?

I tried figuring out how to do this myself with a script field and couldn’t get it to work. I can sort of do it in a calculation field but only with fields from within the table. I might be missing something obvious, maybe I need to look later.

If you’re willing to restructure a little, I think you can do what you want a little easier with a Link to Form field though the script/calculation field in the table should be able to do that.

[Brendan]

Hi Stephen,

I don’t think you need to index into this. Is your goal to do that simple multiplication, then all you need to do is add a Calculation field to your form that multiplies your Minutes field by your Hourly_Rate field.

You can also do it in a script:

function Cost() {
	var minutes_id = 'fld-abcd1234...';
	var hourly_rate_id = 'fld-1234abcd...';

	var minutes = record.getFieldValue(minutes_id);
	var hourly_rate = record.getFieldValue(hourly_rate_id);

	return minutes * hourly_rate;
}

Cost();

The value of record will be the current record in the Table field.

[Stephen Abshire]

Greetings,

Short Version: Is there a compelling performance reason to use a linked form over a table field when the ‘many’ side of the join is typically about 5 records?

Thanks Sam and your proposal to use a new form instead of a table field is something I have considered and still question a bit I suppose. For my usage in this particular database the ‘many’ side will typically be at most 15 but more likely around 5 records. For example, I have a job form (photography) and a contact form. A contact may belong to multiple jobs but have different roles. So let’s say ‘Jane’ is a client (bride) in one of my jobs and she liked my photos so much that she recommended me to a friend. I book her friend ‘Kathy’ who in turn asks ‘Jane’ to be her maid of honor. So now ‘Jane’ is attached to two jobs (weddings) but with different roles.

At this point I have a ‘Roles’ form that joins the ‘Job’ and ‘Contacts’ forms together which allows me to use a contact on multiple jobs but assign different roles. Should I use a table field I could eliminate the ‘Roles’ join form since I have so few records on the ‘many’ side. The bonus to this is that I don’t have to navigate to another form for only a few fields of data. Yes this data can be shown in the table that is created on the ‘Job’ form showing the data in the linked form, by why not just cut out the middleman in this case and just use a table field directly? The downside is a very small amount of data will be replicated (client info), but the tradeoff may be work it to me.

[Sam Moffatt]

I think the challenge is with a table field, the calculation field only displays the fields from the record context. If you try to copy a field placeholder from a parent calculation field into the table calculation field then it doesn’t save right. If you create a script field in the table, when you insert the fields in the editor it includes the index format (e.g. table_field_name[index]) and record in that scope is still the main record, getFieldValue for fields in the parent form work but don’t work for fields in the current row of the table.

At least in my testing, I couldn’t figure out how to get it to behave correctly.

[Sam Moffatt]

From what I understand, I think internally there isn’t much difference between a table field and a link to form field in terms of representation. Table fields are essentially sub forms and each table row has it’s own record. It doesn’t have an explicit form in the form list and there are limitations on the fields inside the table but otherwise it’s the same characteristics.

[Stephen Abshire]

Greetings Sam,

Yes like you said I am unable to use record.GetFieldValue() to grab the value of a field in the current row of a table but it works to grab values from the parent form. Perhaps Brendan can shed some light on that for both of us?

[Brendan]

So there’s something different that happens for Table field sub-fields and sub-records when running a script. When you run the script in the Script Editor, record actually does refer to the parent form’s currently selected record.

However, when you modify a sub-field value for a sub-record within a Table field in which you have a Script Field, Tap Forms will use the correct Table field’s record in place of the record reference in the script.

This works because when you type in a value into a sub-field of your Table field, Tap Forms knows what that currently selected record is. The JSContext is updated with the currently selected Table field sub-record and the script is evaluated.

Hope that clears it up.

Sorry I missed seeing the reply to this message.

[Sam Moffatt]

That means there is no way to get a value from a parent record from the table field?

I’ve been playing with script fields inside table fields and they’re a little quirky. Does the recalculate formulas button properly refresh script/calc fields inside a table? It doesn’t seem like they do. It also took a couple of saves before my script field would properly update to reflect changes.

If the answer to the first question is yes, then a script like this will resync them:

var hourly_rate = record.getFieldValue('fld-47aef2ed3cda40d18ff896959a31062c');
var table = record.getFieldValue('fld-30d04e6103ad4ba8ac1a2149fbfde064');

for(target of table)
{
	target.setFieldValue('fld-b7aca38a1e83483bbe94c7b6cf850f18', hourly_rate);
}

This is a little heavy because it runs every time a change is made to either field (or for the table, any column of any row) but it does work.

[Brendan]

I’ve just exposed the parentRecord property on the TFFormEntry class so you can get access to that for Table fields.

[Author]

Posts