2 Replies Latest reply: May 15, 2014 10:08 AM by guyporter _

# Changing negatives

Hi

I have a problem with extracts that come up from time to time. I am using Monarch to extract aging data to then upload to another system. The other system will not accept negative characters and relies on a letter to convert negative values to the right amounts.

So an invoice of £100 is imported as a positive £100. A credit for £100.00 on a report shows as -£100.00 and is imported as a code C (for credit) £100 and the system then converts this to a negative.

We successfully use a formula

If(I="P".or.I="C".or.I="A",-(A),A)

to convert negatives to positives and use a look up to get the codes so a payment is a P and so on.

this all works fine until the client in their wisdom mixes a credit as both a positive and a negative value so some credits are positive and some negative. The above formula converts the negatives to positives (as it should) but then converts the positives to negatives so the upload then fails

What I am looking for is two formuals that will do similar to the above does the following:-

Say

Look up code =J  Some J's are pos and some neg

So if Code J = a positive then the = JP

or if Code J is a negative the it equals JN

I can then tell a further lookup (I do formulas in simple stages or I get hopelessly lost) to show a JP as an invoice and an JN as a credit. The original formula

If(Item type="JN".or.Item Type="C".or.Item Type="A",-(Amount),Amount)

and I will get the right number to enter into the system

Date      Type                    Amount1 Amount 2

220710     J          -147.87     147.87

220710     J          -29.38     29.38

220710     J          -16.33     16.33

230710     J          0.8     -0.8

230710     J          143.76     -143.76

270710     J          79.22     -79.22

280710     J          5.58     -5.58

040810     J          4.7     -4.7

If you can make sense of this and help I would be grateful. IF there is a better way of doing this then even better. I would appreciate a formual that I can follow.

Thanks

Guy

• ###### Changing negatives

Assuming that codes and values from the report are represented by PreCode and PreValue, then two formulas will get this result:

PreCode    PreValue    Code    Value

J              1000    JP       1000

J             -1000    JN       1000

P             -1000    P        1000

C             -1000    C        1000

A             -1000    A        1000[/FONT]

/codeCode:

If(PreCode='J';

if(PreValue<0;'JN';'JP');

PreCode)[/code]and Value:

Abs(PreValue)[/code]

Does this represent your logic accurately?

• ###### Changing negatives

Many thanks the formulas work. I have to say that I am relatively new to calculated formulas in Monarch and this forum and your assistance has been very useful.

Thanks