Change entered values to positive or negative values

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • In Progress
I have been requested to create a field that changes negative numbers to positive numbers and positives to negative numbers after being entered in the field. I looked at using the ABS but only works with converting negative values to positive but QB has a limitation preventing users from referencing the current field in the formula. 
Any help is greatly appreciated. 
Thanks
Photo of Troy

Troy

  • 110 Points 100 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,664 Points 50k badge 2x thumb
You can make a new field and the formula would be

- [my data entry field]
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,664 Points 50k badge 2x thumb
It is a confusing request. I'm sure that I could find a solution, but the idea of flipping the sign on whatever the user enters seems so foreign to me that I hesitate to suggest a solution.

Are you saying that if the user enters a positive number you want that data entry to be changed to negative and vice versa?

I would have to understand the use case for this need.
Photo of Troy

Troy

  • 110 Points 100 badge 2x thumb
Yes. That is exactly what they are asking for. I know its confusing and strange, which is prolly why I haven't been able to figure out a solution. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,664 Points 50k badge 2x thumb
I'm not going to contribute to that madness.  :) 

Imho it is too dangerous to change the user's actual input.  I suggest a formula field to be used which calculates the opposed signed result of what the user entered, and show that on reports and in view mode.

I say dangerous in the sense of never really being sure what the user entered and if an automated process flipped the sign.  Then what happens if the user edits the record, do we flip the sign again?
Photo of Troy

Troy

  • 110 Points 100 badge 2x thumb
Agreed. What about just being able to keep only positive numbers. If a user enters a negative number it is automatically "flipped" to positive. If they enter a positive it stays positive?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,664 Points 50k badge 2x thumb
If you make a field with a formula of ABS([my data entry field]). Call it [Positive Field],

and you put that field on the form (for now, we can hide it later) then you should be able to have a form rule that says

When the record is saved
 and [my date entry field] is not equal to the value in the field [positive field]

Change the value of [my data entry] field to the value in the field [Positive value] 
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
>... too dangerous to change the user's actual input ...
>...  never really being sure what the user entered ...

Human input should always be verified and changed if incorrect, in the wrong format or if a typo is detected. Luckily it is simple to do with script. Here is the core code (for use with IOL) for negating the number entered by your human:
$("#_fid_6").on("change", function() {
  $("#_fid_6").val(- parseFloat($("#_fid_6").val()));
});
Using a formula to do this only bloats your application with superfluous fields (assuming there is no need for retaining the human's original keystrokes).