AnsweredAssumed Answered

Compare Date across table.

Question asked by MattLeach on Jul 10, 2012
Latest reply on Jul 11, 2012 by MattLeach

I have a scheduling database that allows the user to schedule appointments for multiple days. The user selects the date for the first appointment and specifies how many appointments they would like scheduled.

 

Here is the script i currently have that schedules muiltiple appointments:

 

If [ IsEmpty ( Visits::Account ) ]

Show Custom Dialog [ Title: "Notice!"; Message: "An Account must be selected before saving this appointment!";Default Button: “OK”, Commit: “No” ]

Exit Script [ ]

End If

If [ IsEmpty ( Visits::Multi_RepeatNum ) ]

Show Custom Dialog [ Title: "Notice!"; Message: "You must specify the number of future appointments to be made."; Default Button: “OK”, Commit: “No” ]

Exit Script [ ]

End If

Show Custom Dialog [ Title: "Confirmation"; Message: "You are about to schedule an appointment every " & DayName ( Visits::DateStart ) & " for the next " & Visits::Multi_RepeatNum & " weeks. Are you sure you want to continue?"; Default Button: “OK”, Commit: “No”; Button 2: “Cancel”, Commit: “No” ]

If [ Get ( LastMessageChoice ) = 2 ]

Exit Script [ ]

Else

Set Variable [ $MultiID; Value:Visits::_pk_VisitID & Visits::_fk_ClientID & Visits::Multi_RepeatNum ]

Set Field [ Visits::MultiKey; $MultiID ]

Commit Records/Requests [ Skip data entry validation; No dialog; Force Commit ]

Set Variable [ $ClientID; Value:Visits::_fk_ClientID ]

Set Variable [ $ClientName; Value:Visits::ClientName ]

Set Variable [ $AccID; Value:Visits::Account ]

Set Variable [ $SetDate; Value:Visits::DateStart ]

Set Variable [ $SetTime; Value:Visits::TimeStart ]

Set Variable [ $MultiCount; Value:Visits::Multi_RepeatNum ]

Set Variable [ $counter; Value:0 ]

#

New Window [ Name: "MultiAdd"; Height: 1; Width: 1; Left: -5000; Style: Document; Close: “Yes”; Minimize: “Yes”; Maximize: “Yes”; Zoom Control Area: “Yes”; Resize: “Yes” ]

Go to Layout [ “VisitEntry_Client_FR” (Visits) ]

Loop

Exit Loop If [ $counter = $MultiCount ]

New Record/Request

Set Field [ Visits::_fk_ClientID; $ClientID ]

Set Field [ Visits::ClientName; $ClientName ]

Set Field [ Visits::Account; $AccID ]

Set Field [ Visits::DateStart; $SetDate + 7 ]

Set Field [ Visits::TimeStart; $SetTime ]

Set Field [ Visits::MultiKey; $MultiID ]

Commit Records/Requests [ Skip data entry validation; No dialog; Force Commit ]

Set Variable [ $counter; Value:$counter + 1 ]

Set Variable [ $SetDate; Value:Visits::DateStart ]

End Loop

Close Window [ Name: "MultiAdd"; Current file ]

End If

Close Window [ Name: "Add Multiple Patient Visits"; Current file ]

 

 

I also have a table for holidays, There is a field for the date and a working field so they can set whether they are open or not.

 

What i would like to accomplish is during the Loop of setting the appointments, compare the $SetDate (which will increase by 7 days each loop) to the holiday table to see if a holiday exists and if it does, check the working field is it is set to Yes or No. Would i need some type of relationship between the visits table and the holiday table or use an off screen find?

 

Any assistance would be appreicated.

 

Thanks,

Matt

Outcomes