Separate expenses from deposits

Panorama X
1

I have a list of expenses and deposits, all in Field “Amount”
The expenses are negative Dollar amounts, the deposits Positive.
I want to separate them and place the expenses in a “Debit” field, and the deposits in a “Credit” field.

ANy suggestions as to how to do this?

2

I think this procedure code will do it.

field Debit
formulafill zeroblank(max(-Amount,0))
field Credit
formulafill zeroblank(max(Amount,0))

You could also do it using the same formulas in the Morph Field dialog.

3

THAT DELETED ALL THE AMOUNTS, BUT DID NOT fill in the debit field

4

If I use the Morph fields dialog, should I select Debit field, then Formula formulafill zeroblank(max(-Amount,0))

5

image

Sorry, but I can’t figure out where to insert the command lines in the morph field dialogue

6

I figured it out.
I selected all the values less than zero
Then used morph field in Debit, start with amount field, and all the negative numbers appeared in the debit field.
The n did same for positiver to credits

7

Jim showed you a procedure code (with statements and functions). Choose the menu item View > New Procedure … and paste the code there.

Here is another way to write this code:

field Debit
formulafill zeroblank(?(Amount<0,-Amount,0))
field Credit
formulafill zeroblank(?(Amount>0,Amount,0))

Off course, you can do it manually using the Morph dialog. Then you would have to do similar steps:

Click the field Debit
In the Morph dialog use “Start with formula” and enter

zeroblank(?(Amount<0,-Amount,0))

Then click the field Credit
In the Morph dialog use “Start with formula” and enter

zeroblank(?(Amount>0,Amount,0))

As you can see, using the procedure is a comfortable and repeatable way. Later on you invoke this procedure from the Action menu.

8

Chuck, one of the elements of a design decision is “How often do I want to do it?” That determines the steps to get it done. If it’s to be done repeatedly, then you would make a procedure - which people have given above - so you could have the same steps/action performed as needed. If it’s to be done one time - like importing/converting data from an old system to new - then you have elegant and you have ugly but it works.

It’s sort of like those Mate in Three chess problems. One could spend an hour pondering how to accomplish the goal in just three moves. Or one could take a few minutes and make some moves that clearly solidify the win, though it will take say five, quick, steps.

In your case, I’m guessing you could have just selected records with a negative amount, clicked in the debit field, and formularfilled with Amount. Then selected records with a positive amount (or select reverse) and formulafilled in the Credit field.

There might be some zeroblank cleanup depending on content.

So my point it, its important to consider how often something needs to be done. I think, if it’s a One Time deal, the phrase, “quick and dirty” can apply. It’s not elegant. But it gets the job done and you can move on to the next step.