Question

How to use a formula for each row in a table

Forms

Updated April 27, 2022

asked on December 6, 2017

I have a table with a formula in the column. It's an if statement, so something along the lines of if(x="true","this happens","that happens"). The problem I'm running into is that if the user adds a row, the formula is brought down and ruins the return for that column in all rows. How do I make the formula only look at the specific row it is in?

1 0

Replies

replied on December 7, 2017

Hi David,

Chad's code should also work for your requirements. In case you're not too familiar with JavaScript, you could try to add some more depth to your field formula.

If you want to calculate a value for each row in your forms table, then you need to use the INDEX and ROW functions. Otherwise you get the behaviour that you're seeing now. Since you're also using the IF function, you will probably use it something like this:

=IF(INDEX(Table.Column_1,ROW())="YOURVALUE", 0, 1)

I've added the above formula to Column 4 in the example and when Column1 = "YOURVALUE" then it returns 0, otherwise is returns 1.

The next step is to ensure the formula only applies to the first row. We can either try tying it to static data in the table fields like the formula above that you know will not change or we can try to tell it to only refer to ROW()=1 which means row number 1. Here's the result below:

=IF(ROW()=1, 0, 1)

In this case, always return 0 when it's row number 1, otherwise return 1.

If you share some information about your table and variables, I can assist with writing out the formula for you.

Thanks

Sheldon

4 0

replied on April 27, 2022

Hi!

I am having problems figuring out where to place the ROW() part in the formula I have:

=SUM(IF(GisObjectIdTable.CoverType.PP1=TRUE,1,0),IF(GisObjectIdTable.CoverType.PP2=TRUE,2,0),IF(GisObjectIdTable.CoverType.PP3=TRUE,4,0),IF(GisObjectIdTable.CoverType.PP4=TRUE,8,0))

I want this formula to run for each row depending on the choices the user makes. This is what my table looks like:

The CoverTypeId will be hidden, but the way I have it set up right now it's doing this:

So the formula is working for the first row and then using that same row input for all rows instead of calculating just for that row.

Any help is welcome. Thank you!!!

0 0

replied on December 6, 2017 • Show version history

$('li.table').on('change', '.customclass input', YourFunctionName);

If you put a custom class on your input field within the table, you can use the above code to run a function and it will not run when rows are added, since the additional row fields did not exist on form load.

Or if you need to run through each row and work with multiple fields in the row, you could do a for each row with the condition that the iteration is 1, meaning don't loop.


//i will start at 1 and increase by 1 in each loop
var i = 1;
    $('.table tbody tr').each(function () {
             
          if(i == 1){
            //Your code here
              
             }
          
            //iterate i
            i += 1;
            });

This all assumes you set a custom class on your table called "table" which I like to do. You can use the system default class name: .cf-table-block

Now if you want to know exactly which row a user is in, and only work with the fields in that row, I have always wondered how to do this.

1 0

You are not allowed to follow up in this post.

Question

Question

How to use a formula for each row in a table

Replies

Sign in to reply to this post.