Question

Forms Calculations '{0}' is an invalid formula expression

Forms

Updated November 7, 2024

asked on February 9, 2023

We are getting the following error in a calculation. The system is so concerned with this that it will not even allow the users to submit the form.

Field Name: Total Amount, Position: 45, Error: '{0}' is an invalid formula expression. [LFF1000-InvalidExpression] [LFF1018-ErrorOccuredDuringFormulaCalculation]

Here is our expression, there is no position 45 and no text {0} entered into the expression. It seems everything about this message is inaccurate

=SUM(Expenses_Breakdown.Expense_SubTot)

The other odd thing about this is that the value shown in the field Total Amount is always correct. The calculation appears to be working just fine as far as we can see, only after submission are we told we have to return to the form and fix the expression.

1 0

Answer

APPROVED ANSWER

replied on November 7, 2024 • Show version history

Hi, this issue has been fixed on Forms 12: no error on user task submission after delete row from previous step when read-only formula uses fields not in previous step. (BugID: 187687)

You can see other changes from: Laserfiche 12 Changelog

Get Laserfiche Forms 12 package from: Laserfiche 12 - Downloads

1 0

Replies

replied on April 6, 2023

By any chance is your Expense_SubTot field a text field instead of numeric or currency? I experienced the same issue when using a Single Line field in a calculation. Mine was as follows:

=SUM(ti_cost_ttl/unit_rsf,0)

It dawned on me that unit_rsf was a single line field being filled by a lookup with a number, and I was finally able to successfully submit the form once I changed it to

=SUM(ti_cost_ttl/VALUE(unit_rsf),0)

Props to my support vendor for assisting with the VALUE casting!

2 0

View 5 previous replies

replied on April 6, 2023

Was it displaying the correct result even before you casted it to a string?

In our case the field is displaying an accurate result, you just can not submit the form.

0 0

replied on April 6, 2023

Yes, the displayed calculation on the form itself was fine before the cast, it only threw an error on submission. I was so frustrated! But once I changed the formula, both the display and the submission are good.

0 0

replied on April 6, 2023

Thanks for this info, it could very well be the same thing. This is a problem I often have when using the calculation language.

0 0

replied on April 12, 2023

In our case all the fields were currency fields, so it might be a different scenario. This is why the lack of clarity in what '{0}' means is the real root of the problem here.

2 0

replied on April 14, 2023

Looks like this can also happen if you put an empty variable into a SUM function, IE a field that no longer exists on the form. It will still calculate correctly but throw the error.

1 0

replied on April 14, 2023

If you have SUM functions that can calculate on fields that do not always exist IE Tables with 0 rows, always add a 0 to the end. It makes life easier.

=SUM(Table.Field, Table2.Field,0)

2 0

replied on April 25, 2023 • Show version history

Any updates on this thing here? I have the same situation happening after installing Update 3.

FYI casting my collection field (which is a number value) to a VALUE also did not work here.

=SUM(VALUE(MedBillable.Bill_Amt_2), 0)

UPDATE:
Ok, I actually got this 'un-stuck'.

(During off business hours),

Copy the problem form.
Remove the problematic field OR formula from the form. (just temporarily)
It could then submit (no more recover form error), so I routed it to a new temporary user task with the copied form. Basically, I 'intercepted' the form.
- Here I added the problematic field back in and confirmed the value was correct. This copied form on the new user step COULD submit fine.
Added the problem field back to the original form.
Routed it back to the original step.
I routed it back and forth a few time to confirm the user would be able to do the same.
End up on the original user assigned to the original task.
Removed all temporary routing and user steps.

This does seem like a bug to me. Also, I know the above steps are a total pain......but the process is flowing again. Hope this helps someone.

0 0

replied on July 10, 2023 • Show version history

I just ran into this same error while working on a process for a customer. Found a similar post from 2018 where the issue was reproduced when the source of the calculation was summing from a table where rows were deleted, and the destination of the calculation was marked readonly, and the issue went away when the field was instead made readonly using javascript. The form I was getting that error on, met all of those same criteria, so I tried unchecking readonly on my destination field and making it readonly in javascript instead, and indeed, the error also went away for me.

