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