Searching thru one field using more than a hundred values

[cvasquez]

Hi everyone. I am encountered with a problem when searching thru a single field and only getting results based on those values. Here is a short version of that statement being used:

{INVENTOR.ITEM_ID} in ["180860","180960","180871"]

Here, I only have three values but I need to search over 3000 of them. The database contains around 10,000 items.
I only want to search 3000. If I start adding all of the values in the above example, Crystal V10 does not like it.
Is there an easier way? Please help. Thanks.

Carlo

 

[lbass]

The question is what those 3000 have in common that leads you to want to select them. You should then be using that criterion in your selection statement instead.

-LB

 

[cvasquez]

How do I use the criterion in your selection and can I input 3000 items in there to be compared against that one field , 'INVENTOR.ITEM_ID'? I am new using crystal. You see this task was given to me. I did not choose to do it. There must be an easier way.

 

[Madawc]

You could hard-code 3000 values in a formula field, but this would be a crazy way of doing it. If you have any control over the database, suggest that the values become a new table.

Failing that, it is apparantly possible to use an Excel spreadsheet as one source for Crystal. I've never done it, check Forum 3.

Madawc Williams (East Anglia, UK)

 

[cvasquez]

Thanks will check Forum 3

 

[Naith]

cvasquez,

It's only necessary to take this into the integration forum if you're going to use a file consisting solely of these 3000 values as a datasource.

In doing this, you'd still have to address the initial point raised by lbass.

i.e. what is the criteria you're going to apply to identify all these ids? What makes you want 180871 as opposed to 180872, for example?

It's probably possible to do this without explicitly making reference to each value.

Naith

 

[cvasquez]

The problem is that I was giving a list of items to be pickup by a search criteria on a field,"ITEM_ID". They want to get a description of these items, vendor, Country..etc..
The problem is, that we have over 10,000 items in our database and they only want me to select 3000 items which were given to me on Excel sheet.

 

[Naith]

If you already have the Excel spreadsheet with these values, then linking this into your existing datasource is the way to go.

This will remove the need for listing in values in the report.

Naith

 

[synapsevampire]

LINK (not import) your datasources into an MS Access database, it's more efficient and you'll find maintenance much simpler. Excel sux as a datasource, and those using it as such should immediately transfered to the mailroom or upper mgmt where they won't get in the way anymore.

You can then use a Union query to expose what you want, and base the report on the Access Query.

-k

 

[cvasquez]

How do I go about linking an excel spreadsheet information into Crystal or an Access Database. Any documentation or help on this anywhere?

Thanks for your help guys..it has been very good up to now.

 

[synapsevampire]

In Access use the insert table and select link rather than import and browse out the spreadsheet.

It's the same for your other datasoruces, only you'll use an ODBC source.

Now Access will treat them as local tables and you can query across the data sources.

I don't have Access installed here, and it's likely Access version dependent anyway, so hit F1 and search for LINK TABLE or some such, it's very intuitive.

-k