Repeating some rows dependant on field value

[tomkonec]

I have the following challenge using Crystal

EXAMPLE DATABASE

FIELDS:
Product Section No Price
-----------------------------------
6977AQR 1.10 1.16
6978ARW 1.10/2.10 0.78
6978AWD 2.2 /2.10 1.19
6979AWQ 2.10 0.98

The slash in the section number shows that the product appears in more than one section. The slash is always in position 5 of the "Section No" field

The database is a list of products and I would like to print a list of products grouped and sorted by section number. This is fine, however the complication comes where the product is repeated in more than one section. In this situation I would like to print (i.e. duplicate) the product in both sections.

Example:

SECTION 1.10
6977AQR
6978ARW

SECTION 2.10
6978ARW
6978AWD
6979AWQ

SECTION 2.2
6978AWD

Hopefully this makes sense. I'm not sure where to start in order to make the rows duplicate themselves.

 

[RedHeadedStepITChild]

If you do not have too many sections then you could write a formula to use as a group:

If SECTION = 1.10/2.10 then
"Section 2.10"
Else
If SECTION startswith 1.10 then
"Section 1.10"

There is a possibility that this formula will "loop" because it has two true statements in the If Then Else statement.

When you have the formula created, just insert the formula as a group.

Hope this gets you started.

Jason

 

[Madawc]

You're wanting the same detail line to appear in two groups. This isn't possibly in Crystal, unfortunately. You can add the same table twice, the second time as an alias, but I don't think that will help.

You could do a subreport that shows just the records that have a second section number equal to the group's section number. Imperfect and it would miss any section number that existed only as a second section number.

If you are using SQL, it would be possibly to write something in SQL that would do this.

PS. It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

You could use a union statement to return the field twice by using a command as your datasource. If your database was Access, you could use a union like:

Select table.`product`, left(table.`sectionno`,4)as secno,table.`price`
from table
union
Select table.`product`, mid(table.`sectionno`,6) as secno,table.`price`
from table

You could then group on {command.secno}. You will have to determine what functions you can use in your particular database though, as left and mid might not be options for you.

-LB