Change [Amount] (Price type) to negative (*-1) if [Inc. Exp.] = "Expense"

[StephenMattison]

Howdy! I'm building an accounting app (streamlined Quickbooks) and I want to change the [Amount] (Price type) data to a negative if [Inc. Exp.] = "Expense".

I don't want User to have to bother entering a negative number into [Amount] when logging an Expense.  Especially if we are going to use the OCR feature to add Receipts to the App, which I am hoping to do soon. OCR will always read a positive price/[Amount] printed on the receipt, so I will have to have a way to turn [Amount] to a negative.  It would be best if the User would see this happening in real time, instead of "hope" it happens in the background after Sync. TYVM!

[JuneCorpuz]

Hey. You can create a validation that IF [Inc. Exp.] = "Expense" then [Amount] should be < 0 OR you can create an action that upon saving the form, it will change the number to a negative number if the [Inc. Exp.] = "Expense" and [Amount] > 0

[StephenMattison]

ty. I would like automatic changing [Amount] to negative to happen in real time if possible. It seems like this should be doable somehow. Especially if we are going to use the OCR feature to add Receipts to the App, which I am hoping to do soon. OCR will always read a positive price/[Amount] printed on the receipt, so I will have to have a way to turn [Amount] to a negative.  It would be best if the User would see this happening in real time, instead of "hope" it happens in the background after Sync. tyvm

I entered this into the App Formula for [Amount] and the expression checker liked it, but then the [Amount] field does not appear/show.    IF(([Inc.Exp.]="Expense"),[Amount]*-1, [Amount])

I also tried an expression in [Amount] using Virtual Columns (with no luck) where one VC is just [Amount]*-1 and the other =[Amount]   IF(([Inc.Exp.]="Expense"),[AmountNeg], [AmountPos])

[Steve]

You cannot recompute the same column in which the user entered the value while in the form.

An alternative would be to add a separate column to receive the negated value.

Another option would be to leave it as a positive value but change how you compute your sums.

[StephenMattison]

Thanks Steve! I set it up originally to just compute my sums based on "Expense" or "Income" and it was working fine.  I might be overthinking the need to have [Amount] negative for "Expense."  The more I study how real accountants do this, the more I felt like my way needed to change, but I could probably do it all just fine as I have it. Accountants probably keep accounting complicated so you have to hire them to keep it organized.  I used to use Quickbooks, but I want to make my accounting much easier & faster than that.

I just replaced [Amount] with [Credit] & [Debit], but now I have to change 15 Report expressions accordingly.  I might change it back to just [Amount] and play with it more before making big changes.  As long as I get the same/better results in the end, I'll be happy, but I'm torn between making everything super simple and visually make sense to me or making it so a real accountant would love the App.  Thanks again!