Iterating through the data base using a formula in my report. 2

[mondi]

How can I make a formula in a report that iterates through the data fields of a data set that I put in my report as the data source?
The exact problem that I have is this: I have a data table named <ITEMS>. This data table has a data field named <ITEMS_ID>. The <ITEMS> is linked with another table named <COUNTRIES>. The <ITEMS_ID> is the key for the two tables. Now I have to check for all the items i.e. all the <ITEMS_ID> in the data base and for each item, to find it's country of origin in the <COUNTRIES>.
How can I check the <ITEMS_ID> ?
I am using Crystal Reports for .NET version 2002
The data base wasa created in SQL.
Thanks for any answers.

 

[kenhamady]

Can't you just link these two tables using ItemID? That way the Country will become an additonal column (field) for each record.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[mondi]

I have linked the tables by the <ITEMS_ID>, but the problem is that I don't know how to iterate through all the ItemsID-s.
My data set has a table <TRANSPORTS>. And for each transport, I have different items. For each item I have to control their country of origin. If the country of origin is the same for all the items then I have to display in the report the name of the country. If there are more than on countries then I have to display a message.

 

[kenhamady]

A formula is a virtual column - not a process that can go back and forth through the records. You check to see if there is more than one country by doing a summary of the country field that does a distinct count. When you say:

&quot;If the country of origin is the same for all the items..&quot;

All the items in the entire report or in each transport?
Are your records grouped by Transport?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[mondi]

In my data set there is only one transport. The transport has different items.

 

[lbass]

You probably have a group on transport, and then you should group on {items.items_ID}. Then create a formula for your group (item ID) header or footer:

if distinctcount({countries.country}, {items.items_ID}) = 1 then {countries.country} else &quot;Your Message&quot;

Make sure you substitute your exact field names.

Hint for future posts: Your first post left out some key information: &quot;If the country of origin is the same for all the items then I have to display in the report the name of the country. If there are more than on countries then I have to display a message.&quot; To get helpful answers, it is very important to outline the entire problem in the original post.

-LB

 

[kenhamady]

or, if you mean ALL items in the entire data set you would use:

if distinctcount( {countries.country} ) = 1 then ....

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[mondi]

However thanks for your answers.
I appreciate that a lot. I think the distinctcount will solve my problem.