I have a datasource defined by sql command. It produces a table with 4 fields, ID, Category, Item, Checked.

In my report, the id is matched to a record using select expert. In my report I want to display "checked" which is 0 or 1 based on category and item. so I am needing a formula. I want to return chr(168) or chr(252) based on true/false. Here is what I started with, obviously broke.

Select {Command.Checked}
where {Command.category}="Hazards" and {Command.item}="Electric Shock"

That helps, but I need to also specify which record to use. Each record will match like 10 categories in the view. I need to select the category which matches "xxx".

if
(
If table.category matches "xxx"
then select table.field
)
then chr(252)
else chr(168)

Here is a screen cast which shows the data in SQL and then the crystal reports formula editor. Hopefully makes sense. http://screencast.com/t/Nu5tzRvPAYM5

permitID = 2 does not produce a unique row in the command dataset. it will produce many matching rows. It is a one to many relationship in the database. The permit has core data (id, date, type, etc) and then the permit has these categories of hazards (electric shock, fire, flooding, etc). In the application the user selects a check box which marks the appropriate hazards. so on the crystal report I want to show a checkbox next to Electric Shock, Fire, etc. The way we have done it in past is to write a subreport for each checkbox on the report. And the subreport will have select expert to identify the exact row of data that applies. This could mean as many as 100 subreports on one crystal report. I was hoping to avoid that. Thanks for your help, I am guessing this is too confusing to handle here.

I understand where you are going. We have customer requirements that are very specific as to the data formatting on the page. So we insert the check boxes one by one. This is why I was trying to do a record select in a formula. As it is, I have to write a subreport that returns a wingding that I display on the form. this equals like 37 subreports on the one report I am currently working.

Again, thanks for being willing to discuss.