Sort and add a record in “Table” field

[iorbita]

Hello,
I think there is a problem with “Table” field and how new records are added.
If sort is ascending (A to Z) it’s logical for me that new recording must be added AFTER the existing one and not before, and viceversa, if sort is descending (Z to A) new recording must be added BEFORE the existing one.

Here a short screencast to illustrate the problem: https://filedn.com/lV1GJHCzFN9yUtwWXQpOHiY/forum/video/tapforms-table-field.mp4

Also, how to add a new record between two existing record? In any table editor you select the record and you can choose whether to add a new record before or after the existing one, in TapForms just impossible … did I forget to select any option?

Thank you,

Lorenzo

[Sam Moffatt]

I think your issue is that the table is sorting the record when you add it by the sorted field. An empty field when sorted ascending is before a non-empty field. In descending mode, the empty field is sorted last.

Sorting the table field inherently will change the order, the table fields themselves don’t have any real inherent order. One trick I use to ensure consistent ordering is to add a field for it and then I sort on that field. For me I have an “Orders” form and a “Order Items” Link to Form (behaves similar to tables) and within “Order Items” is a “Line Number”. I sort then by “Line Number” and it appears in that order. Then when doing that, to insert something in the middle (that doesn’t have some other sort property), you set the numbering to be in the middle though you have to update the other records to increment their value.

If when sorted the record you want to put in the middle will naturally go in the middle, after updating the table it should go where it needs to be. You might need to ask Tap Forms to refresh the UI for you by manually toggling the sort order or hit the refresh/”recalculate formulas” button at the bottom of the record. However if your sort order would put it in the middle, then you can rely on that behaviour.

[Brendan]

You can also add a Date Created field to you form and sort by that so Tap Forms always sorts it based on when the records were created.

[iorbita]

Hello
first of all thank you both for your answers ;)

I haven’t used TapForms for a long time, so if I remember correctly, to “populate” tables and have the possibility to manage their sort, they must necessarily be linked to another form? So I think my mistake was to enter data directly into table…

Thank you again,

Lorenzo

[Brendan]

Hi Lorenzo,

Are you using a Table field? If so, that’s the only way you can enter data into it. Directly that is. With a Link to Form field you can enter data into it direct or you can navigate into the child record and add it there just like a regular form… because it is. Just linked.

[iorbita]

Hello again…

Are you using a Table field?

Yes I’m using Table field

I found a solution on how to insert data directly into table: I create a “number” field with an automatic incrementation and I hide it, then sort is done on number field… however I don’t understand how to insert new data between two records…
Sam suggested adding a number between two records and then incrementing (manually?) the following records… this can be fine for few records, but it must be painful to do when you have several records.
Is it possible to automatically increment them ?
Thank you,

Lorenzo

Off Topic: how is “Table” field displayed on mobile application?

[Sam Moffatt]

You need to make the number field visible and change the value of it to be a number between the two records you want to put it between so that the sorting works properly.

[Brendan]

If you’re using an Auto-Increment field, you could have it increment by 10 or 50 or whatever. But then you’d have to temporarily disable the auto-increment function to insert a record in between two different records. Then turn it on again.

[iorbita]

ok, thank you both ;)

[Sam Moffatt]

I have a script I use that I think I’ve posted elsewhere for the ordering of child records, that means you can edit the ordering field relatively straightforward. It’s a reply to a similar sort of thread. I’ll reply here if I find it again.

[iorbita]

…thank you :)

[Sam Moffatt]

I found the post that I was thinking of but it was a generic counter implementation.

It’s a little more involved, here’s a simpler script I use with a link to form use case.

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

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

This is a little tricky, order is the parent record (the Link from Form field) , order_items are all of the children of the parent (a Link to Form field in the parent reecord) and then line_number is the line number. I could probably change it to use the max function that was added but for the most part this works for me. As Brendan suggested you could add in a multiplier (e.g. order_items.length * 10) to give yourself some space around the numbers.

The order and line_number field equivalents will be accessible via the script editor directly and for order_items I opened up the order form and got it’s field ID from there.

You should be able to modify it to work with a table field as well though I’ve personally never done much with table fields and scripts. There is something special about the scripting environment inside of a table though a quick glance at the documentation doesn’t reveal it. Perhaps Brendan can chime in on this one.

[iorbita]

…the world of scripting and therefore programming is not yet part of my skills, I started to learn javascript recently, it will be a good exercise, thank you anyway ;)

Lorenzo

[Sam Moffatt]

This one isn’t too bad to get started on, when ever anyone has told me they want to learn programming my first question is what is the project or problem they want to solve. You’ve already got a small problem to solve which is a great place to start. I will suggest if possible maybe switching over from table to a link to form if you don’t have too much data because I know the link to form works but the table I haven’t used as much. Apart from having an extra form, it can behave more or less identically.

Just to expand on the script, let me go line by line since it’s a relatively small script. This is a script field inside the new form and make sure you set up a Link to Form field from the existing form to your new form and also make sure the Link to Form field has ‘show inverse relationship’ ticked.

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

This first line is there to get a reference to the parent record (your current form right now). This is the piece I’m not so certain about how you get in the table field hence the suggestion to use a new form and Link to Form/Link from Form. In my case I have an Order form and then an Order Items form. For your situation you’ll need to change the fld-c3a6725d7d9446da92bbb880ffe90a9e to match the Link From Form field. To do that in the script editor for the script field, double click on the form header in the left panel.

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

This is the Link To Form field and to get the value of this you’ll need to be in the equivalent of your Orders form (your current parent form). If you don’t have an existing script field to use, I generally create a new form script (third tab on the right panel), open up the editor and then get the ID I need. In this case you’re looking for the section heading with the name of your new form, in my case ‘Order Item’ and double click on that to get field ID to splice in above.

In this situation, order_items returns a set of records linked to the parent record via the field. This essentially is all of the sibling records of the currently selected record.

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

This is the new field in the new form that stores essentially your offsets. You can get this in the script editor by double clicking on the field name and it should insert this or a similar line with the correct field ID.

if (!line_number)
{

This is a check to see if the line_number is not set on this record already. This just makes sure once it has a value set, it doesn’t overwrite it again.

	record.setFieldValue('fld-f95b68d488cb4b058bbf3de84e1a7c3b', order_items.length);

This line sets the value of the line_number field (notice the ID is the same) to the number of records currently linked to the parent (order_items.length). When this is working properly, each time a new record is created, the number of items will be incremented automatically. It’s not 100% perfect but it’s a good enough approximation that doesn’t require scanning all records to find the current maximum and set it to be higher than that.

	console.log('Setting line number to ' + order_items.length);
}

I like lots of logging and I put in a log message just to see what is going on and we need to close out the if statement block hence the }.

Hopefully this helps, a little more detail on what this script means and how to make it happen for yourself. Again I recommend switching over to a second form and a Link to Form field because I’m not sure how well this works with table fields.

Good luck! Scripting in Tap Forms unlocks a large number of capabilities and gives you a large amount of power to customise your workflows.

[iorbita]

…thank you very much Sam, I really appreciate the time you have spent to explain me your script, I’m going to analyze it, try to understand it and reproduce what you explained in a new TapForms file in order to link a form to table.

Thank you again Sam ;)

Lorenzo

[Author]

Posts