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

Question

Question

LF Forms-Expense Reimbursement Template Calculation Errors

Forms Business Processes Capture How To
Updated February 22, 2023
asked on January 19, 2023

Hello,

I am having issues getting the calculations to work correctly on the Expense Reimbursement Template in forms.  I want to have the expenses auto populate based on the selection in the "purpose" drop down menu, but when I add a new row it is adding the corresponding value to the new row. The same happens if I set a fixed number of rows, the value of the first selection is auto population into all of the amount fields simultaneously. I know I am probably going to have to work out some kinks with the mileage row as well so any advice on that would be helpful.

Thank you!

Expense Reimbursement Template Calculation Error.JPG
Expense Reimbursement Template Calculation Error 2.JPG
Expense Reimbursement Template Calculation Error 3.JPG
Expense Reimbursement Template Calculation Error.JPG (66.82 KB)
Download
Expense Reimbursement Template Calculation Error 2.JPG (35.55 KB)
Download
Expense Reimbursement Template Calculation Error 3.JPG (64.69 KB)
Download
0 0

Answer

SELECTED ANSWER
replied on February 15, 2023

The "This field has a calculation" error is one indication.

I can't really say what is happening without knowing how the calculation looks, but I would say to make sure of the following things:

  • The syntax is correct (use the documentation linked in the calc menu)
  • The INDEX functions are being used properly

 

For example, in the following screenshot you have an INDEX function out by itself next to the MULT function.

If the intention was to get the Purpose variable to use in the multiplication, then it should be inside the MULT

