I am trying to implement an inventory counting app with a Google Sheet storing all the values. I have an original quantity field and a rechecked quantity field. My pivot table has to poll the figures from the rechecked qty column because 10% of all original checks will be rechecked and if they change I need to make sure I am counting the rechecked figure and not the original. The problem I have is that on the recheck screen I have to set the initial value at 0 because the rechecker shouldn’t know the original count. However my spreadsheet needs to have a recheck quantity value stored after the original count because that is the column that my Pivot Table polls and this causes a problem on the Edit screen in the app because it will set the initial value from the spreadsheet hen I really need it to be 0. Can I save the rechecked qty value the same as original qty value when saving a new record and then present the user with an initial value of 0 on the edit record screen?
If I have understood correctly, I think you might be able to change your design and logic a little bit to achieve what you are looking for.
What if you adjust the pivot table to get the values from a third column (e.g. Final Check, Final Qty., etc.) that is hidden from user and contains a formula that if the [Recheck] is 0, it would return [Check]'s value, and if it is >0, then it would consider Recheck as final quantity; this way you can always keep a 0 for [Recheck] column’s Initial Value.
Hi Reza thank you. I am still a little unsure. I have [Original Quantity] and [Rechecked Quantity] which initally are being saved with whatever [Original Quantity] is. [Rechecked] will potentially change for 10% of all [Original] entries so my Pivot Table is polling [Rechecked] on all rows. I just can’t seem to offer the user a 0 value for [Rechecked] quantity when the edit screen is opened. Are you able to expand a little more if you think the above is still how you understand it.
Well, that is impossible to always show initial value 0 for a column to user, and at the same time after saving that record change the value to something else, or display 0 for recheck to user while the existing value is something else. As long as you are using initial value 0, it will be saved in spreadsheet too upon save.
Therefore I was suggesting a workaround i.e. adding a third column that applies this logic and decides which quantity is supposed to be the final quantity that is going to be used by pivot table later on. Does it make sense?
I think I have it working with your suggestion. Just took one more time to read it . Thanks a million.