Using master table to
keep track of homework

Chong-ho (Alex) Yu, Ph.D., MCSE, CNE


I am a "cyber-teacher" who offers Web-based instruction. I maintain a File Maker Pro database that allows students to submit assignments to the Web server, and also retrieve the assignment with my comments later.

There are several homeworks in the semester. However, I don't want to open them individually. Rather I would like to see a master table showing all assignments by all students, so I could easily tell who has done which assignment and who hasn't done which. In the table, a completed assignment should show a value of "1", a completed assignment with my comments, "2", otherwise, "0". The values in the table cells should be hotlinked to the actual assignments. "0" should point to a blank new input form while "1" or "2" links to the submitted paper. The output should look like the following (This table just shows you the idea. The links inside the cells are not active):

Student Name

Homework 1

Homwork 2

Homework 3

Homework 4

Alex Yu





James Jones





Tommy Lee





0 = not submitted, 1= submitted but not commented, 2 = submitted and commented


First, you have to create separate databases for different homework assignments, and one database for the master table. In all homework databases you should create a field to store the creation date of the record.

You don't need to create another database to store your comments. The comment fields can be embedded in the homework database. And then you join all of them as relational databases by a key field such as user ID.

In the Master database, create a calculation field called "CH1TF," which stands for "comments on homework1, true or false". In the formula enter the following If-then-else statement. The statement means that if the field "comments" in the database "homework1" is empty, return the value "0", otherwise, "1."

		If(IsEmpty(homework1::comments), 0, 1)

Create another field labelled "H1TF," which stands for "Homework 1 true or False". This field will be used on the Web to indicate the status of the homework assignment. In the formula enter a nested if-then-else statement as shown below. In a nested if structure, the condition in the first "if" is evaluated first. If the condition is satisfied, the value will be passed to the next level "if" for further evaluation. In this example, if the creation date of "homework1" is blank, a value of "0" will be returned because no assignment is submitted. If it is not empty, the next question is whether comments by the instructor has been made. This question has already been answered by the preceding field "CH1TF". Therefore, if "CH1TF" is "1", a value of "2" will be returned, otherwise, "1".

		If(IsEmpty(homework1::H1creation_date), 0, If(CH1TF=1, 2, 1))

Next, create a calculation field entitled "H1". The purpose of this field is to provide different hotlinks according to the status of the homework assignment. Enter the following if-then-else statement into the formula. It means that if the creation date of "homework 1" is not empty, show a link to a blank new input form, otherwise, show a link to the submitted record according to the user ID. The characters after "http" should be continuous. Here they are shown in different lines just for clarity. The purpose of each line will be explained later.


The above source code is difficult to read, but not difficult to understand. The function of each tag is explained in the following:

In order to output a table as shown in the beginning, you have to edit "search_result.html". Insert the following line into the table cell where the status of homework 1 is shown. The appeared text, which is provided by "H1TF," would be "0", "1", or "2". The hotlink, which is provided by "H1," would show either a new input form or the submitted assignment by that student.

		<A HREF="[FMP-field:H1]">[FMP-FIELD: H1TF]</A>

Duplicate the same fields and formulas to "homework2," "homework3," and so on. Now you may keep the search engine for "master" only and discard those for individual homework databases.


FMP Tips Contents

Other computer tips

Search Engine

Credit/Copyright ©

Simplified Navigation

Table of Contents

Contact Me