How to Link a New Field

Michele Thompson 2

Hi! I had to add a field to my export that we use for a Crystal Report.  I went to the Database Expert and added it to my fields but it is not linked and there aren't any lines to link it to anything. Any thoughts on this? Did I do something wrong?


Thanks for the help!


Michele

Chester Bammel

There has to be a common field on the table containing the field and one of the existing tables.  I don't know the tables you are using, but for instance, if you had a table with state names , it will have a unique ID.  Then if you have a table of addresses, it might have that same State ID field.  You would then link the state ID fields on the two tables and you would then be able to pull the state name.  Similarly, you must link some identifier field on your new table with the same identifier field on one of your existing tables. 

Michele Thompson 2

Chester Bammel:

There has to be a common field on the table containing the field and one of the existing tables.  I don't know the tables you are using, but for instance, if you had a table with state names , it will have a unique ID.  Then if you have a table of addresses, it might have that same State ID field.  You would then link the state ID fields on the two tables and you would then be able to pull the state name.  Similarly, you must link some identifier field on your new table with the same identifier field on one of your existing tables. 

Thanks Chester!  I got it to link somehow. Now the issue is sometimes it is putting the amount in and other times it is putting nothing. I can't figure out how. I did a formula that

 if {CnPrFin_2.CnPrFin_2_Information_Type} = "Capacity Identified" then {CnPrFin_2.CnPrFin_2_Amount}    and I got No errors found. Thoughts?

Chester Bammel

Is this value, "Capacity Identified" hand entered, or a set value from a dropdown or table?  If it is hand entered, the danger of using free form text is an extra space in the middle or end will result in a miss-match.  Do you have an ELSE in your statement?   I often, for debugging, put in a bogus ELSE.  For instance, if {CnPrFin_2.CnPrFin_2_Information_Type} = "Capacity Identified" then {CnPrFin_2.CnPrFin_2_Amount}  ELSE "Nothing Found".   In that case, you should get a value on every record in the report.  If you get "Nothing Found" then investigate if there really should be nothing.  If there should be, then investigate why for that particular record was there no match.

Michele Thompson 2

It's weird. It is putting $0.00 in those that have a capacity identitified amount. If there is nothing it just leaves a blank. I don't understand why it is pulling some but not others when there is an actual amount there.

Chester Bammel

It's so hard to diagnose without being able to see the data, the structure and the report itself.  I'm still betting it has something to do with the link, assuming there is nothing else wrong with any forumlas. 

Michele Thompson 2

Another note, when I run a CSV file to check the export, I see the amounts. So it is even more perplexing that it is not pulling it into the report. Maybe it isn't linked right, but not sure how to change it.

Michele Thompson 2

Not sure if this helps, but this is what I am linking.


 

Chester Bammel

On the surface, that looks right.  But I'm wondering about CnPrFin_2_IDKEY.   On the field explorer, right click on CnPrFin_2LINK inside the CnPr table and Browse Data.  Then, do the same for the same in CnPrFin_2.  Are they the same data?  If so you probably have the correct join.   But also look at Browse Data for CnPrFin_2_IDKEY  inside CnPrFin_2 and see what that data looks like.  I've seen some databases (Not RE) where they have a particular field like that for the KEY for that table.  It could be that you link to there instead.  You could always try it and if it doesn't work, switch it back.


 

Michele Thompson 2

I think you are onto something! One link is correct, the other looks like it is skipping. That may be the issue!



 

Chester Bammel

If the table that is missing data is in CnPrFin_2, then right-click on the link itself and choose right outer join as the join type and try that.


 

Michele Thompson 2

We always use Left Outer Join - it has something to do with RE. And that is already done. I did try the right just for the heck of it and got errors. I so wish RE had support for this - it must be something so simple!