Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Linkin Tables Post Reply Post New Topic
Author Message
JDodd
Newbie
Newbie


Joined: 01 Mar 2007
Online Status: Offline
Posts: 15
Quote JDodd Replybullet Topic: Linkin Tables
    Posted: 01 Mar 2007 at 1:14pm
Hi All,
 
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.
IP IP Logged
JillMessier
Newbie
Newbie
Avatar

Joined: 05 Mar 2007
Location: United States
Online Status: Offline
Posts: 1
Quote JillMessier Replybullet Posted: 05 Mar 2007 at 8:56am
Have you tried to hide the detail if the total sale =<0
this would be formating the detail section
 
Jill Messier
Jill Messier
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 05 Mar 2007 at 10:40am
Create a formula something like this:
 
IsNull({table.payment code}) or
not ({table.payment code} in ["R", "E"])
 
In the Select Expert, select this formula and "Is True".
 
-Dell
IP IP Logged
JDodd
Newbie
Newbie


Joined: 01 Mar 2007
Online Status: Offline
Posts: 15
Quote JDodd Replybullet Posted: 06 Mar 2007 at 7:39am

First of all thanks so much for your replies.

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?
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 06 Mar 2007 at 9:15am

The reason the formula doesn't work if you move the IsNull check to the end has to do with the way databases handle nulls and the fact that formulas with "and" or "or" are evaluated from left to right.  If you compare a field with a null value to a specific value, the null values will always be false no matter what the comparison is.  For example, if you do {table.field} <> 'S', all records with a value that is not 'S' will appear in the result but any record with a null value in the field will NOT be in the result.  So, since reversing the order of the formula does the explicit comparison first, the nulls are filtered out there.  If you check for null first, the nulls will be included and only the non-null values with be evalueated in the rest of the formula.

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:  {@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:

{@CountIt}
If {@Total Due} <> 0 then 1 else 0
Then, instead of doing a count, you do a sum of {@CountIt} to get a count of the records that are actually appearing on the report.
 
-Dell
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.