Active TopicsMemberlistSearchHelpRegisterLogin

Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Tips and Tricks
Message Icon Topic: Display Truncated Appt Time Post Reply Post New Topic
Author Message
kaupang
Newbie
Newbie
Avatar

Joined: 14 Jul 2010
Location: United States
Online Status: Offline
Posts: 11
Quote kaupang Replybullet Topic: Display Truncated Appt Time
    Posted: 15 Jul 2010 at 5:19am
Hello,

I am working with manipulating an existing report.  The report generates appointments based upon parameters entered by the end-user.  One of the parameters is an AM or PM session.  If the AM session is chosen, then I want it to display only the appointments from the AM, and vice versa.

In order to display the start time of the appointment, this formula is used:

 if Truncate ({APPOINT.From} / 60 )  > 12.00 then
        ToText ((Truncate ({APPOINT.From} / 60 )) - 12,"00") +":"+ToText (Remainder ({APPOINT.From},60 ),"00") + "PM"
    else if Truncate ({APPOINT.From} / 60 )  = 12.00 then
        ToText (Truncate ({APPOINT.From} / 60 ),"00") +":"+ToText (Remainder ({APPOINT.From},60 ),"00") + "PM"
    else
        ToText (Truncate ({APPOINT.From} / 60 ),"00") +":"+ToText (Remainder ({APPOINT.From},60 ),"00") + "AM"

Is there a simple formula that can make it only display AM appointments when the AM parameter is selected?

Thanks in advance and let me know if I'm posting in the wrong spot.
Kaupang
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Jul 2010 at 5:32am
Is your data source a datetime or time field that defines the appointment time?
if so you can use the select expert something like:
({Param?}='AM' and time(table.appointmenttime) in time({ACTIVITIESHISTORY.STARTTIME}) in time(00,00,01) to time(11,59,59))
or
({Param?}='PM' and time(table.appointmenttime) in time({ACTIVITIESHISTORY.STARTTIME}) in time(12,00,00) to time(23,59,59))
IP IP Logged
kaupang
Newbie
Newbie
Avatar

Joined: 14 Jul 2010
Location: United States
Online Status: Offline
Posts: 11
Quote kaupang Replybullet Posted: 15 Jul 2010 at 5:47am
Hello,

Unfortunately, the data source is a Number. Angry For example, 510, 720, or 900. 

Thanks,
Kaupang
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Jul 2010 at 5:50am
what does the number represent in order to convert it into a time?

Edited by DBlank - 15 Jul 2010 at 5:50am
IP IP Logged
kaupang
Newbie
Newbie
Avatar

Joined: 14 Jul 2010
Location: United States
Online Status: Offline
Posts: 11
Quote kaupang Replybullet Posted: 15 Jul 2010 at 5:54am
It represents the minute in the day, I think.  I can browse the data and it has numbers that go up in intervals of 5, for example 600.00, 605.00, 610.00, 615.00.
Kaupang
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Jul 2010 at 6:18am
From the other formula I would agree that it is number of minutes from midnight.
this should work if that is the case:
({Param?}='AM' and {APPOINT.From} < 720)
or
({Param?}='PM' and {APPOINT.From} >= 720)
IP IP Logged
kaupang
Newbie
Newbie
Avatar

Joined: 14 Jul 2010
Location: United States
Online Status: Offline
Posts: 11
Quote kaupang Replybullet Posted: 15 Jul 2010 at 8:32am
Hello,

I've been messing around with this for a few hours now.  I think I'm stuck. 

I inserted the formula into my report to see that it gives a Boolean value, which is great, thanks!  Big%20smile  However, I'm confused as to where I could incorporate it into the report.  I think that I'm looking for it to truncate, or not display, appt times that are from the opposite session, AM or PM. 

For example, with this formula, it displays the appt time.  If the appt time in APPOINT.From is 540, it displays as 09:00 AM:

 if Truncate ({APPOINT.From} / 60 )  > 12.00 then
        ToText ((Truncate ({APPOINT.From} / 60 )) - 12,"00") +":"+ToText (Remainder ({APPOINT.From},60 ),"00") + "PM"
    else if Truncate ({APPOINT.From} / 60 )  = 12.00 then
        ToText (Truncate ({APPOINT.From} / 60 ),"00") +":"+ToText (Remainder ({APPOINT.From},60 ),"00") + "PM"
    else
        ToText (Truncate ({APPOINT.From} / 60 ),"00") +":"+ToText (Remainder ({APPOINT.From},60 ),"00") + "AM"

Same with 900 displays 03:00 PM.

Using the formula below, how would I get the report to not display PM session ({?Session}) times when the AM session parameter ({?Session}) is selected?

({?Session}='AM' and {APPOINT.From} < 720)
or
({?Session}='PM' and {APPOINT.From} >= 720)

Thanks,
Kaupang
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Jul 2010 at 9:12am
just stick it in the select expert and it will filter out the unwanted data from the report.
IP IP Logged
kaupang
Newbie
Newbie
Avatar

Joined: 14 Jul 2010
Location: United States
Online Status: Offline
Posts: 11
Quote kaupang Replybullet Posted: 16 Jul 2010 at 8:07am
That's awesome!  I had to play around with the Select Expert, but that worked like a charm.  Thanks again!!!

Thumbs%20Up
Big%20smile
Kaupang
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.