=MULT(INDEX(Out_of_Service_Area_Fees.Purpose,ROW()), ..

 

And in the following screenshot, you have an extra "(" and you have content inside of the ROW function which shouldn't be there.

ROW() should be empty because it returns the numeric value of the "current" row in the table, which can be used in the INDEX function like so

=INDEX(Table.Variable,ROW())

This is the exact syntax you should be using every time you reference a table variable the way you're doing, and each variable needs to be wrapped in its own INDEX function.

For example,

=MULT(INDEX(Table.Variable1,ROW()),INDEX(Table.Variable2,ROW()))

Be very mindful of the opening and closing parentheses.

It can help to build your function out in pieces, then add each piece together rather than trying to write it all in one go and risk missing/misplaced parentheses that break everything.

For example,

Start with

=MULT(x,y)

write the functions to grab each individual variable

INDEX(Table.Variable1,ROW())

INDEX(Table.Variable2,ROW())

Then replace your placeholders with those complete functions so you don't have to worry as much about counting all the parentheses, you just have to make sure each "piece" is correct and in the right spot.

0 0

Replies

replied on January 19, 2023

What are you using to auto-populate the value? That's the most likely thing that needs adjustment, but I didn't see that part in the screenshots.

0 0
View 11 previous replies
replied on January 19, 2023 • Show version history

Hi Jason,

Forgot to include that, here it is!

0 0
replied on January 19, 2023

Okay so the problem there is that the variable is referencing the entire column, not the associated row.

In order to pull only from the associated row, you need to include the INDEX() function in your calculation.

Like so,

=INDEX(Expenses.Purpose_1,ROW())

The Index function is how you get a specific value from a multi-value variable like the ones in a table or collection, and the ROW() function tells the calculation to reference the same row as the specific field.

 

However, there's a big catch here. Calculations cannot be combined with free form input, so if you want users to be able to change/override those amounts, then you'll need a different approach.

Basically, a calculation will fire/update anytime a change event occurs, so if you pick from a dropdown, the value is populated, you change the value, then another change is triggered, it'll revert to the value from the calculation.

If you don't want users to change the calculated value, then make the field read-only, but if you do, then things get more complicated.

1 0
replied on January 19, 2023

Thank you!!

0 0
replied on January 24, 2023

@████████ So I am getting stuck on another part of this, I made a separate table for the mileage calculation and I've tried every combination I can think of (including MULT and PRODUCT) to get the mileage to multiply by the mileage allowance (0.625) and I cannot get it to calculate. Thanks in advance!

0 0
replied on January 25, 2023

If this is in a table, you still need the Index function to calculate per-row values.

0 0
replied on February 14, 2023

Hi @████████,

So we are kind of back at square one with these calculations again. The project end user is wanting the fields separated out into different sections but this has posed a problem for the calculations again. I am running into that issue with the mileage table still and I am wondering if that is because the values are typed in rather than selected from a dropdown?

I am also having an issue with a section (Out of Service Area Fees) where they want to be able to select a dropdown with a corresponding value and then type in the number of days. This would then need to be multiplied by the  corresponding values in the dropdown to get the total amount. Please see screenshots for more info.

02-14-23-Mileage Section.png
02-14-23-Out of Service Area Fees.png
02-14-23-Mileage Section.png (12.59 KB)
Download
02-14-23-Out of Service Area Fees.png (27.23 KB)
Download
0 0
replied on February 15, 2023

Hi @████████

I'd need more information. For example, what do your calculations look like, and how is it not working (i.e., wrong value, not updating at all, etc.)

0 0
replied on February 15, 2023

Hi Jason,

I tried many variations yesterday and was unable to figure it out. I know I had it working partially before my end user requested all of the changes but unfortunately I forgot to save the calculations that were working before I made the changes. This is what I left off on.

https://cnmelectric-my.sharepoint.com/:v:/g/personal/kristina_padilla_cnmec_org/EZuOuLQ2AEJEhsIgUby0rdcB_dPXFwwXEt0WtpN_MQY4dw?e=gLBOWb

0 0
SELECTED ANSWER
replied on February 15, 2023

The "This field has a calculation" error is one indication.

I can't really say what is happening without knowing how the calculation looks, but I would say to make sure of the following things:

  • The syntax is correct (use the documentation linked in the calc menu)
  • The INDEX functions are being used properly

 

For example, in the following screenshot you have an INDEX function out by itself next to the MULT function.

If the intention was to get the Purpose variable to use in the multiplication, then it should be inside the MULT

=MULT(INDEX(Out_of_Service_Area_Fees.Purpose,ROW()), ..

 

And in the following screenshot, you have an extra "(" and you have content inside of the ROW function which shouldn't be there.

ROW() should be empty because it returns the numeric value of the "current" row in the table, which can be used in the INDEX function like so

=INDEX(Table.Variable,ROW())

This is the exact syntax you should be using every time you reference a table variable the way you're doing, and each variable needs to be wrapped in its own INDEX function.

For example,

=MULT(INDEX(Table.Variable1,ROW()),INDEX(Table.Variable2,ROW()))

Be very mindful of the opening and closing parentheses.

It can help to build your function out in pieces, then add each piece together rather than trying to write it all in one go and risk missing/misplaced parentheses that break everything.

For example,

Start with

=MULT(x,y)

write the functions to grab each individual variable

INDEX(Table.Variable1,ROW())

INDEX(Table.Variable2,ROW())

Then replace your placeholders with those complete functions so you don't have to worry as much about counting all the parentheses, you just have to make sure each "piece" is correct and in the right spot.

0 0
replied on February 15, 2023

Oh my gosh thank you so much, this makes a lot more sense now! I was finally able to get the calculations to work properly and I'm sure I will be able to apply this many times in the future! 

Many thanks,

Kristina

0 0
replied on February 20, 2023

Hello, @████████

I am struggling with yet another calculation. Just when I thought I was beginning to understand, I've been hit with another curveball that's making me question my sanity. 

My end user is wanting me to have the insurance totals in a table, subtracted from the total reimbursement requested. It seems like it would be fairly straightforward so I tried this:  =SUB(Total_Reimbursement_Requested,Less_Insurance_.Total_Amounts).

When that didn't work I tried this: =SUB((SUM(Mileage_Table.Amount____,Monthly_Fee_In_Service_Area.Amount_Monthly_Fee_In_Service_Area,Outofpocketexpenses_PersonalCreditCardexpenses.Amount____,Out_of_Service_Area_Fees.Amount, Outofpocketexpenses_PersonalCreditCardexpenses.Amount),Less_Insurance_.Total_Amounts)

When that didn't work I tried: =SUB(Total_Reimbursement_Requested,INDEX(Less_Insurance_.Total_Amounts,column_4())

I've tried so many combinations now that I can't remember but I still am unable to get it to work.

Screenshot 2023-02-20 144616.png
Screenshot 2023-02-20 144616.png (46.21 KB)
Download
0 0
replied on February 20, 2023 • Show version history

@████████

Build it out one step at a time. Also, keep in mind that when you're looking for assistance, the "how" of it not working is always important.

For example, are you getting a syntax error in the designer? Are you getting an error when testing the form? Are you getting no results or unexpected results?

The answers to those kinds of questions are critical for troubleshooting.

 

Note that you should only use INDEX when you want a specific row. I'm not sure where the "column_4()" bit came from, but that part of the index function should only ever be one of the following:

  1. ROW() when using it within a table
  2. a hard-coded number 
  3. a variable or calculation that return a single number value 

 

However, in this case you don't want/need that at all. 

If you want the sum of a column it should just be SUM(Table.Column). I strongly encourage looking through the documentation on the formulas to get a better idea of what is required/allowed for each type of calculation.

Like I said before, build it in pieces because it is going to be a nightmare to try to figure out which part isn't working if you build it all at once.

Start at the "bottom" and get your sums first. Once you have your sums sorted out you can start building those working pieces into a larger calculation.

0 0
replied on February 22, 2023

Hi @████████

 

I was finally able to figure out the calculation needed was 

=SUB(SUM(Total_Reimbursement_Requested),(SUM(Less_Insurance_.Total_Amounts)))

 

I have been using https://go.laserfiche.com/support/webhelp/Laserfiche/10/en-US/administration/#../Subsystems/Forms/Content/FieldCalculations.htm

as a reference  to write calculations but I am not finding it that helpful. Is there another resource that I can use? I searched the support and answers site but didn't find anything.

Thank you,

Kristina

Screenshot 2023-02-22 092512.png
Screenshot 2023-02-22 092512.png (56.7 KB)
Download
0 0
replied on February 22, 2023

Hi @████████

The documentation is probably the most detail you're going to get anywhere. Forms uses OpenFormula, so the overall syntax is pretty much the same as what you would find in other programs like MS Excel.

The key is understanding the Forms variables and the underlying data types to get an idea of what calculations apply to different scenarios.

The most important thing is to note that Table/Collection variables are always multi-valued even if there's only one row.

For example,

A standalone number variable would return a single value like 5

But a number in a table/collection returns a list like [5,4,3] or [5]

So, regardless of how many values your table/collection variable has, it must always be treated differently than a field outside of a table/collection.

 

With that in mind, if the documentation says a calculation accepts a collection/list, you can use the variable as-is (i.e., in the SUM calculation)

However, if the calculation expects single values (i.e., the SUB function), then you need to use the appropriate calculation to get a single value (for example, using the INDEX function to get a row-specific value, or using SUM to get the total for a particular column).

Your final calculation works because the SUM functions are taking each list and combining them into single values that the SUB function can accept.

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

Sign in to reply to this post.

Details
Attachments
Related Posts
Loading ...