Change entered values to positive or negative values | Discussions

Expand all | Collapse all

Change entered values to positive or negative values

  • 1.  Change entered values to positive or negative values

    Posted 11-29-2017 22:08
    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


  • 2.  RE: Change entered values to positive or negative values

    Posted 11-29-2017 22:12
    You can make a new field and the formula would be

    - [my data entry field]


  • 3.  RE: Change entered values to positive or negative values

    Posted 11-29-2017 22:14
    Actually, it probably has to be

    -1 * [My Data entry field]


  • 4.  RE: Change entered values to positive or negative values

    Posted 11-29-2017 22:26
    But A formula may not contain a reference to itself directly or indirectly through another formula?


  • 5.  RE: Change entered values to positive or negative values

    Posted 11-29-2017 22:53
    Are you trying to change the sign on a data entry field or a formula field.  if its a data entry field then you will nee to create a new field


  • 6.  RE: Change entered values to positive or negative values

    Posted 11-30-2017 14:49
    Its a data entry field. I've asked why cant the user just enter the information correctly, but they want to try and ensure data integrity. :) 


  • 7.  RE: Change entered values to positive or negative values

    Posted 11-30-2017 15:09
    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.


  • 8.  RE: Change entered values to positive or negative values

    Posted 11-30-2017 15:16
    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. 


  • 9.  RE: Change entered values to positive or negative values

    Posted 11-30-2017 15:33
    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?


  • 10.  RE: Change entered values to positive or negative values

    Posted 11-30-2017 19:59
    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?


  • 11.  RE: Change entered values to positive or negative values

    Posted 11-30-2017 21:38
    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] 


  • 12.  RE: Change entered values to positive or negative values

    Top
    Contributor
    Posted 12-01-2017 14:57
    >... 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).