How to look at the next two records (cascaded next?) 1

[rleyba828]

I am using Crystal reports version 8. We have to map out RJ 45 floor ports ("A" for Phone, "B" for data and "c" for spare) to a particular staff "Name" Since we imported a flat file spreadsheet, the name is only paired with either the "A or "B" or the "C" row in the spreadsheet. That means two out of every three rows in the spreadsheet will have its "Name" column blank. I would like to populate all the Blank A or B or C fields so that the name would appear in all of them.

So, how to program Crystal to check if the first record "A" has a name or is blank. if Blank, then check the next record (B) and copy the name from there. If that is blank too then go to the next record (C) and copy the name from there...In other words, two records ahead.

Is this possible?

thanks very much

 

[CR85user]

I'm sure that the ideal answer for this would be to add the table again as an alias to bring in the relevant data.

Just off the top of my head I would try something like the following:

(
if isnull({table.name}) or {table.name} like '' then
(
if nextisnull({table.name}) or next({table.name}) like '' then
*Not sure on logic for retreiving field for next + 1*

else
next({table.name})
)
else {table.name}
)

I'm afraid I can't think at the minute of the best method for retreiving the data for the value of the field where the recordnumber = current +2 but I will have a play later on. Possibly we could create an array and then split it accordingly. I'm sure someone else may be having less of a dim day than me

'J

 

[lbass]

Do you have a field that IS populated for every row and that corresponds to the name, e.g., an ID field? In other words is there some field that is consistent per set of name/ABC that allows you to know that the data belongs to a specific set? Please show some sample data.

-LB

 

[rleyba828]

Guys, thanks very much for the replies.

lbass, to answer your question, YES there is a common field. Here is sample of the data

Port Staff Desk-ID
11A 007
11B Employee1 007
11C 007
12A Employee2 009
12B 009
12C 009
13A 011
13B 011
13C Employee3 011

In this sample, every SET of ports A, B, C always belong to one Desk ID. What makes things a bit harder for me though is that the Staff field doesn't always fall on the record containing the "A" port. Sometimes it lines up with the "B" port record and sometimes on the "C".

My objective is to populate EVERY "Staff" field with the name corresponding to that set, so that none of the "staff" fields are blank.

Thanks very much.

 

[lbass]

Insert a group on deskID (you can suppress the group header and footer, and then create a formula {@employee}:

maximum({table.staff},{table.deskID})

Place this in the detail section instead of {table.staff}.

-LB

 

[rleyba828]

Hi lbass, This is great! We are almost there. I got the results I needed...as in all "staff" fields are now populated. The only problem is because they are grouped by deskID, the list is sorted by Desk ID. How Do I sort the list by "staff"?

Also, this is the first time I have seen the maximum function used this way. What exactly is this function maximum({table.staff},{table.deskID}) doing in this case?

Thanks again

 

[lbass]

Since the only two results for {table.staff} are the employee name or null, the maximum per group, picks up the employee name. The formula itself just displays the maximum employee name value per deskID group.

Try the following for sorting. Right click on the staff field and insert a maximum at the deskID group level (this activates the topN/group sort feature). Then go to report->topN/group sort, and choose the deskID group tab->select "All"->maximum of {table.staff}->ascending.

-LB

 

[rleyba828]

LB, it works! Absolultely amazing! Thanks