Question

Forms 10.2.1 Tables assign 1 to null values

Forms Version 10

Updated April 3, 2018

asked on December 15, 2017 • Show version history

I came across a little something that appears like a bug and wanted to get some feedback on it.

Below are 2 tables in layout view:

Both tables are multiplying the Number column by 2 and putting the result in the Output Test/Multiply columns. It appears that when the field is null, Forms considers it to be 1 rather than 0.

In the top table (Test if Zero), I placed a logical test in the Output Test column to see if it is null or not. If it is not null, then perform the multiplication:

I looked around the answers site and found some something similar where a less than 0 evaluation was taking place. I tried the below formula and it kills the formula:

Here is the output which is concerning. Below is how the form starts out with a default value of 0 in the Number column:

When I clear out the default value I get the following:

Has anyone else seen this? There are easy workarounds such as the logical test above and making the field required, but they are still workarounds.

Thanks!

1 0

Answer

SELECTED ANSWER

replied on December 18, 2017 • Show version history

Thanks Chris, I can reproduce the issue now.

But this is not a bug, it is expected behavior: PRODUCT function would ignore variable with empty value and only product those non-empty values. The behavior is same as how it works in Excel, see https://support.office.com/en-us/article/PRODUCT-function-8e6b5b24-90ee-4650-aeec-80982a0512ce

You can keep the current workaround if you want to change the behavior. Sorry for the confusion.

1 0

Replies

replied on December 17, 2017

Hi Chris,

For fields inside table, the variable testTable.testNum is an array, not a single value. If you want to compare number with number, you need to replace testTable.testNum in the second formula with INDEX(testTable_1.testNum, ROW()), which is used to get testNum on the same row as the output field.

I tried on 10.2.1.246 and it seemed that formula array<number would always be true whatever the value is, which was a bug. However that could not explain why it showed 2 in your case. Can you give me the actual screenshot of the formula in the second table? For your existing screenshots, formulas in both tables are using the same variable which does not make sense.

1 0

View 2 previous replies

replied on December 18, 2017 • Show version history

Thank Rui,

The lower table in the pic above (No Test if Zero) is using a simple PRODUCT formula:

In both the tables I am only doing as simple multiply by 2. In the Test if Zero table, I am using the SUM formula to determine if the field in null or not. As I said previously, the < or > operators do not work to evaluate the null value.

As a reference, here is the Test if Zero formula:

Since this post, I have been using the INDEX formula to get the actual value out of the table cell. I have found that I must still test if the value is null, otherwise the formula places a 1 in the cell. This looks like a bug to me. make sure that once you place a number in the Number (testNum/noTestNum) field, you then clear it out with a null value. Watch how the formula behaves.

Thanks

1 0

SELECTED ANSWER

replied on December 18, 2017 • Show version history

Thanks Chris, I can reproduce the issue now.

You can keep the current workaround if you want to change the behavior. Sorry for the confusion.

1 0

replied on December 19, 2017

Thanks Rui. Do you have any better suggestions than my workaround to accomplish the same thing? An easy solution is to make the field required, but in some cases that won't work. Thanks!

0 0

replied on December 19, 2017

I think your SUM function for testing is zero is quite good. Why would you need other suggestions?

0 0

replied on December 20, 2017

LOL, I don't know what I don't know :) Thought I would ask. Thanks for your help, very much :)

0 0

replied on April 3, 2018 • Show version history

This issue that formula array<number would always be true whatever the value is has been fixed at Forms 10.3.1, and you can download newest package at https://support.laserfiche.com/news/3882/laserfiche-10-3-1-released.

0 0

You are not allowed to follow up in this post.

Question

Question

Forms 10.2.1 Tables assign 1 to null values

Answer

Replies

Sign in to reply to this post.