how to filter negative numbers in calc field

[kwparker80]

I know this is probably the simplest thing to do, but I have pulled out what was left of my hair trying to figure it out.

I need a calculated TOTAL field on a daily sales report that displays the total of negative amounts entered in the 'Amount Paid' field on the invoice. The invoices are filtered by a user selected date at opening. All the other calculated breakdowns by cash, credit card, etc. work fine, but no matter how I try to do it I can't seem to get a formula to work for Refunds.

The PAID field is in the INVOICE table, so I can't see why this is giving me so much trouble. What do I need to add to TOTAL(PAID) to get this to work. I tried TOTAL(PAID<0), which Alpha5 likes, but returns a '0' even though I have a -$14.99 payment saved in the database. TOTAL(PAID)<0 returns a 'F' logical answer even though there is a negative amount in the database.

I have amazed myself on how fast I am picking up xbasic up till now. I feel like I haven't learned anything when I run in to things like this.

Thanks to anyone who might be able to help!

Ken

[Mike Wilson]

Focusedon correct fied?

Ken,

In my table for donations that has a calculated field for the field "Amount", I made some negative entries and created a query on the table to filter for all values <0 with the Genie. It worked fine. The spectacles gave this code:

'Default comment text.
DIM records_found as N
records_found = topparent.queryrun("Amount < 0","","","No","",.f.)

You wrote:

I tried TOTAL(PAID<0), which Alpha5 likes, but returns a '0' even though I have a -$14.99 payment saved in the database. TOTAL(PAID)<0 returns a 'F' logical answer even though there is a negative amount in the database.

I'm assuming the calculation for the Total is based upon the sum of the values in the records Paid fields. So why are you not filtering the table for the values in the fields that are <0, to get the total of the records with negative values? It seems that your trying to filter the calculated field and not the table fields. Are you focusing on the correct field for your filter?

Mike W

[Tim Kiebert]

Ken,

In your report create a calculated field (doesn't need to be placed on the report) with following expression If(PAID<0,PAID,0) . Then create another calc (which you do put on the report) with the following: total(calc->What_ever_you_called_the_other_one)

[kwparker80]

Thanks, Guys

I guess you think I am am an ingrate for not responding sooner. I found out first hand last week what happens when your house is struck by lightning, and it took a while to get my ISP to get me a new DSL modem.

Your suggestions are appreciated, and I will give both a try tonight and see what happens. I'm sure one, if not both, suggestions will work just fine.

I failed to mention that the calc field I had created was returning a COUNT of the negative transactions for the given date, so I feel sure that the additional calc field totaling the other will work just fine.

I new it had to be something simple.

Thanks Again!