What am I doing wrong?

[Chris Echols]

I thought I had a handle on relationships but obviously not. I'm trying to make a database to keep up with volunteer hours. I'm at the point now where I have to update all of the flat files that I've made to make input and reporting a little easier. I have 3 tables, volunteers, time cards (the FM stock file) and tasks. I want to use the time card as my parent file, so I have the relationship with the time card and the volunteer table working ok. I'm using lookups to actually get the names. The problem is that I made this button to "add a task to this day" and I want it to make a new record and record the related name and the associated time card. Multiple tasks can be related to one time card.

How do I get the lookups to work for names, dates, etc? Or should I be using portals? Right now I have the volunteer ID related to the volunteer ID on Time card and the Time Card ID, related to the Time Card ID on the task record. Do I need to somehow relate the task ID back to the time card?

[mav]

Chris,

It sounds like the volunteers needs to be the primary table, with the time card being a report (or portal) of the tasks table records assigned to the volunteers.

This way, you can have a portal of the tasks on the volunteers screen, being filtered by a global field that is based on the dates from a value list, created from the date field from the task.

Can you post a small file to show where you are at ?

[Chris Echols]

Sure...

Here ya go...

I thought about that, but since I've never had to build a table with portals at all, I'm missing something to make it work like I want to.

Volunteer_Time_and_Task.fp7_2.zip

[mav]

Hey Chris,

I've adjusted your file, so that the VPs are the primary table, and that the Time Sheets are created from the VP layout, and the Tasks from the Time Sheet layout.

I've created one sample script to create the Task from Day 1, you need to create separate scripts for days 2-6 or adjust the current script to include a script parameter, but that’s an other level of scripting. I've attached the scripts to the word next to the new icon, as the icon would not fit next to the day 1-7 new task lines, you may want to look at changing this.

I've removed the look up for first + last name and Department + Extension fields from Time Cards and Tasks and placed these fields from the VP fields.

I've renamed the Primary and Foreign keys (these are used for the relationships) to all end in ID and are at the top of the Define Fields window. I've removed the TaskSerial as it was not being used.

I've set up a Global fields to 'capture' the Primary ID and the other global to ‘capture’ the date from the Day 1, when switching to the new layout to create new records (copy and paste means you can't grab more than two fields and the user may have data in the clipboard)

The portals on the VP layout show the Time Cards and Tasks assigned to that VP. There is also a portal on the Time Card for the Tasks. You may wish to change the fields inside the portals, and change the look and feel.

There are scripts that were part of the demo file that may now be broken, you will need to find which ones you still want to you, and reconfigure them. You have a number of Tasks that do not have a Foreign ID Key, although you can still go in and manually enter the TimeCard ID yourself, or delete these records.

My personal view is that you start with the person, then all other records exist from there, although you can still do finds and create reports from the Time Sheet and Task screens if you wish. I always think of a 'report' as a found set, and even the records inside a portal is a 'screen report'.

Let me know how you go with this . . .

Volunteer_Time_and_Task_Portal.zip