Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Articles
Search: 

How to display a timetable in a browse window using SQL & Genxtab
Christian Desbourse, January 1, 2001
Our sample data consists of two tables, an employee table and a table defining the timetable we want to display. The timetable is defined with the starting time (date, hour and minutes) and the employee in charge (hempl_id) of a number of time slot allocations of 30 minutes each. The data usually co...
Summary
Our sample data consists of two tables, an employee table and a table defining the timetable we want to display. The timetable is defined with the starting time (date, hour and minutes) and the employee in charge (hempl_id) of a number of time slot allocations of 30 minutes each. The data usually cover a one-week period starting on any day of the week. The time of the day covered can extend from 0.00 to 23.30.
Description
Our sample data consists of two tables, an employee table and a table defining the timetable we want to display. The timetable is defined with the starting time (date, hour and minutes) and the employee in charge (hempl_id) of a number of time slot allocations of 30 minutes each. The data usually cover a one-week period starting on any day of the week. The time of the day covered can extend from 0.00 to 23.30.

Genxtab.prg is a utility program which you can find in your Foxpro directory. Genxtab will generate a cross-tabulation from a table containing at least three fields corresponding to the following data :

the row field : row identifiers
the column field : column headings
the cell field : cell value corresponding to the row/column intersection

In our example, a first approach would be using employee id as row identifier, and time as column heading. If you look at the final display of the timetable, you will notice that more information has been included, as Name, First Name and initials to identify employees, that additional lines have been included to identify the different dates in the timetable, and that column headings have been simplified to indicate the hours only.

Additionally, some time slots may exist in the data file, without any employee allocated. In that case, we want to replace the employee name with a series of question marks.

A last thing to notice is that Genxtab will only work with numeric cell values and caracter type row identifiers and column headings.

Preparing the data for Genxtab

Two groups of data will be needed, the first one corresponding to the ‘employee’ lines, the second to the ‘day of the week’ lines. These two groups will be built using SELECT SQL and UNION :

before, we have to determine the starting date of the timetable :

CALCULATE MIN(ha_date) TO ld_start
and generate an array containing employee IDs and Name, First Name, Initials:
SELECT DISTINCT empl_id, ;
    PROPER(ALLTRIM(nom))+' '+PROPER(ALLTRIM(prenom))+' ['+init+']' AS empl ;
    FROM employee INTO ARRAY e_id ;
    ORDER BY nom ASCENDING
When you generate an array using SELECT SQL, it is a good practice to define an empty array in case no records have been processed by the SELECT, which you can determine using _tally, thus avoiding the situation where the array does not exist.
    IF _tally = 0
      DIMENSION e_id(1,2)
      e_id(1,1) = SPACE(6)
      e_id(1,2) = SPACE(10)
    ENDIF
The data we need in our first temporary table (Temp1) is defined as follows:

DataFirst groupSecond group
ha_d(employee defined)day digit +employee_id +SPACE(3)day digit
ha_d(no employee defined)day digit +‘000000’ +sequence nr
hmtime string= hours * 100 + minutes'9999'
npers0101

Now we can proceed with the main SELECT-SQL :

SELECT STR(ha_date-ld_start+1,1)+IIF(EMPTY(h_start+m_start),'  ',;
       IIF(EMPTY(hempl_id), ;
       '000000' + STR(ha_seq,3),hempl_id+'   '))+SPACE(55) AS ha_d, ;
       PADL(ALLTRIM(STR(h_start*100+m_start,4)),4,'0') AS hm, ;
       01 AS npers, ha_date FROM horattr HAVING &lc_filter ;
       UNION ;
       SELECT STR(ha_date-ld_start+1,1)+SPACE(64) AS ha_d, '9999' AS hm, ;
       01 AS npers, ha_date FROM horattr ;
       GROUP BY ha_d ORDER BY ha_d ;
       INTO TABLE Temp1
Space have been added where needed to keep data formatting between the two groups.

You can see now that Genxtab can be applied to this table with Row identifiers in Ha_d, column headers in Hm and cell values in Npers. Further processing will be needed to get row and column headings meaningful to the user.We will also need the date of each of the days of the timetable, so let’s save these into an array for later use

