Subtotaling 3 separate tables into 1 table?

asked on February 27, 2020

Hey there!

I've been looking around and haven't seen this asked so hopefully this isn't a repeat question. I have been working on an expense reimbursement form for my company. Currently, I have it set up so I have 3 different tables for people to enter their expenses to in case they need to be split or billed to multiple clients/jobs. I used the Laserfiche help section to get each table to have its own subtotal using JavaScript.

What I would like to do, however, is to add these subtotals for each row into a final review table that is separate from the other 3 tables.

I do have fields to give grand totals at the end, but it would be nice to have the breakdown in the separate categories as a final review for anyone submitting.

I am pretty new to JavaScript so I'm not even sure if something like this is even possible? Any help/guidance is appreciated!!

Thanks!

1 0

replied on February 27, 2020

Hi Kassie!

First off, to make things as easy as possible, make sure that each of your 3 tables have the categories on consistent rows. For example, make sure that Meals is always in row 1, Lodging is always in row 2, etc.

Next, you'll want to have the category totals as single line fields, each with their own formula following this structure:

=SUM(INDEX(Table_1.subtotal_column, 1), INDEX(Table_2.subtotal_column, 1), INDEX(Table_3.subtotal_column, 1))

In the above formula, the "1" is the row for that category, so if Meals was consistently in row 1, the above formula would return the sum of all three subtotals for Meals. You'll need to update this number for each of the categories depending on which row they are in (2 for Lodging, 3 for Taxi/Bus, etc).

2 0

View 5 previous replies

replied on February 27, 2020

So my tables are line up as I duplicated them so they would be consistent so this should work.

When using that formula, though, the field just shows 0 even though I have amounts in the table?

0 0

replied on February 27, 2020

Make sure that you're also re-naming the table and columns in the formula to match your table and column names.

1 0

replied on February 27, 2020

Hmm, yup, I've done that too.

=SUM(INDEX(exp_info_1.Subtotal_1, 1), INDEX(exp_info_2.Subtotal_2, 1), INDEX(exp_info_3.Subtotal_3, 1))

0 0

replied on February 27, 2020

Let's try something else.

Create a new table for your category totals with a fixed number of rows and one column. In the column's Field options, go to the Advanced tab and use this code:

=SUM(INDEX(exp_info_1.Subtotal_1, ROW()), INDEX((exp_info_2.Subtotal_2, ROW()), INDEX(exp_info_3.Subtotal_3, ROW()))

1 0

replied on February 27, 2020

Ah, thank you for your patience in helping me. It still is showing as 0 though with this code :( Does the tables being on separate tabs make a difference at all?

0 0

replied on February 27, 2020

No problem! We'll get to the bottom of this.

Try the following steps:

In the Forms Designer, click on the first user input table
Click Edit
Click "Field options" for the column that has the subtotal
Copy the variable field
Click on the category totals table
Click Edit
Click "Field options" for the column
Open the advanced tab

Paste the copied variable into this formula:

=SUM(INDEX(exp_info_1.PASTE-VARIABLE-HERE, ROW()))

Save and preview the form
Insert amounts into the first table and see if they show up in the category totals table.

This should work for getting subtotals to populate from the first table only. Let me know if this works and we can get the totals from all of the tables next.

1 0

SELECTED ANSWER

replied on February 27, 2020

Hi Kassie

Are you still using JavaScript to calculate the subtotal? it could be you have to add a line in your code for field.trigger.change To get Forms to recognize the value has been set in the field..
What you want to do can be done without any Javascript. It would likely be easiest if you perform the table subtotal calculations using the Formulas is the Field Advanced tab so you don’t have to deal with scripting.
In each tables Subtotal field you you first use the

=SUM(INDEX(tablename.fieldname1,ROW()), INDEX(tablename.fieldname2,ROW()), etc) for all of the fields.

3 0

replied on February 27, 2020

Ahhhh! That worked! It was the JavaScript, glad to know I don't have to use that.

Thank you both so much for the help!!

0 0

Question

Question

Subtotaling 3 separate tables into 1 table?

Answer

Replies

Sign in to reply to this post.