DateTime Overlap 2

[mikeisvfp]

I am trying to add a new appointment to see if it will overlap with any existing ones in my grid columns, I have created a startdate field and enddate field in my appointment table. (data types are DatTime)

The key is to search startdate.alias and endate.alias to see if an overlap exists, If so, don’t permit the data entry and alert the user that the appointment date overlaps with another. Otherwise permit the new appointment entry.

I have a function provided by Olaf that I am using but i dont know how to code it so it works with the function below

Function Overlap(ttStartDateTime1,ttEndDateTime1,ttStartDateTime2,ttEndDateTime2)
Local llOverlap
llOverlap = ;
Between(ttStartDateTime2, ttStartDateTime1, ttEndDateTime1)OR;
Between(ttEndDateTime2 , ttStartDateTime1, ttEndDateTime1)
Return llOverlap EndCase

please help

Thanks

Mike

 

[Olaf Doschke]

EndCase? must have been a typo. That must be EndFunc. Besides that I don't believe those are the two BETWEENS I wrote.

Besides that you use that function by calling it. The 4 parameters are for two pairs of start and end datetimes, one of the new appointment, one of an appointment to check against.

So you will need to loop all appointments and then call the function for each one against the new appiontment. If one of the calls returns .T., you have an overlap with that appointment and the new appointment or the old one should be moved.

Bye, Olaf.

 

[Mike Lewis]

Mike,

First, I don't know what the "EndCase" is doing there (the very last word in the function). Looks like a pasting error. You will need to take it out.

To answer your question, my guess is that you should call this from the Valid event of the Textbox object within the Column object within the Grid object. If you fix it so that the Valid returns the same value as the function (.T. or .F.), it means the user will have to edit the data in the cell when an overlap is detected.

However, you will need to add a message to the user. If the function returns .F., you should advise the user of the overlap and tell them to fix the problem.

Since Olaf gave you the function in the first place, you might wait for his response before doing the above. If he tells you something different, then he's right and I'm wrong.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[mikeisvfp]

Sorry Olaf it might also be the way i copied and pasted the code..
Anyway, can you please show me how i would do this, Ive been going crazy trying to get this to work. To be honest Ive never done any looping before either.

 

[mikeisvfp]

Or can anyone please help me for this matter?

 

[JRB-Bldr]

The FUNCTION, by itself (without the ENDCASE and with a space before the OR) looks reasonably OK.

I do notice that you do not have a PARAMETERS line in the above code so as to accept the values passed from the Function call - most likely a typo on your part.

FUNCTION Overlap
PARAMETERS ttStartDateTime1,ttEndDateTime1,ttStartDateTime2,ttEndDateTime2   
* -- Check if #2 Start & End Date/Times is between #1 Start & End Date/Times ---
* --- If Either One is BETWEEN(), Return a TRUE (overlap exists) ---
Local llOverlap      
llOverlap = ;      
BETWEEN(ttStartDateTime2, ttStartDateTime1, ttEndDateTime1) OR ;      
BETWEEN(ttEndDateTime2 , ttStartDateTime1, ttEndDateTime1)    
RETURN llOverlap

But we don't know how you are using it to understand what is and what is not working.

Are you calling the function on the user clicking on a Button or something?
And are you populating the appropriate parameters BEFORE the function call?
Or what?

You might want to look into your VFP Help system for the BETWEEN() function to understand what is being done.

Good Luck,
JRB-Bldr

 

[mikeisvfp]

Well the idea is to call the function before the record is saved so i assume i will call it when the user clicks on the save button.
Also Olaf mentioned something i hadnt noticed, there are 4 parameters here and i have only two fields i am working with the startdate and enddate. im not sure the BETWEEN function will work with just startdate and enddate?

 

[JRB-Bldr]

im not sure the BETWEEN function will work

Have you gone into your VFP Help utility to learn how the BETWEEN() function operates?

there are 4 parameters here and i have only two fields i am working with the startdate and enddate

That is why I asked above...
Are you populating the appropriate parameters BEFORE the function call?

The FUNCTION, as written, is looking for 4 discrete PARAMETER values and will work on them as I indicated:
* -- Check if #2 Start & End Date/Times is between #1 Start & End Date/Times ---

If you do not have the 4 parameters appropriately defined and passed to the Function(), you cannot expect to get the results you want.

Good Luck,
JRB-Bldr

 

[Mike Gagnon]

JRB-Bldr
I see 4 parameters, what do you see?

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[JRB-Bldr]

Hi Mike - I also see 4 Parameters within the Function, but mikeisvfp is saying that he either isn't populating all 4 of them and/or he is not putting all 4 into his Function call - or something (even that's not clear).