SELECT Temp1
SELECT LEFT(ha_d,1) AS hdow, ha_date FROM dm011200 ;
       GROUP BY hdow ORDER BY hdow INTO ARRAY la_ddate
Now let’s apply Genxtab to the table, generating a second temporary table Temp2
DO genxtab2 WITH 'Temp2',.f.,.f.,.t.,1,2,3,.F.
Genxtab accepts 8 parameters:

1. Output table name
2. Cursor only output
3. Close input table
4. Show thermometer bar
5. Field position for crosstab rows
6. Field position for crosstab columns
7. Field position for crosstab cells
8. Create a total field

Here is what we find after applying Genxtab to Temp1:

You can see our timetable taking shape now, but with meaningless column headers and row descriptions. Notice also the column headers generated by Genxtab from the contents of the column headers field, hm.

Crosstab data manipulation

The data in the first column has to be transformed in meaningful line descriptions corresponding to one day of the timetable or to one employee.

The day line description includes the day of the week and the date and corresponds to ha_d containing a single digit in the first position followed by six blanks. The function FDOW() returns a string like "Wednesday 29.11.95" and takes the date as parameter.

The employee line description contains Name, First Name and Initials, which we extract from the e_id arrray we prepared earlier. Rows where no employee has been assigned (employee ID = ‘000000’) have a series of question marks "??????" as line description.

Here is the transformation of the first column :

REPLACE ALL ha_d WITH ;
  IIF(SUBSTR(ha_d,2,6)==SPACE(6), ;
  fdow(ld_start+VAL(ha_d)-1), ;
  IIF( SUBSTR(ha_d,2,6)='000000','??????', ;
  e_id(CEILING(ASCAN(e_id,SUBSTR(ha_d,2,6))/2),2) ) )
The cells of the table corresponding to assignments should display as "XX", and all other cells should remain blank. This means our cells must contain caracter type data. But, if you remember, Genxtab requires numeric cell contents, which results in numeric values of 1 in the cells corresponding to assignments. To get where we want, we will have to modify the structure of Temp2 table by changing the type of cell fields from N to C.

At the same time, we will also change field names from C_0830 to something more meaningful to the user. Be careful, though. Modifying the structure of a table containing data may rresult in the loss of your data. FoxPro creates a new table and appends data from the old table to the new table. Fields with a new name have no equivalent in the old table and will remain empty after the modification.

Our first step will then consist in saving our data from Temp2 table to an array :.

COPY TO ARRAY la_brw
While the data resides in the array, we can modify the type of cell data, and replace 1s with "XX" and leave other cells blank :
FOR ln_loop = 1 TO ALEN(la_brw)
IF TYPE('la_brw(ln_loop)')='N'
    DO CASE
      CASE la_brw(ln_loop) = 0
        la_brw(ln_loop) = '  '
      CASE la_brw(ln_loop) > 0
        la_brw(ln_loop) = 'XX'
    ENDCASE
  ENDIF
ENDFOR
From the command window, you would now type MODIFY STRUCTURE and make the changes interactively, then save the new structure. From within an application, the process is somewhat different. Using COPY STRUCTURE EXTENDED, you transfer the file structure to another file, in which you can then make all your changes. The modified file is used as a template to build a file with the new structure, using CREATE FROM, and last step, the data is transferred back from the array where it was saved to the new table
COPY STRUCTURE EXTENDED TO Temp3
USE Temp3 in 0
SELECT Temp3

Here we have a direct access to the structure of our data table and we can modify field types, lengths, or names programmatically.

REPLACE ALL field_type WITH 'C' FOR field_name='C_'
REPLACE ALL field_name WITH 'C' + RIGHT(STR(100+RECNO()-1,3),2) ;
        FOR field_name = 'C_'

While we have access to the table structure, we can prepare column headers for our BROWSE command.

Columns corresponding to the hours have a two-digit "hours" description while the remaining columns, which correspond to the half hours, have a blank description.

