Layout Question

[mcole]

I am currently using a single db file to collect information

about music students. Among the fields are time and day

for each student's lesson. I want to create a layout that is

essentially a table with time along the left side and

day along the top.

I tried to make 6 global fields (text, repeating 23) to

insert the names of each student for the appropriate day.

The file was sorted by day and time prior to looping through

the records. My plan was to have the global fields arranged

on the layout from monday to saturday and the students name

would be added to the field by the script. But I can't seem

to figure out how to enter information into a repeating field

with a script.

Is there another way that someone would approach this problem?

Am I stuck with creating 23 X 6 =138 fields that are to be

placed on the layout and calculate which field should get

which student name? Argggh.

Ideas welcomed.

Thank you,

Michael

[LiveOak]

You need to quite treating FM as a flat file and bring some of the relational power into play. The general technique we use to do this type of thing is a little more complex. First, I would put the student data (name, address, phone, etc.) into a Student file. I would next create a related file Lessons (related by an auto entered student number) and store the appointment informaton in this file. The third piece is how to display the appointments. Next, display appointment information in the Student file on a layout dedicated to that purpose. The fields displayed on this layout are all related fields from the Lessons file. Here you have a couple of options. You could build 6 relationships with the Lessons file, each showing a list of appointments for one day and display the appointments as lists in 6 portals. If you would rather have a slot for each appointment, you will need to create 138 relationships with the Lessons file. The indexes for these relationships can all be calculated from a single entry into a global field which specifies the week you wish to look at. Each of the 138 indexes points to a different date/time slot. If a record in the Lessons file exists with a matching index, it is displayed, if not the slot will be blank.

This same approach can be used to display timecard entries or create a general purpose calendar. The key is not to embed you data in repeating fields where it is impossible to summarize it (say print a list of appointments for Thursday), but to make each data element (appointment) a record in a separate file. Then if you want a report of all of John Smith's lessons for the last 5 years, you can find, sort and print one (or of all the Wednesday lessons or of all the 5:00 PM lessons, etc.).

Hope this isn't too hard to understand, it is definitely not a good first beginner project!

-bd

[mcole]

Ok, I have been working on this for a few days. I am a

bit confused about the relationship that I am suppose to

create which will allow me to display a single days'

appointments. Would you mind directing me to a file that

may show an example of this as explained or would you

mind trying to explain it in a step by step fashion so that

I can 'see' what is suppose to happen.

Thanks,

Michael

[LiveOak]

The key to this is the creation of the 138 indices for the left side of the 138 relationships. They are all calculated based upon a single global date field:

gWeekBegin

Day1Slot1 (calculation, text, ASCII) = gWeekBegin & "-" & "1"

Day1Slot2 (calculation, text, ASCII) = gWeekBegin & "-" & "2"

.

.

.

Day2Slot1 (calculation, text, ASCII) = (gWeekBegin + 1) & "-" & "1"

Day2Slot2 (calculation, text, ASCII) = (gWeekBegin + 1) & "-" & "2"

.

.

.

Day6Slot6 (calculation, text, ASCII) = (gWeekBegin + 5) & "-" & "6"

This way when a new week beginning date is entered, all the displays "scroll" to the new week of appointments.

Each of you 138 appointment display fields could have a button linking to a script (you might need 138 of them!). The script would test the appointment slot, if there was no appointment, the script would navigate to a screen to enter one. The script would then automatically create a record in the related file and set the index on this record (right side of the relationship) to the proper date-slot, governed by the button pushed.

If this is still a little fuzzy, let me know and I'll create a simple example file and send it to you.

-bd

[mcole]

Thanks for the example file. It has been very informative. I noticed that there is only one relationship, the Day1Slot1 that creates a new related record. I don't quite understand this. How many indices can the single variable, index, contain? Is there a maximum?

Thanks,

Michael

[LiveOak]

A total red herring! Just uncheck the box. All the relationships should have this unchecked. I changed my mind about the implementation in mid stream.

"How many indices can the single variable, index, contain?" I don't know what you mean. Try restating your question without using the terms "index" or "indices". If the question is "How many relationships can be based upon a single field?". Practically speaking, there is no limit.

-bd