7 Replies Latest reply on Jul 11, 2012 10:27 AM by MattLeach

    Compare Date across table.

    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

        • 1. Re: Compare Date across table.
          wimdecorte

          While both those approaches (relationship and find) would work you could also load the holidays in a multi-line variable (since they are static) at the beginning of the script and then just use the FilterValues function to check if your $setDate is in the holiday list.

          • 2. Re: Compare Date across table.
            MattLeach

            How would i load the holidays into a multi-line variable?

            • 3. Re: Compare Date across table.
              wimdecorte

              Check out the GetNthRecord function that allows you to loop through records without physically moving through records.  If you do that on the holidays table and append each holiday date to a variable,  you'll be all set.

              • 4. Re: Compare Date across table.
                MattLeach

                Thanks. While that would help for the holiday dates themselves, that does not do any checking on the field for working (yes/no)

                 

                Would there be a way to populate the multi-line list of holidays with those holidays which holiday::working = no?

                 

                Thanks,

                Matt

                • 5. Re: Compare Date across table.
                  greglane

                  Hi Matt,

                   

                  The ExecuteSQL function can be used to query matching records in the holiday table from any context. The following function would return the number of holiday records where  holiday::date = $setDate and holiday::working = "no".

                   

                      ExecuteSQL("SELECT count(*) FROM holiday WHERE \"date\" = ? and \"working\" = 'no'"; ""; ""; $setDate)

                   

                  Greg

                  • 6. Re: Compare Date across table.
                    comment

                    MattLeach wrote:

                     

                    Would there be a way to populate the multi-line list of holidays with those holidays which holiday::working = no?

                     

                    You could define a relationship matching a global/calculation field populated as "no" to the holiday::working field. You could  constrain the related set further by excluding holidays before StartDate, and perhaps even by setting an upper limit (a generous one, since you'll be skipping forward 7 days for each intervening holiday).

                     

                     

                    Once you have the relationship in place, the "multi-line list of holidays" will be given by =

                     

                    List ( Holidays::HolidayDate )

                     

                    and you can use the FilterValues() function against this list to check each date in turn.

                    1 of 1 people found this helpful
                    • 7. Re: Compare Date across table.
                      MattLeach

                      Thanks guys.

                       

                      I was able to get the end result by combining wimdecorte and Michael Horak's ideas.