I'm new to the forum but here goes my first question.

 

I have an invoice file that is keyed by company id and service date.  I also have a cash recept file keyed by compnay id, service date, and a sequence number that holds transactions against the invoice.  Each transaction in the cash receipt file has a payment code. (P)ayment, (R)efund, (C)redit, (A)djustment, and (E)xtra payment.  The total due on the invoice is defined at total billed ( in the invoice file ) - sum of the payment amounts using only the P,A,C payment types.  The catch is some invoices will have no records in the cash receipt file.  I need only the invoices with a total due <> 0 to appear on the report.

 

 I have the two tables linked by by company id and service date with a left outer join specification.  If I have no filtering on the payment code I get the invoices with no payment records just fine but when I filter on payment code not in 'R' 'E' the invoices with no payment records diappear from my report.

 

Any help would be greatly appreciated.

 

Thanks in advance.

this would be formating the detail section

 

Jill Messier

 

IsNull({table.payment code}) or

not ({table.payment code} in ["R", "E"])

 

In the Select Expert, select this formula and "Is True".

 

-Dell

I'm not really showing any detailed information concerning the invoice just the amount billed, amont paid,   and the total due.  the detail payments are always hidden.

 

The IsNull({table.payment code}) or not ({table.payment code} in ["R", "E"])  got me real close. But the strange thing is that if I reverse it and do the payment code in ....or is null the invoices with no payment disappear again.  Strange huh!.  So, I can now produce a listing of all invoices regarless of total due and regardless if payments have been made.  Thats great and definately a step in the right direction.  Now, to only show those where a we have something due. 

 

MY report is grouped by Comapny ID and Service Date, each unique pair is an invoice.  Total billed is defined as Admin fee + Setup Fee.  Total paid is defined as sum(payamt) where my paycodes are filtered as suggested.  Total due is defined as total billed - total paid.  If I try and do a group selection where total due <> 0 I get an error saving it can't be calculated until later,  IF I use total billed - total paid which is the same as the formula for total due  the condition is excepted and works fine if I runit for a single company.  When I open it up to all companies the group selection is only applied to the one compnay I have been testing with.  All invoices for all other companies are shown regarless of total due.  What's up with that?  I was filtering to the one company at the records selection level whic is where I put the payment code selection.  CAn Record slection and Group selection be used together?

Since your Total Due is a somewhat complex formula, I would handle that by setting a suppress formula on the section where you're displaying the data.  The formula would look like this:  mailto:%7b@Total - {@Total Due} = 0

 

Since the records with a 0 total due will actually be included in the result set (the display of them is suppressed so that they don't appear) if you want to do any counts, you'll have to do something like this:

 mailto:%7b@CountIt - {@CountIt }
If  mailto:%7b@Total - {@Total  Due} <> 0 then 1 else 0

Then, instead of doing a count, you do a sum of mailto:%7b@CountIt - {@CountIt } to get a count of the records that are actually appearing on the report.

 

-Dell