Temporary Tables- A Simple Solution

By Jennifer Neighbors, Senior Consultant

This week I discovered a very simple solution to a couple of challenges that initially had me stumped. What I stumbled upon in my effort to solve a couple of puzzling programming problems is quite simple to put into place, requires no fancy programming to learn and can be used by any Ninox beginner. I call my solution “temporary tables”. These are tables that hold records only for a short period of time while you need them for a specific purpose. They aren’t a part of the database architecture at all. They are merely containers.

 Here’s what I mean: I had a report based on records in a table. Upon implementation of my database there are going to be hundreds of records in the table. I needed a subset of those records as the “line items” in my report because a report based on the entire table would be many pages long; too overwhelming to be useful. I needed to find a way to narrow them down. As a programmer, I can apply date filters manually to narrow the records to a date range. But which date range? And, could I expect novice users to go into the report layout screen and apply a filter there?

 I decided to try creating an invisible temporary table, and, based on the user’s own entered date range, and populate that table each time the report was run. The temporary table, not the original table, would form my “line items” detail on the report. I placed a button on the screen with along with a beginning and ending date for the report. When the user pressed the button, the code procedure created records in the temporary table that fit the defined date range. Then, it generated an email to the linked customer with the report attached and deleted all the records in the temporary table. Success! I decided to keep this in mind; it seemed like a handy approach.

 A few days afterward, another puzzler appeared in my programming life: My client, a music store, needed a visual display of available half-hour time slots for student lessons. Each teacher worked a different set of hours on different days of the week and their schedules were already partly filled with lessons. So, my display had to show a specific teacher’s unique daily schedule with half-hour time slots available (or not available). This would help the user quickly see when another student could be booked in. Here again, I was able to use a temporary table to solve my dilemma.

 I made a very simple invisible table with columns for the lesson date, the start time, the end time and a Yes/No field for availability. Then I put a button on my form and a view. The view simply showed the temporary table’s records. The first thing the button’s “on-click” code did was to delete any records already in the temporary table. Then, using stored work start and work stop times for each day of the week in the teacher’s table, the code populated the temporary table with records: one for each half-hour time slot in that teacher’s working day. I used more code to compare the start and end time in each record with the records in Calendar Plus’s hidden event table. I inserted “Yes” if no conflict was found and inserted “No” if it was. The view quickly filled with a useful display of that teacher’s schedule. Later I used formatting to make the available time slots colored green and the others red.

Here’s how my code works:

* if ‘LESSON DATE’ then     

     delete (select ‘AVAILABLE TIME SLOTS’);

     let xCurrRec := number(Id);

     let xDate := date(‘LESSON DATE’);

     let xTeacherName := TEACHER.’Full Name FNF’;

     let TStart := number(‘Teacher Start Time’);

     let TEnd := number(‘Teacher End Time’);

     let j := do as server

          let xNmbrHalfHourSessons := (TEnd – TStart) / 1800000;

               for x in range(0, xNmbrHalfHourSessons) do

                    let i := (create ‘AVAILABLE TIME SLOTS’);

                    i.(SESSIONS := xCurrRec);

                    i.(‘Lesson Date’ := xDate);

                    i.(‘Start Time’ := TStart + 1800000 * x);

                    i.(‘End Time’ := time(number(i.’Start Time’) + 1800000));

                    i.(Available := “Yes”)

               end

     end;
     for j in select ‘AVAILABLE TIME SLOTS’ do

          let s := (select ‘CALENDAR PLUS ENTRIES’)[date(‘START DATE’) = xDate and 

date(‘END DATE’) = xDate and PARTICIPANTS like xTeacherName];

           let a := count(s[‘START TIME’ <= j.’Start Time’ and ‘END TIME’ > j.’Start Time’]);

           let b := count(s[‘START TIME’ >= j.’Start Time’ and ‘END TIME’ <= j.’End Time’]);

          let c := count(s[‘START TIME’ >= j.’Start Time’ and ‘END TIME’ < j.’End Time’]); 

          let d := a + b + c; 

          if d > 0 then

               j.(Available := “No”)

          end

     end
else

     alert(“Please enter a lesson date.”) 

end

In this instance my temporary table is named “Available Time Slots”. Its field names are “Lesson
Date” (a date field), “Start Time” (a time field), “End Time” (a time field) and “Available” (a yes/no field). First, I delete all the records in the table. Then, I loop through and create one record in this table for each half-hour interval and then populated the fields accordingly. Another loop evaluates each record for conflicts with calendar entries and sets the “Available” field to “No” when a conflict is found. Do you wonder why some lines of code refer to the number 1800000? It’s because that is the number of milliseconds in a half-hour block of time. Having calculated that, I can easily figure out how many time slots are in a specific teacher’s schedule for the selected day.

I am taking advantage here of Calendar Plus’s hidden table named “Calendar Plus Entries” to interrogate the calendar. The calendar’s “Start Date”, “End Date”, “Start Time”, “End Time”, and “Participants” fields make this code block possible.

 Notice that only a portion of this code is executed on the server. That is because date calculations and alerts don’t work well there. Even placing part of this code on the server, however, speeds it up considerably.

 Keep in mind that tables can be more than database structural elements. Try using them as temporary containers when you need to store records for a specific purpose to get things done.

 *Thanks go to David Gyenes for explaining to me how to evaluate each time slot for conflicts using the variables “a”, “b”, and “c”.

en_USEN