Got another weird one here. I've got an inve...

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.

0 3 396
3 REPLIES 3

@Marc_Dillon

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.

@Marc_Dillon

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

Top Labels in this Space