Got another weird one here.
Iโve got an inventory system where users input adjustments whenever they use inventory items. On the adjustment form they pick the item and how much they use. Now for entering in the amount, I wanted to make it easy for users if they ever have to do a simple multiplication, e.g. if they use 5 boxes of something that has 11 items in a box, they can enter the 5 โunitsโ and 11 โunit sizeโ. Otherwise they simply enter a straight value in a separate field โamountโ. Then the actual adjustment is computed in an โadjustmentโ virtual column with the app formula:
=if(
[Amount] <> 0
,
[Amount] , [Number of Units] * [Unit Size])
Iโve noticed in the past that sometimes some โadjustmentโ values are showing 0 when the user input a โnumberโ and โunit sizeโ, but they shouldnโt be 0. If I go into the record to edit it, but donโt change a single thing, it shows the โadjustmentโ value calculating correctly, then I can save it and everything seems fine.
Iโll repeatโฆ no actual edits were made, I simply opened the record and re-saved it, and it got fixed.
I also believe (but not 100% sure) Iโve noticed a messed up one, that got previously โfixedโ, but then again reverted back to showing 0.
It hasnโt happened in a while, until today there was another one, so I figured I take the opportunity to see if I could track down the issue. Any ideas?
It only happens when using the โunitsโ*โunit sizeโ calculation. Maybe the [Amount] <> 0
condition can be something better? I also have editable_if statements for the other 2 fields that require โamountโ to be zero, and โamountโ has an initial value setting of 0.
If the [Amount] field is both a free field and also dependent to [Unit] and [Unit Size] fields you can either leave the initial value blank or use:
=IFS( AND(ISNOTBLANK([Unit]),ISNOTBLANK([Unit Size])),[Unit] * [Unit Size], TRUE, NUMBER("") )
expression to either have the calculation value or empty value. Additionally you can verify the column with
OR(ISBLANK([Amount]),NOT([Amount]=0))
That expression is for the initial value of [Amount]?
What do you mean by verifying the column?
I need (wellโฆwant) to include the ability for the user to change their mind which method to use and โresetโ a field.
There is certainly more than one way to achieve what I need, and perhaps changing to a different way will eliminate this oddity. Iโm very interested if anyone has any idea as to why the [Adjustment] amount seems to flip-flop between the correct value, and 0, without any changes being made.
I do it this way:
1.) Set this expression to [Amount] columnโs Show_if =AND(ISBLANK([Unit]),ISBLANK([Unit Size]))
2.) Set this expression to [Unit] and [Unit Size] columnsโ Show_if =ISBLANK([Amount])
3.) Set this expression to the Initial Value of *[Amount] column =IFS( AND(ISNOTBLANK([Unit]),ISNOTBLANK([Unit Size])),[Unit] * [Unit Size], TRUE, NUMBER("") )
With this way you donโt need the [Adjustment] Virtual Column
User | Count |
---|---|
43 | |
30 | |
24 | |
23 | |
13 |