You cannot add a Wrk table to any other report. In many cases it does not actually exist and in all cases it is empty of date until the printing program it references is run. In this case Invoice Printing. You need to add the CI table instead

That would explain why I was so frustrated. Do I need to do anything other than add the field? Is there a formula that I need to create? Thanks, BigLouie.

You have to first link in the CI_Item table and then the CI_ExtendedDescription table. Link the two CI tables based on the extended description key.

The result of simply adding the CI_ExtendedDescription table to the report database, then dragging the field ExtendedDescriptionText to the report: I ended up with an 1145-page report, with the first 1144 pages filled with the bill number and description, with the components tacked to the very end. When I added the CI_Item table as well, and linked them by name, I got 2878 pages, no bill number or description, and each component was listed multiple times with what appears to be every extended description in the database. I haven't decided whether that's progress...

OK let me be a little more specific. In the BM_GrossRequirementsWrk table create a UDF and make it a Business Object and then as source select CI_Item and then Extended Description Key. Update the ODBC. Next open the report in Design view and add the CI_extendeddescription table and link on the key as a left outer since not all the items would have extended description. Update the ODBC and then open the report in design mode and do an Update Database. Then add the field to the report.

OK let me be a little more specific. In the BM_GrossRequirementsWrk table create a UDF and make it a Business Object and then as source select CI_Item and then Extended Description Key. Update the ODBC. Next open the report in Design view and add the CI_extendeddescription table and link on the key as a left outer since not all the items would have extended description. Update the ODBC and then open the report in design mode and do an Update Database. Then add the field to the report.

Specificity is good, as I'm approaching this from a complete lack of programming knowledge/experience. And most of what I've managed to do so far can be attributed to luckily stumbling upon the answer. I'm going to need help from the IT department on the ODBC, as I don't want to do something that makes a huge mess.

OK you don't understand. In Custom Office when you create a UDF there is a prompt to update the ODBC settings. It happens in Custom Office. I would suggest working with your reseller if this is your first time with UDFs

Can you guide me on how to update the ODBC? I looked at the Crystal Reports manual that I have and it wasn't much help. Also, is updating the database and verifying it the same? I don't see a choice for updating it in Crystal.

I'm sorry to come across as a complete ignorant newbie, but well, that's pretty much what I am. I appreciate your offering help and sharing your knowhow.

I did the UDF and updated the ODBC there. It was the second directive to update the ODBC that has me confused. Our IT person hasn't had much experience with Crystal, and was unsure what to do as well. Maybe trying to muddle my way through isn't the approach that I can take this time. Thanks for all your time.

When you open the report in Crystal you have to do a Verify Database to get the field to show up.

I did that a few times after adding the UDF and it told me that it was up to date each time. I just verified it again and got a notice that the database had changed. Maybe that was part of the problem, but I'm still getting multiple pages and the report seems to have gotten hung up during generation. I was really trying to learn how to do it rather than go to the reseller, as they are good at just doing things for us instead of teaching us how to do them.

It sounds like the linking between your data tables may be set up wrong. In Crystal go to the Database menu at the top and go to Database Expert. You should see both of your tables on the right side of the menu, BM_GrossReqrmntWrk and CI_ExtendedDescription, assuming you see both of your tables there click the Links tab in the upper left hand corner.

Both of your tables should show here, you will want to have the BM_GrossReqrmntWkr table to the left of the CI_ExtendedDescription table, spread them apart enough so that you can see white space between the two tables. On the right side of this window there should be a Clear Links button, if the button is not grayed out click it. It will ask you if you want to remove all the links, say yes. Now that your links are clear we will need to connect the two tables.

First we will want to locate the UDF you created in BM_GrossReqrmntWrk, UDF's are located at the bottom of the table you added them to. Scroll down to the bottom of the BM_GrossReqrmntWrk table, you should see your UDF here. Click on the name of the UDF so it is highlighted, now click and drag from the UDF to the ExtendedDescriptionKey field of the CI_ExtendedDescription table. You should see a line drawn between the two tables now, representing your link. Double click the line to view the Link Options. Under the Join type on the left side you will want to select the Left Outer Join. Leave the other options as they are and click OK at the bottom. You now can click OK at the bottom of the database expert window as well.

This will hopefully straighten your report out.

Sincerely,

Jonathan K.

Yes! Thank you so much, Jonathan, that solved my issues and shows the extended descriptions. I was letting the link wizard link them, and that must be what was causing my problem--I had too many links happening. Thank you again! The Crystal help file was, no surprise, really no help at all.

If you add your UDF to the report do you have data in it? I am wondering if that actually contains the data we think it should.

If it were me here is what I would try next...

I would have the BM_GrossReqrmntWrk, CI_Item, and CI_ExtendedDescription tables. Clear your existing links and relink them like this:

I am assuming you want the extended description for your component item codes... It is important to note that for the link between CI_Item.ExtendedDescriptionKey > CI_ExtendedDescription.ExtendedDescriptionKey I have used a Left Outer Join, if you do not use a left outer join you will only see records for items that have extended descriptions. I am assuming not every item in your system does.

When placing the extended description on the report it is important to have it in a section where it would be directly linked to an item. For example in the details section, or in the ComponentItemAndRevision group.

Hope this helps.

- Jonathan K.