Unfortunately his typo mistakes in the above code
Function Overlap(ttStartDateTime1,ttEndDateTime1,ttStartDateTime2,ttEndDateTime2)
continues the mystery - Did he just misplace the PARAMETERS line within the Function or did he show the Function call instead of the FUNCTION definition itself.

As I told him, I think he first needs to understand the BETWEEN() function and then use it as it is intended to work - and pass the necessary parameters to support it giving him the answer he wants.

mikeisvfp - Good Luck,
JRB-Bldr

 

[Olaf Doschke]

I already said, what the 4 parameters are for. You need two appointments to check one against the other. And each appointment has a start and end datetime 2*2=4 parameters.

pseudcode, just to illustratoe on how to use:

table appointments has all stored appointments (with start and end). variables newstart, newend have the new appointment start and end datetimes, then do:

select appointments
scan appointments for not (appointments.end<newstart or apointments.start>newend)
  if overlap(appointments.start,appointments.end,newstart,newend) or;
     overlap(newstart,newend,appointments.start,appointments.end)
    
     messagebox("there is an overlap with an existing appointment") 
  endif
endscan

Bye, Olaf.

 

[mikeisvfp]

Thank you Olaf, and sorry for the headache guys...

One small problem though i am getting the following error

SCAN appointments FOR NOT (appointments.end<newstart or appointments.start>newend)

command contains unrecognized phrase/keyword

 

[Olaf Doschke]

It should just be SCAN FOR ...

But you need to change that code to your table and field names anyway.

Bye, Olaf.

 

[JRB-Bldr]

As Olaf has said, the code you are trying to do should be:

SELECT appointments
SCAN FOR NOT (appointments.end < newstart OR appointments.start > newend)
  <do whatever>
ENDSCAN

Good Luck,
JRB-Bldr

 

[mikeisvfp]

Ok Im gettig somewhere, It works, only the loop really loops like I have to click the OK button like 10 times, is there anyway to stop this and have it loop just once

 

[JRB-Bldr]

You do realize of course that you could do the same thing without using the SCAN/ENDSCAN or that particular function call.

Once you had the proposed dtNewStart & dtNewEnd values defined you could run a SQL Query of the entire Appointments table to see if anything overlapped.

SELECT *;
 FROM Appointments;
 WHERE (BETWEEN(dtNewStart, Appointments.Start, Appointments.End);
 OR BETWEEN(dtNewEnd, Appointments.Start, Appointments.End));
 INTO CURSOR OverLaps READWRITE

SELECT OverLaps
IF RECCOUNT() > 0
   * --- Overlaps Exist ---
   <do whatever>
ELSE
   * --- No Overlaps Exist ---
   <do whatever>
ENDIF
USE

If you wanted, you that SQL Query could be put into its own FUNCTION or into a separate Form Method as long as the dtNewStart & dtNewEnd values were 'visible' to use within it in some manner.

Good Luck,
JRB-Bldr

 

[JRB-Bldr]

Opps...

My query criteria above did not include the possibility that BOTH the dtNewStart was earlier that the Appointments.Start AND the dtNewEnd was later than the Appointments.End.

Modified...

SELECT *;
 FROM Appointments;
 WHERE (BETWEEN(dtNewStart, Appointments.Start, Appointments.End);
 OR BETWEEN(dtNewEnd, Appointments.Start, Appointments.End));
 OR (dtNewStart < Appointments.Start AND dtNewEnd > Appointments.End);
 INTO CURSOR OverLaps READWRITE

And you can keep modifying the query criteria to include anything else that I might have overlooked.

Good Luck,
JRB-Bldr

 

[mikeisvfp]

JRBbldr Thank You, Thank You works like a charm.
And Im sorry ive been a pain in the ....

thanks once again guys

 

[Olaf Doschke]

You could simply exit the loop, once you found one entry overlappping. EXIT is the command for that. Put it directly after the messagebox.

Bye, Olaf.

 

[TamarGranor]

@JRBBldr

Unfortunately his typo mistakes in the above code
Function Overlap(ttStartDateTime1,ttEndDateTime1,ttStartDateTime2,ttEndDateTime2)
continues the mystery - Did he just misplace the PARAMETERS line within the Function or did he show the Function call instead of the FUNCTION definition itself.

You can define functions and procedures as he did by putting the parameters in the FUNC or PROC line. That automatically makes them local. That is:

FUNCTION MyFunc(a, b, c)

is the same as

FUNCTION MyFunc
LPARAMETERS a, b, c

I also want to add that PARAMETERS is, in my view, obsolete and should always be replaced by LPARAMETERS.

Tamar