maximum date and time status 1

[codrutza]

CR XI

Pls help

I have for a group job, records like this:
Job 1…
Status Date Time
Blue 12/08/2011 15:55
Red 12/08/2011 15:29
Green 11/08/2011 14:49
Orange 09/08/2011 10:11
Yel 11/08/2011 14:30
Green 11/08/2011 14:49
Job 2….

How do I get the status for maximum date and time in the group, as it would be for this case:

Job1 Blue

I know maximum(date,job) and maximum(time,job) but I don’t know how to make the connection with status

 

[lbass]

If you sort by date in descending order, you can just place the status field in the group header and it will show the correct value.

-LB

 

[codrutza]

But for some recors it's the same date with different status; I noticed just now that are records with the same date and with the same time and different status...

 

[lbass]

So if there are two records that meet your criterion, what is your rule for which one should display?

-LB

 

[codrutza]

The time I guess. (The date plus the time).
But I tried to find myself a new criteria. I found out from my boss that there are three statuses which are important red, orange and green. They can be more statuses which are green, more statuses that are orange and more statuses that are red for one job, but not more than 10. The ultimate status would be green. So I put three formulas: @red: if status=red then 1, @orange: if status=orange then 10, @green: if status=green then 100; I sum them in group footer @sumcolour and I made another formula @colour: if @sumcolour >=100 then "Green" else if @sumcolour <100 and @sumcolour>10 then "Orange" else if @sumcolour <10 then "Red". The rest of statuses they are not important.
That idea of your is great, if you consider somehow the date and the time; they are 2 separate fields.
What do you think about my idea?

 

[lbass]

I think you should just create a formula that orders the color in order of priority, e.g.,

select {table.color}
case "green" : 1
case "orange" : 2
case "red" : 3
default : 4

Sort first on a datetime formula in descending order:

datetime({table.date},{table.time})

...and secondly on the color formula in ascending order, and then again, just place the fields in the job group header.

-LB