COPY TO ARRAY la_colname FIELDS field_name
*-- number of data columns
ln_brcol = ALEN(la_colname) - 2
DIMENSION la_header(ln_brcol), la_colvar(ln_brcol)
*-- generate headers
FOR ln_loop = 1 TO ln_brcol
  lc_min = SUBSTR(la_colname(ln_loop+1),5,2)
  lc_hr  = SUBSTR(la_colname(ln_loop+1),3,2)
  la_header(ln_loop) = IIF(lc_min='00',lc_hr,'  ')
ENDFOR
Now let’s rebuild our data file with the modified structure and the modified data :
CREATE Temp4 FROM Temp3
SELECT Temp4
APPEND FROM ARRAY la_brw

Almost there now, except for column headers.

Preparing the BROWSE command

Our BROWSE window contains one fixed column displaying line descriptions and a variable number of data columns with variable headers as the timetable can cover any time range within the 24 hours of a day.

We are going to prepare the lc_brfld string, the variable part of the FIELDS clause of the BROWSE command. The FIELD clause for a C_0900 column would look like this :

C_0900 :H="09",
The whole string is built within a loop covering all cell columns. The column header is found in the array la_header we prepared earlier.
lc_brfld = ""
FOR ln_loop = 1 TO ln_brcol
  lc_colhead = 'C' + RIGHT(STR(100+ln_loop,3),2)
  la_colvar(ln_loop) = lc_colhead
  lc_brfld = lc_brfld + lc_colhead + " :H=la_header(" + ;
             STR(ln_loop,2) + "),"
ENDFOR
Woops, don’t forget to remove the last comma separator after the last field :
lc_brfld = ALLTRIM(lc_brfld)
lc_brfld = LEFT(lc_brfld, LEN(lc_brfld) - 1 )
Wouldn’t it be nice to have a dotted line to separate the days ? The last column of the cross tabulation contains 1s in the lines corresponding to the days of the week. It will only take a few more lines of code :
lc_lastcol = 'C' + 	RIGHT(STR(100+ln_brcol+1,3),2) && last column
SCAN FOR &lc_lastcol = 'XX'
  FOR ln_loop = 0 TO ln_brcol
    lc_thiscol =  'C' + 	RIGHT(STR(100+ln_loop+1,3),2)
    REPLACE &lc_thiscol WITH '--'
  ENDFOR  
ENDSCAN 
and here comes the final BROWSE commnd :
DEFINE WINDOW lw_br from 1,0 TO 24,79 ;
    TITLE "HORAIRE (ATTRIBUTIONS)" SYSTEM CLOSE FLOAT COLOR SCHEME 10  
BROWSE FIELDS ha_d :7 :H='   ', &lc_brfld ;
    NOMODIFY NOAPPEND NODELETE NOMENU WINDOW lw_br     
You may think this was a long walk with lots of data manipulation just to browse a set of data. I am sure many of you have learned at least one trick along the way, and that was the purpose of this article. And if you knew them all, you could as well write the continuation, which is about printing the timetable with a single report form, covering any time range, whether it fits on one page, or is several pages wide.
Christian Desbourse, Ir C Desbourse
Christian Desbourse is an independant consultant providing services in database development and Visual Foxpro programming. Independent developer of business applications. Business experience in logistic and industrial project management. Microsoft MVP from 1996 to 2002. MCP Visual Foxpro Desktop and distributed applications
More articles from this author
Christian Desbourse, January 1, 2001
One of the most powerful command available with FoxPro’s language, SELECT-SQL allows you to extract from your databases the right data you need. For the beginner, FoxPro provides a built-in RBQE dialog which builds the SELECT-SQL statement and stores these in QPR files, which can be executed from w...
Christian Desbourse, January 1, 2001
If you include two tables in a query without specifying a join condition, every record in the first table will be joined with every record in the second table, with huge results as a consequence.
Christian Desbourse, January 1, 2001
Simulate SEEK, DO..WHILE and SCOPE Do you want to retrieve the five companies following ‘France restauration’ in Customers ? Oh I know, xBase junkies can do that with a SEEK, and a DO WHILE and ....but aren’t we talking SQL ? We will use a UDF to control the scope : FUNCTION incremnt PAR...
Christian Desbourse, January 1, 2001
You have set up an extensive security system to control access to your application through user log-in procedures, and password entry. But some people tend to be security reluctant, they write their password on the wall, or worse, everybody is using the same "Hello" password.