Comparing Number Field to Comma Separated String 1

[mwake]

I'm using CR2008 with an Oracle 10g db as my data source. I have a report which returns all countrys within a country group. Each country has an id which is a number field (ex. 200.00). Users have the option of selecting individual countries within the country group, which populates a selected countries field, which is a string of comma separated numbers (200, 250, 300, 350). If they select individual countries, I am only supposed to return those countries selected. I need a formula that compares the country id number to the values in the string and returns true if it appears in the string, or false if it does not, so I can suppress the countries not selected:

Country ID Country Selected Countries True/False
100 US (100, 150, 200, 250) True
125 France (100, 150, 200, 250) False
150 Italy (100, 150, 200, 250) True

 

[MCuthill]

mwake,

A formula field as follows should return what you seek.

IF InStr({Table.SelectedCountries},{CountryID})>0 THEN TRUE ELSE FALSE

InStr() returns the first character of the substring if found, zero if it is not found. InStr() is of the form InStr(Str1, Str2) where Str1 is the whole string, Str2 is the substring you are searching for.

For example:
InStr("(100, 150, 200, 250)","100") will return 2
InStr("(100, 150, 200, 250)","120") will return 0
InStr("(100, 150, 200, 250)","150") will return 7

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

mwake,

If the above does not work (because CountryID is a number, not a string), try the following:

IF InStr({Table.SelectedCountries},ToText({CountryID},0,""))>0 THEN TRUE ELSE FALSE

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[mwake]

Thanks Mike. That did the trick!!

 

[SmpTraining]

This may not affect you mwake but...

For this solution to be reliable, all country ID's must be exactly three digits long. If country id's were to range from 1 to 9999 for example, then searching for '1' would match many incorrect countries (e.g. 100, 101, 14, 914 etc.)

In these cases you would search for either of the following:
'(1,'
', 1,'
', 1)'
'(1)'

In otherwords, you would search for '1' at the start of the list, the middle of the list and at the end of the list. You would also need to search for '1' being the only item in the list!

This may help others even if it doesn't affect your situation.

Steve Phillips, Crystal Reports Trainer & Consultant