Crystal Reports XI – Finding records that need to have appointments rescheduled

40 pts.
Tags:
Crystal Reports Record
Crystal Reports XI
Hi, I have the following fields to work with: ID #, issue #, meeting #, prior meeting dates, and status. I keep isolating the old records that have already been rescheduled along with the ones that need to be rescheduled. Any ideas would be appreciated. Thanks! Babeas

Software/Hardware used:
Crystal Reports XI

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 12  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    babeas, posting the question again will not help if you don't add the necessary details. How can we suggest a solution if we don't know how you decide when a record needs to be rescheduled or how you identify those that have already been rescheduled? We could try to guess, or assume, but, why would we do that? Please, if you really need help, put some more effort in describing your situation, and I'm sure people will try to help you.
    69,475 pointsBadges:
    report
  • babeas
    A meeting needs to be rescheduled when it has been approved for rescheduling. This occurs when someone calls to request a reschedule or subject matter experts are not available for the meeting. When approved for reschedule the Officer ID becomes 987 or Officer Name XXX. In addition they should have a null disposition.I can pull all of the appeals that have 987/XXX and a null disposition, but it eliminates the new hearing date. I have tried hearing_date =currentdate -7, and that works some. However, it would be best to reschedule as soon as they become 987/XXX.Isnull({DISPOSITION.DISPOSITION}) and{HEARING.HEARING_DATE}>=CurrentDate-7 and{HEARING.OFFICERID} = 987 orIsnull({DISPOSITION.DISPOSITION}) and{HEARING.HEARING_DATE}>=CurrentDate-7 and{HEARINGOFFICER.HEARING_OFFICER}=["XXX","xxx"]This is coming from a 20 year old system that is currently being rebuilt, but we are trying to isolate records electronically for the time being. The 987 and XXX are key to something being rescheduled. However, they can be rescheduled several times. Hopefully, this was enough information. Thanks for your feedback!Babeas
    40 pointsBadges:
    report
  • carlosdl
    My new comments are not being displayed...
    69,475 pointsBadges:
    report
  • carlosdl
    Will try again, separating it in smaller parts, to see which one is the problematic one.
    69,475 pointsBadges:
    report
  • carlosdl
    This new info clarifies it a lot, thank you.
    69,475 pointsBadges:
    report
  • carlosdl
    I'm still not sure to understand why pulling all of the appeals that have 987/"x x x" and null disposition eliminates the new hearing date.
    69,475 pointsBadges:
    report
  • carlosdl
    Off-topic: It seems that the "x-x-x" is the culprit.
    69,475 pointsBadges:
    report
  • carlosdl
    Also, what is the logic behind that "-7" in your conditions? When a meeting has been approved for rescheduling (i.e. it has been modified to have 987/"x x x"/null), is there something that changes when it has already been rescheduled, that you could use to differentiate them?
    69,475 pointsBadges:
    report
  • babeas
    Correct! The 987 = XXX for the officer name in two different tables, so either code would work if used. The 987 is entered for the hearing officer ID to show that the record needs rescheduled.There are two ways to add a new date, and I think that may be adding to whether or not the 987 is replaced or stays on record. When  the new hearing is scheduled, a new hearing officer ID is assigned with the new hearing date. Some where behind the scenes the record shows up on the screen as a "Y" = yes for rescheduled. However, I have the table layout, and this field is no where to be found. Plus, it can still happen mulitple times because it does not go away.The {Hearing.hearing_date}= <currentdate - 7 is merely to allow some time for dispositions to be added after the hearing date. This is an issue too, for some decisions are deliquent. They can be dealt with because they should have been taken care of already.I am trying the following, but I will need to check several records in the morning to see if it is working. Isnull({DISPOSITION.DISPOSITION}) andnot (isnull({HEARING.HEARING_DATE}))and{HEARING.HEARING_DATE}<=CurrentDate I removed the 987 because it tends to show me the old record that needed rescheduled, and it eliminates the new record that was rescheduled because it is no longer equals 987.In a brief review of the results, it appears that I am getting records for hearings that have passed because they do not have a disposition. I will check to see if there is a field that states they will be writing a decision. If there is a field, then we may have figured it out.Thanks again for your help and patience.
    40 pointsBadges:
    report
  • carlosdl
    Isn't there a field like a case number or something similar that identifies the case or the process itself, and not a specific hearing ? If possible, it could be a good idea posting the table structure, and also letting us know the database you are using (some SQL constructs might not be supported by all database products). 
    69,475 pointsBadges:
    report
  • babeas
    Tables - relevant fieldsAppeal - appeal #, client ID, Assistance_Program, and the Request_DateHearing - This contains the date, officer and other information for each hearing the is scheduled. The Maximum ({Hearing.Hearing_Date}) needs to also have the Officer ID of 987 to be relevant for this report. The issue has been that if we isolate the 987 records then it ignores the newest hearing date, which means that the hearing has already been rescheduled.HearingAppeal - Connects the Hearing and Appeal tables. It also contains the type of hearings. (O-original, R-Rescheduled) Hearings can be rescheduled more than once. The main reason for this table is to connect the two main tables.HearingOfficer - officer ID = 987, officer name = XXXDisposition - Disposition should be nullDecisionStatus - status id, officer id, decisionstatus (P=None, N=New, S=Submitted, A=Approved)Client - case # and county #County - county name (Scheduling is grouped by county name)ActivityDescription - Appeal # connects to record comments. Rescheduling information is usually put in the lowest appeal, and it is helpful for scheduling.Things needed to make this report accurate:Need to use Maximum ({Hearing.Hearing_Date}) to isolate the most recent hearing date.Most recent hearing date should have {HearingOfficer.OfficerID}=987.I have a jpeg and PDF of my tables, but I am not sure how to get them to you.
    40 pointsBadges:
    report
  • carlosdl
    Sorry for the late response.  It is really difficult to understand the tables structure here, since the editor removes any formatting and line breaks.  If you have a jpg image, you can upload it to any image hosting site, and then post the link here.
    69,475 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following