Crystal Reports Linked Tables/ formula
Hi, I hope I can explain this correctly, complete novice in Crystal!
I have a report created that I export data form an Access DB into a csv file. The crystal report contains 3 tables at the moment, linked with left outer join and with internal reference as the field that should be matched. This works fine
I have now added another table that contains around 12 records. I have checked and these 12 records are present in all 3 of the existing tables, so I added the table again with a left outer join and with the same field. I have a formula field in crystal called paid, which adds up the field paid in all 4 tables. Since I have added the 4th table, this field now only show amounts when there is data from the 4th Table, otherwise the field is just blank. All other fields are as expected. I'm certain that this is something to do with my links, I have tried everything but can not get the field to show the correct data in the paid field. The formula is very basic and just adds the paid fields from all the tables together. If I remove the 4th table from the formula all works fine, but without adding the amount from the 4th table.
Tables are linked as follows
table1 -left outer join =internal ref table 2 -left outer join =internal ref table 3 -left outer join =internal ref table4
I have tried other links but when I do this I only get the data from table 4, I missing something really basic I know
Can anyone put me out of my misery :-)
I have a report created that I export data form an Access DB into a csv file. The crystal report contains 3 tables at the moment, linked with left outer join and with internal reference as the field that should be matched. This works fine
I have now added another table that contains around 12 records. I have checked and these 12 records are present in all 3 of the existing tables, so I added the table again with a left outer join and with the same field. I have a formula field in crystal called paid, which adds up the field paid in all 4 tables. Since I have added the 4th table, this field now only show amounts when there is data from the 4th Table, otherwise the field is just blank. All other fields are as expected. I'm certain that this is something to do with my links, I have tried everything but can not get the field to show the correct data in the paid field. The formula is very basic and just adds the paid fields from all the tables together. If I remove the 4th table from the formula all works fine, but without adding the amount from the 4th table.
Tables are linked as follows
table1 -left outer join =internal ref table 2 -left outer join =internal ref table 3 -left outer join =internal ref table4
I have tried other links but when I do this I only get the data from table 4, I missing something really basic I know
Can anyone put me out of my misery :-)
peter57r
Do you have any selection rules operating?
ASKER
Could this be the formula? Would I need to add if is null for the field paid from table 4?
ASKER
Only selection is on client name which is the same
Could this be the formula? Would I need to add if is null for the field paid from table 4?
I would try this... The NULL that you get with the left outer join could make your formula behave unexpectedly.
Sounds like your paid formula is failing because some values are null, there are a few ways to handle this
try
File menu -> report options -> convert database null values to default, check thi sbox and rerun your report
try
File menu -> report options -> convert database null values to default, check thi sbox and rerun your report
ASKER
Hi
Tried that does not make any difference unfortunately. It appears that when calculating the formula is there is no data in table 4 it does not add up the rest of the values in table 1, 2 and 3
Tried that does not make any difference unfortunately. It appears that when calculating the formula is there is no data in table 4 it does not add up the rest of the values in table 1, 2 and 3
please post your formula
ASKER
{DamagePayment_ThirdPartyD
As soon as I add the last field is when it only returns values with an internal reference in that table. I want it to return the total of all those fields, even if there is no internal reference, in a table, does that make sense
Thanks
As soon as I add the last field is when it only returns values with an internal reference in that table. I want it to return the total of all those fields, even if there is no internal reference, in a table, does that make sense
Thanks
Try this, this should fix the issue if it is down to nulls : make sure you untick the "convert database null values to default" option first
local numbervar cMTCost := If IsNull({Migration.Total Cost}) Then 0 Else {Migration.Total Cost};
{DamagePayment_ThirdPartyD
if this doesn't fix it then your issue isn't down to null values, can you upload the report with saved data for us to look at.
local numbervar cMTCost := If IsNull({Migration.Total Cost}) Then 0 Else {Migration.Total Cost};
{DamagePayment_ThirdPartyD
if this doesn't fix it then your issue isn't down to null values, can you upload the report with saved data for us to look at.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That seems to have worked, thank you. Any chance you could explain what it is doing? I guess it is creating the MTCost buy looking at the field, is that contains a null then show 0?
Yes it's simply replacing a null value with a zero, strange the first option didn't work as that shoukd have the same effect.
Anyway glad you got it working.
Anyway glad you got it working.
It sounds like GJParker has this sorted out, but just to add to what he has already said:
If the problem is nulls and this is just a regular formula and not something like a record selection formula, then "Convert Database NULL Values to Default" really should have taken care of it. You did change that setting under File > "Report Options", correct? You can also find that setting under File > Options, but that sets the default, which won't change an existing report.
The more recent versions of CR also have a setting in the formula editor to control how an individual formula handles nulls. You could see if you have that setting.
FWIW, here's an explanation of the problem from the CR 10 Help:
In general, when Crystal Reports encounters a null valued field in a formula, it immediately stops evaluating the formula and produces no value. If you want to handle null field values in your formula, you must explicitly do so using one of the special functions designed for handling them: IsNull, PreviousIsNull or NextIsNull.
If the problem is nulls and this is just a regular formula and not something like a record selection formula, then "Convert Database NULL Values to Default" really should have taken care of it. You did change that setting under File > "Report Options", correct? You can also find that setting under File > Options, but that sets the default, which won't change an existing report.
The more recent versions of CR also have a setting in the formula editor to control how an individual formula handles nulls. You could see if you have that setting.
FWIW, here's an explanation of the problem from the CR 10 Help:
In general, when Crystal Reports encounters a null valued field in a formula, it immediately stops evaluating the formula and produces no value. If you want to handle null field values in your formula, you must explicitly do so using one of the special functions designed for handling them: IsNull, PreviousIsNull or NextIsNull.