Value saved is different from what is shown in form

[KG360]

My field "PerspCount" shows the right value, and the formula delivers the correct result but saves a value that is different. More specifically, it omits the addition from [@cbParamVirtual62] below. Does anyone who sees this post have a clue? For example, "PerspCount" show 4 in the form and then 3 is saved to the table.

PerspCount (Count the number of distinct perspectives in the target table)
(SELECT COUNT (DISTINCT Perspective) FROM tbl32_OrgTargets WHERE Corp_FK=target.[@field:Corp_FK]) + [@cbParamVirtual62]

Virtual 62 (Checks if a distinctly new perspective is added)

CASE WHEN (SELECT COUNT (DISTINCT Perspective) FROM tbl32_OrgTargets WHERE Corp_FK=target.[@field:Corp_FK] AND Perspective = [@field:Perspective]) = 0 THEN 1 ELSE 0 END

 

Appreciate greatly any ideas and solutions,

 

Regards/Gunnar

[cheonsa]

Hi @KG360,

I tried to replicate the issue on my end, however, I experienced a different problem. I am getting an Invalid Formula error if I don't convert both equations to integers. 

May you please try this one:

CONVERT(int,(SELECT COUNT (DISTINCT Perspective) FROM tbl32_OrgTargets WHERE Corp_FK=target.[@field:Corp_FK])) + CONVERT(int,[@cbParamVirtual62])

The value being saved in the table is correct.

 

Hope this helps.

[KG360]

On 5/26/2023 at 6:38 PM, cheonsa said:

Hi @KG360,

I tried to replicate the issue on my end, however, I experienced a different problem. I am getting an Invalid Formula error if I don't convert both equations to integers. 

May you please try this one:

CONVERT(int,(SELECT COUNT (DISTINCT Perspective) FROM tbl32_OrgTargets WHERE Corp_FK=target.[@field:Corp_FK])) + CONVERT(int,[@cbParamVirtual62])

The value being saved in the table is correct.

 

Hope this helps.

Many thanks. I will save that solution, and make special notice that you converted both fields to INT instead of just the virtual field. I ended up circumvent the issue by creating a 'realä field in the table to save the temporary value generated in the virtual field, and then it works. Virtual field behave!