Possible Linking Error?

Chris Springer · June 2016

Hello all,

My organization is trying to change the way our weekly revenue report tracks gifts in our reports. Previously, we used the campaign field and our report worked fine, but now we want to use the fund type. The report currently isn't returning any results for two particular fund types after updating the formulas accordingly. I'm newer to linking things using the database expert, but my coworker and I believe this is where our problem is. What are some typical errors I can look for?

Sidenote, I'm also newish to RE and Crystal, so let me know if I need to provide you with more info.

John Heizer · June 2016

Use the Database Expert to make sure the fields are linked with a "Left Outer Join."

Chester Bammel · June 2016

I would say you are on the right track. If any one of the tables that is joined does not have a value, then that record will not show up at all. So, look at the main table and for any table linked to it, that may have even the remotest possibility of not having a value and turn that join into a left outer join.

I'll give you an over simplified example. You have records for customers. John Smith has purchases, but you have a table called "Electronics". If you use an = join and John has not purchased any electronics, then John would not show up in the report at all. If you change the link to a left outer, then even if John does not have any electronic purchased, his record would still be visible.

Hope this helps a bit.

Joshua Bekerman bCLO bCRE · June 2016

Adding to Chester's example, this might also help:

http://bekerman.com/blog/table-links-an-explanation/

The other articles might help since you are "newish"

Chris Springer · June 2016

Thanks everyone for your responses. I found the particular link that needed to be left-joined and it solved one of the fields. However, I am still having an issue with one other. We are attempting to code our temporary restricted gifts so that the gift attribute is the field our report pulls from. Right now the report is only pulling the gifts that have an attribute description filled in. This sounded like another left joining issue, so I tried tweaking the links, but no luck... Could it be due to the way tables are arranged? In case it helps, here is the formula I'm using...

if {Gf.Gf_Date}>=Date (2015,07 ,01)and {Gf.Gf_Date}<=Date (2016,06 ,30) and

{GfAttrCat_1.GfAttrCat_1_Description} <> "Grant" and {Gf.Gf_Fund} <> "UNR" then {Gf.Gf_Amount} else 0

Thanks for all your help!

Marie Stark · June 2016

I think you also need to account for Null or blank values. Something like If not is null ({GfAttrCat_1.GfAttrCat_1_Description) then whatever , or if GfAttrCat_1.GfAttrCat_1_Description] <> ' ' then whatever.

Chris Springer · June 2016

Well the problem is now officially solved! It turns out one field was a result of needing to be left joined, and the other was a result of how the formula dealt with nulls. I changed the formula setting from "Exceptions for Nulls" to "Default Values for Nulls."

Thanks everyone for your help!

Possible Linking Error?

Comments

Categories