2 0

replied on February 9, 2023

Hi Chad,

I cannot reproduce this issue with Forms 11 Update 3. It looks like Expenses_Breakdown.Expense_SubTot is a field inside Table/Collection, and you are getting to sum of all the rows values. I'd recommend you open a support case and provide more information:

1. The exact version of Forms

2. The business process to reproduce, better with a simple test process

3. The event viewer logs when error occurs

1 0

View 4 previous replies

replied on February 10, 2023

A support case for writing a calculation?

Forms version is 11.0.2212.30907

I tried my best to create a simple test process to reproduce it by writing down everything they were doing in the existing process and re-creating only the specifics, it did not throw the same error though so I had no luck there. The process it is happening in is complex but the calculation is not complex.

Looked at the event viewer but do not see anything, I am pretty sure that notifications of calculations not working is not an event viewer thing.

This does happen across multiple production forms, I personally am just unable to create a simple example no matter what I try.

The only constant we know is that it requires the user delete a row from the collection. However the calculation is still accurate after they do this and the number still reflects the total.

0 0

replied on February 13, 2023

Well, I still cannot reproduce your issue after some attempts, and you said it does not happen for all processes, and require "the user delete a row from the collection", there may be some special settings that leads to your issue, so it's better to open a support case and let Support Engineer help you identify what is exactly the cause. Another reason is, Answers site is not allowed to upload bp files, so it's hard to narrow the issue either.

0 0

replied on February 14, 2023

What does it mean by '{0}' is an invalid formula expression?

I thought the expression was the instructions typed into the calculation field, but {0} does not exist in the instructions.

0 0

replied on February 15, 2023

Yes, the {0} here is kind of confusing as it does not tell exactly which field fails to be calculated, it could be a potential bug. Can you open a support case with the process you used to reproduce and record a video what steps are taken to reproduce?

0 0

replied on February 21, 2023

We are having an identical issue as well. Forms version 11.0.2201.20436. Was the root cause of the issue discovered?

1 0

replied on February 21, 2023

Hi Isaac,

If you find it an identical issue, it's better to open a support case as soon as possible.

0 0

replied on September 15, 2023

Did this bug get fixed? We are using Laserfiche Forms Professional Version 11.0.2307.40547 and just received a report from a user saying they are getting the error. She provided a screenshots and based on what I saw, a row was deleted from the table.

1 0

replied on July 3, 2023

What were the results from the support case? Just curious.

0 0

replied on January 18, 2024

We upgraded to one of the versions listed above a few months ago, and have found many forms no longer work as they used to for years before.

Encountered this error today, with a SUM field that was grabbing data based upon whether a person checked boxes - this populated currency fields with a value (or zero if unchecked). The fields were passed to an approval form later in the process, where the approver could "override" and change any values before they were SUM'd in the totals field that gave the error.

I used both ABS and VALUE to try and fix this, and also added the ",0" as a last value to the SUM. None of those worked.

The solution was to remove the "read only" characteristic to the SUM field. So now they can edit a line that should not be edited... and I can only put a text value above the field saying "please do not edit the field below" which is not an elegant solution.

This is why I hate software updates... things work a certain way for a very long time, and developers tweak how functions behave or how rules work... and break all kinds of things. If it's not BROKEN, don't mess with it... or is this how one creates work to justify their job??? lol

0 0

View 1 previous reply

replied on June 26, 2024

I too am experiencing the same issue. I'll be opening a support ticket.

0 0

replied on June 26, 2024

This has been a problem for me since 2017. I always have to disable the field with CSS to ensure any variable that is crucial saves correctly.

1 0

replied on November 4, 2024

I encountered the same issue, but thanks to your findings, I was able to find a workaround.

I unchecked the read-only option and then created a rule to disable the field always. 🙃

1 0

You are not allowed to follow up in this post.

Question

Question

Forms Calculations '{0}' is an invalid formula expression

Answer

Replies

Sign in to reply to this post.