Time Overlap problem 1

[mikeisvfp]

Hello experts,

Ive been thinking my hole Time overlap through and I noticed a small problem. If a customer cancels appointment, I wouldnt want the user to have the ability to delete the appointment. Rather then delete i decided to add a status called CANCELLED, but if this appointment is cancelled with anticipation other customers must be able to book for that time period without overlap. How would I do this



here is the time overlap code

SELECT *;
FROM Appointment;
WHERE dtNewStart < Appointment.Enddate AND ;
dtNewEnd > Appointment.Startdate AND ;
dtNewstart <> Appointment.StartDate ; && Uniquely identifies current record &&
INTO CURSOR OverLaps READWRITE


SELECT OverLaps
IF RECCOUNT() > 0
MESSAGEBOX('there is an overlap with an existing appointment' ,16, 'Time Overlap')
ELSE
thisform.save()
ENDIF
ENDCASE
so basically i need to say something like

IF BILLING.STATUS = 'CANCELLED'
then this time is not a overlap

Please Help

 

[Dan Freeman]

Help with what?

 

[GriffMG]

I'm thinking he wants to exclude any records with a cancelled billing status from his overlaps table...

But it's not very clear, and what is the ENDCASE for?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

Yes, I think Griff is right. He needs to add BILLING.STATUS <>'CANCELLED' to the definition of an overlap.

But it's hard to be sure. The question is somewhat muddled.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[GriffMG]

I think he needs to do a join to a billing table, on a key we can't see at the moment, and then include the 'and billing.status<>"CANCELLED"' in his selection criteria.

Select appointment.*, billing.status from appointment, billing WHERE dtNewStart < Appointment.Enddate AND dtNewEnd > Appointment.Startdate AND dtNewstart <> Appointment.StartDate and billing.UnknownKeyField = appointment.UnKnownKeyField and billing.status <> 'CANCELLED' INTO CURSOR OverLaps READWRITE

Not sure the READWRITE option is necessary, or workable in this context though.

Good luck

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

This could be one of those threads where we all try to guess what the original question is about, only to find that it turns out to be something quite different.

My advice to Mikeisvfp would be to try to get a clear idea in your mind about exactly what your problem is, and then try to explain it in terms that an outsider would understand.

If you do that, I'm sure you'll get some good answers here.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[GriffMG]

A 'Guess the Question' question

B-)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

Simply avoid all cancelled appointments in the initial sql select, then you don't need to analyse the result records, as they only overlap with any non cancelled appointment anyway.

Bye, Olaf.

 

[mikeisvfp]

Thanks Guys GriffMG is right

Thank you all once again