Format Time and show last record 2

[kcoleman26]

Crystal XI

I am working on a report that will show me the last record for a room.

My Time comes from iseries in military time as a packed number 152129 is basically 3:21.29pm I really don't even need the seconds just the military time formatted 15:21.

I only want to see the last record for a given room number if the Status of that room is 5(5=GRA in Room).

The display would be
Room # Status Time
C1012 GRA in Room 15:30

Thanks,
KC

 

[PatriciaObreja]

Using the Select Expert select only the records that have Status = 5.

Format Time field to show only hours and minutes.

Order the records desscending by Time.

Group records by Room number.

Place the fields from Details section into Group Header, and suppress the Details section.

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[lbass]

To convert the number to a time, use:

stringvar x := totext({table.number},0,"");
time(val(left(x,2)),val(mid(x,3,2)),val(right(x,2)))

Then format the resulting time to just show hours and minutes. This assumes the number is always 6 digits.

-LB

 

[kcoleman26]

Thanks lbass

PatriciaObreja,
By using the select expert to only show status = 5. I am getting EveryRoom and the time that it went to the 5 status.

Example1:
C1021 Clean 7:35
C1021 GRA in Room 6:55
C1021 Vacant/Dirty 5:00

By selecting status 5 I am pulling the room number C1021 GRA in Room 6:55. I wouldn't want to see this room in my report.

Example2:
C1022 GRA in Room 8:55
C1022 Vacant/Dirty 6:30

I would want to see this room because the last entry of the Room number is 5(GRA in Room)

Thanks,
KC

 

[lbass]

Assuming you have a group#1 on date or are limiting the records to one date, you could insert a group on room# and then go to report->selection formula->GROUP and enter:

{@time} = maximum({@time},{table.room}) and
{table.status} = 5

-LB

 

[kcoleman26]

lbass - Almost there

Here is the formula you gave me.
stringvar x := totext({HTP.RCTMHT},0,"");
time(val(left(x,2)),val(mid(x,3,2)),val(right(x,2)))


It says no errors found but when I save and exit I get a pop up box that says Hour must be between 0 and 23.

Thanks,
KC

 

[lbass]

Is the field ever null? Is it ever shorter than 6 digits?

Try changing the formula to the following:

stringvar x := totext({HTP.RCTMHT},"000000");
time(val(left(x,2)),val(mid(x,3,2)),val(right(x,2)))

-LB

 

[kcoleman26]

Beautiful!!! thanks lbass...It took me while I made all of the changes and forgot to save, so I had to recreate the report. (hate it when that happens)

KC

 

[kcoleman26]

Last thing on this subject I have many more I have yet to figure out. Still learning....

Now I would like to display HH:MM the difference between currenttime and In-Time.

I have {@f_Currenttime} - {@f_Time} and my results are

RESULTS:
Time Currenttime Difference
9:20:08am 9:30:11 603

DESIRED RESULTS:
Time Currenttime Difference
9:20:08am 9:30:11 0:10


{@f_Time}formula is:
stringvar x := totext({HTP.RCTMHT},"000000");
time(val(left(x,2)),val(mid(x,3,2)),val(right(x,2)))

Thanks,
KC

 

[PatriciaObreja]

ToText(Truncate(603/3600), 0, "") + ":" + ToText(Truncate(603/60), 0, "")

Will give you:
0:10

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[kcoleman26]

Now I am getting 0:10 for all of my results. How can I get the difference between Time and Currenttime

KC

 

[PatriciaObreja]

In the formula that I gave you please replace "603" with your difference.

ToText(Truncate(({@f_Currenttime} - {@f_Time})/3600), 0, "") + ":" + ToText(Truncate(( {@f_Currenttime} - {@f_Time})/60), 0, "")

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[lbass]

What is the formula for currenttime? Assuming they are both times within the same date, you should be doing the time difference calculation like this:

datediff("s",datetime(currentdate,{@f_time}), datetime(currentdate,{@f_currenttime}))

Then use faq767-3543 to convert this (set it = dur) into the string display hh:mm.

-LB

 

[kcoleman26]

1. lbass this formula works if I already have the data in the report and then I just add the field. When I promt for new parameters I get an error "HOURS MUST BE BETWEEN 0 AND 23".
stringvar x := totext({HTP.RCTMHT},"000000");
time(val(left(x,2)),val(mid(x,3,2)),val(right(x,2)))

2. My formula for currenttime is the function currenttime which displays 3:22:25PM

 

[lbass]

Please show a sample of the {HTP.RCTMHT} field. Can it ever be null?

-LB

 

[kcoleman26]

HTP.RCTMHT Data

0.00 (start value)
1,000.00
2,000.00
3,000.00
...
957,000.00
958,000.00
959,000.00 (end value)

 

[lbass]

What are the corresponding time values for these? In your first post, you said the the field was a packed number, and that 152129 is basically 3:21.29pm, and that does make sense--15 becomes 3PM, 21 = minutes, 29 = seconds. But there is no way this works for your samples of 957000, 958000,959000. So what times do they correspond to?

-LB

 

[kcoleman26]

That is strange when I right click on the field from the field explorer and browse data those were the numbers I got. However when I insert the {HTP.RCTMHT} field into my report it reads 152129 so I am not really sure where the browse data is coming from.

It is just strange that the formula you gave will work if I insert it after I run the data. Inserted before I run the data I get the error.

KC

 

[lbass]

Try this:

stringvar x;
if isnull({HTP.RCTMHT}) then
x := "000000" else
x := totext({HTP.RCTMHT},"000000");
time(val(left(x,2)),val(mid(x,3,2)),val(right(x,2)))

Then change your datediff formula to:

if {@f_time} = "000000" then 0 else
datediff("s",datetime(currentdate,{@f_time}), datetime(currentdate,{@f_currenttime}))

This would give a 0 if there was a null value.

I also think you should test the field HTP.RCTMHT by placing it in a new report in the detail section and inserting minimums and maximums on it to see if all possible values make sense.

-LB

 

[kcoleman26]

still not working I will try again on Monday I am done with this for today.....