You are viewing limited content. For full access, please sign in.

Question

Question

Forms Collection Calculation Issues: Index/Row function NOT working

Laserfiche Cloud Forms
Updated July 26, 2021
asked on April 8, 2021

Hello, I'm having an issue getting Index/Row to work on my form.  I have a form with a collection and several fields in the collection.  I want to have one field that is calculated off the square feet input in that collection item. 

Here's my formula: =SUM(INDEX(Foundation_Slabs_Collection.Slab_Collection_SqFt,ROW()),1)

At this time I'm just trying to get it to work with a simple formula then I'll build it out from there.

Even this simple SUM function of adding one to my SqFt input value is giving me an error.

Both Fields are Number fields with 4 decimal places.  

I've added collection formulas using index/row functions several times before and never had issues so I have no idea what's going on.

This is on Cloud using the new Modern forms creation tool as well so don't know if there's some bug with the new update or something.

0 0

Replies

replied on April 9, 2021

Can you show more details about the fields and where the calculation is being performed? I just tested your formula using my fields and it worked fine. 

I have two fields in my collection "Number" and "Sq Ft + 1". The calculation for Sq Ft + 1 is

=SUM(INDEX(Collection_2.Number,ROW()),1)

 

 

0 0
View 12 previous replies
replied on April 9, 2021

Hey Jared, sure I'll provide some screenshots.

0 0
replied on April 9, 2021

Actually, it was working for me with just the one collection row, but once I added another one, I saw the error also. I'll bring it back to the team for investigation. 

0 0
replied on April 9, 2021

Okay thanks!  Although I can't currently get it to work with just one Index/Row so you made it further than me.

I'm not sure if this is relevant but the calculation field is within a section (and that section is within another section).

 

1 0
replied on April 12, 2021

Hey Jared, any update from the team on this?  Should I open up a ticket?

0 0
replied on April 12, 2021

I opened a bug already. We are investigating. 

0 0
replied on April 12, 2021

Okay thanks for the heads up!

0 0
replied on May 2, 2021

Hey Jared, any update on a fix for this bug?

0 0
replied on May 3, 2021 • Show version history

Yes, the bug has been fixed. It was pretty general about the INDEX formula breaking after adding a second row (316085). Please try your calculation again now that that's fixed. If you can still reproduce, let me know so we can investigate further. 

0 0
replied on July 26, 2021 • Show version history

I'm trying to do a similar thing but am still getting the error. To test it I created a collection with just one field and then tried to index that field but it didn't work.  The formula I'm using is =INDEX(EMS_WR_Cabinet.Amount,ROW()) . Am I using the formula incorrectly? 


0 0
replied on July 26, 2021

Is your hidden "Total" field inside the same table/collection as Amount?

Row() means "the row this field is in" so you can calculate the values within a given row INSIDE the table. For example, if you have a table with Columns A, B and C, you could put a formula inside column C saying

=INDEX(table.A,row()) + INDEX(table.B,row())

so that the value in column C would be A + B in each row. 

If you are outside the table, you don't need the row() function because you aren't in any given row. If you just want to add all the values in a column, just do 

=sum(table.C) 

which would give you the sum of all the values in column C of the table. 

0 0
replied on July 26, 2021

I tried both ways. 

Total outside of the collection:



Total inside the collection:

0 0
replied on July 26, 2021

What is your goal? To get the sum of all values in two columns? 

Outside of the table try

=SUM(EMS_WR_Cabinet.Amount) + SUM(EMS_WR_Cabinet.Amount_2)

Or is your goal to get some sub-totals in each row of the table? Your total inside the collection formula looks correct if that's inside a collection field. 

0 0
replied on July 26, 2021 • Show version history

This is for an inventory check. What I had done before on a different process was create tables and change the row name for each item, and then if the sum of the table was more than 0, the process goes to the restock list, only showing the items that need to be restocked.

With v. 11, we can't change the row names on tables anymore or use javascript to iterate through the tables to hide rows with empty cells, so I am trying to use a collection instead. Ideally, I want to have 4 columns: 2 custom HTML fields and 2 number fields like this: 



Then, if the total is more than 0 in the collection, the process would go onto a list of needed items. If the total is 0, then it would be considered fully stocked, and the process would end. 

I tried this outside of the table like you recommended but got another error:

0 0
replied on July 26, 2021

Are the Amount fields numbers or single lines? 

With numbers, this works for me

 

I also got it working with single lines

Check your variable names are correct. 

0 0
replied on July 26, 2021 • Show version history

They are both number fields:

I have been using the variable drop-down menu so I don't type in the variable and it's the only collection I have so far. But somehow it just started working right now out of nowhere. I didn't change anything though.

1 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...