Calculations within a collection.

asked on October 7, 2024

Hello,

So I've been struggling with an issue, and I'm not sure if it's a bug, something that has to be done with JavaScript, or something I'm not doing properly(more than likely this is the issue).

I have a form where I gather a users first and last name inside of a collection from a lookup table. I then want to combine both the first and last name from that collection and add it to a list of anyone that might be listed in any subsequent additions to that collection.

I'm using a calculation on the "First and Last name" fields, but what I'm finding is that all of the "First name" fields get listed together and all of the "Last name" fields get listed together, and they are not getting listed like "First name, Last name", "First name, Last name".

Here are the calculations I'm trying to use to get the first and last names together:

=CONCATENATE(Index(Involved_Parties_Information.First_Name_1,ROW()), ", ", Index(Involved_Parties_Information.Last_Name_1,ROW()))

I'm pretty sure that the "calculations" I'm trying to use are correct as I've done similar things to this before and they work, but I'm either missing something or not understanding how those functions work, or they're not supposed to work that way inside of a collection. If there's only one set in the collection the calculation works just fine, but if there are multiple collections then that's where the issue arises.

A Picture of the Collection for reference.

Any help would be greatly appreciated.

0 0

replied on October 7, 2024

Where is your calculation field? In your screenshot I can see your source fields in the collection, but it's not clear where the calculation is happening.

As a note, the ROW() function works in the context of a field's parent table/collection, so your calculation should work if it inside the same collection, but if it is a non-repeatable field outside of the collection there is no "row" value to reference.

0 0

replied on October 7, 2024 • Show version history

The Calculation field is a multi Line field on another page of the form. It's just needed to gather the info from the 2 fields in the collection and bring it to a hidden field on the last page of the form so I can send the form to the people in that multi line field.

0 0

replied on October 7, 2024 • Show version history

In that case you'll need a different approach.

It is important to note that calculations work from the context of the field where they are set, not the source. As a result, there is no "row" value that can be referenced using ROW().

If you're using INDEX outside of a table/collection, you have to reference an explicit row number rather than using ROW().

For example, you could do something like INDEX(Collection.Field,1), but you would run into other issues with that because you don't have a static number of rows to work with.

Alternatively, ROW() would work if you had the values in another table or collection, but then you have the similar challenge of having to ensure the secondary table has the same number of rows as the first.

My suggestion would be to do the calculation inside the table with a hidden column; this way you have a preformatted list to work with rather than trying to match up two columns from outside the table.

Add a (hidden) column inside the table, and the calculation you already have will work to produce a "First, Last" name for each.

Then, you can use the JOIN function/calculation in your multi-line to display that list with whatever delimiter you choose.

For example, the following would show all values of the column separated by a line break character, which can be added with CHAR(10).

=JOIN(CHAR(10), Collection.Full_Name)

The end result should be something like this

Alternatively, if you're using Workflow for anything, you could also handle it there using a For Each Row to build the list in multi-value token.

0 0

Question

Question

Calculations within a collection.

Replies

Sign in to reply to this post.