Discussions

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

You can make a new field and the formula would be

- [my data entry field]

Actually, it probably has to be

-1 * [My Data entry field]

But A formula may not contain a reference to itself directly or indirectly through another formula?

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

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. :) 

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.

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. 

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?

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?

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] 

>... 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).

Where would I place this script to change my field to a negative? Do I need to change my field type from currency to formula - numeric?

------------------------------
Hillary Whitney
------------------------------

Original Message:
Sent: 12-01-2017 14:57
From: _anom Diebolt)
Subject: Change entered values to positive or negative values

>... 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).

One way is to create a new formula field  with the formula

-[field which is positive]


------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

Original Message:
Sent: 11-05-2020 16:15
From: Hillary Whitney
Subject: Change entered values to positive or negative values

Where would I place this script to change my field to a negative? Do I need to change my field type from currency to formula - numeric?

------------------------------
Hillary Whitney

Original Message:
Sent: 12-01-2017 14:57
From: _anom Diebolt)
Subject: Change entered values to positive or negative values

>... 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).