Complicated Select/Grouping -- two variables

[jollyreaper]

Got a doozy of a problem on Crystal XI. Our construction people use a sequence number to indicate the proper order for construction tasks to appear on the schedule. They also have the option of assigning the task to a task group.

The table in the database thus looks something like this (using pipes to separate fields)

PrimaryKey | Task Description | Sequence | Taskgroup | lots of other stuff that's not important

When they see the sequence number in the application, it is printed like so -- Taskgroup+Sequence. The taskgroups are A, B, C etc., sequence numbers are 25, 100, 1000, 5000, in whatever ascending order that makes sense to them.

Now they were *TOLD* it would make our lives easier if they kept the sequence numbers unique. Naturally, they ignored us. So we've got duplicates as far as sequences go, even though they are unique when paired with a task group.

My problem now is that I've got to try and run reports based on these groups. There are three groups:
A50 to A2800
A2800 to B2300
B2300 to B5700

I know how I would do this in Excel. I would just concatenate the sequence and taskgroup into a third column and do my work based off of that. I assume I will be doing something similar in Crystal to work through this problem, grouping by the concatenation of these two data fields. The only question is ... how?! I know this has to be fairly rudimentary but I'm still now sure. Thanks!

 

[TheBlondOne]

you can just write a formula which is like

if taskgroup+sequence in "A50" to "A2800" then "A50 to A2800" else
if taskgroup+sequence in "A2801" to "B2300" then "A2801 to B2300" etc...

You should then be able to group on that formula

HTH

-Steve


"if at first you don't succeed, sky diving is not for you"!!! )

 

[jollyreaper]

Ok, I must be braindead today.

I created the formula as you suggested. Construction wants three different reports for those value ranges -- the ranges corresponding to "front", "middle", and "back".

So I'm starting with the middle report first. I tell it to group on sequence and I tell it to use this formula when grouping:

if {CTASK.TASKGROUP} + {CTASK.SEQUENCE} in "A2800" to "B2300" then "middle"

It complains that "a string is required here" for CTASK.SEQUENCE. I checked the spelling to be on the safe side and it is correct. For whatever reason, I am not allowed to see query fields in this version of the formula editor. I click and drag the fields in usually to remove the possibility of typos.

I know this has to be obvious. What am I overlooking?

 

[TheBlondOne]

ok sorry thats my fault i was assuming that sequence was a string.

try this

if {CTASK.TASKGROUP} + totext({CTASK.SEQUENCE},0,"") in "A2800" to "B2300" then "middle"

let me know how it goes




-Steve


"if at first you don't succeed, sky diving is not for you"!!! )