Link to child records from a parent in a portal

[nick_jp]

Hi All,

I'm relatively new at FM Pro and have been struggling a bit with how to set up queries.

The below is a little long-winded. But I want to give examples of what I've throught and worked through up to this point. I put it in the portal forum, hope it's in the right place.

Anyway...

----

What I want to make: Timesheet DB

What I want the user to see:

User will create a timesheet for a given week, say July 1-7. They can enter new lineitems to log work done, where the select project, client etc from lists. Then they have 7 date fields labelled mon, tue, wed... where they can put a number of minutes.

Relationships:

1 "Timesheet" can have many "lineitems", which can in turn have up to 7 "date entries". Ie, on any line item, you can enter time data for up to 7 days.

I have two ways of storing these "date entries"

a) You have 7 date_minutes fields in the lineitem table. Easy to do.

Have a seperate table for date_minutes information. Seems harder to do, but you can do more granular analysis later.

A date_entry table record would contain the pk for the line_item as a foreign key, a date and a number of minutes.

Deciding which method to use - which is more flexible after the dat is in the system?

Option A, 7 date_min fields in lineitem table.

If the date_minutes records are in the lineitem table, it's easy to enter new records and view them later. But harder to do analysis at the "date" level later. Ie, searching for all records for a given date "day1" entails:

i. finding all timesheets which encompass day1

ii. getting the "day of week" for day 1 - say it's monday.

iii. getting all the line items with something in "monday" (would need your find script to be dynamic, because it doesn't know what date it's looking for)

iv. Analyse and report from that found set.

Option B, child table.

If the date_minutes are in their own table, it becomes very easy to search the records later, and making reports is easy because you can simply have lookups to parent records in lineitem or timesheet_main. If possible I would like to go this way, it appears much cleaner from a programming perspective.

However I'm struggling to see in FMP how you could enter these records (and display). Lineitems are entered in a portal in the timesheet screen. In each lineitem you have 7 date_minutes fields which lookup to child records. I'll probably create these when the lineitem is also created, putting in "timesheet date", "date+1", "date+2" as their date value and setting min fields=0.

--> The thing I can't figure out is how do can you get FM Pro to link from the lineitem to the 7 correct date records? (field's must be editable). If you go back to the timesheet, it has to be able to look up those 7 records for every lineitem in the timesheet. Is this possible? I read in a book about making extra instances of tables.. but it seems that even then you would need 7 instances * a variable numbr of timesheet records.

Sorry for the long post, hope it makes sense. Am I trying to force FM Pro to do something that just wont work with the seperate table for date_minutes records?

[Fenton]

A "timecard", from what I see in FileMaker's template file for example, is basically a "week". But, if done relationally, I see 3 "entities," a week, a day, times (various fields; start, end, lunch, etc., which have times in them).

The "week" could be done, as a view, using a self-relationship, with the WeekOfYear( date ) function; I don't think it needs a table necessarily, but it wouldn't really hurt, may be easier.

A Dates table. Then the Times table. In Dates, the records are dates. A relationship to Times on the date field, with "Allow creation of related records." A portal based on the relationship.

This would produce a layout almost exactly like the Time Cards FileMaker template. The only real difference would be that the week would be Sun->Sat, not Mon->Sun. If you can live with that, then it's all simple. If not, then you'd need to use a calculation to move Sun back to the previous week of year (that would mostly just be a visual change, from Weeks; its date would remain the same).

I'm assuming you're doing this because you want to say something about what was done during the times? Or some other analysis that requires knowing what date/day of week an entry is?

[comment]

With respect, I am going to disagree. The only "real" entities I see here are Users, Clients, Projects and the actual entries. By entry I mean a record like:

UserID: 52

ProjectID: 23

Date: 7/19/2007

StartTime: 09:00

EndTime: 10:45

Weeks are weeks whether you record them or not. The entries would be equally valid if aggregated by day, or by month, or any other period.

All that refers to the data level. If the entries need to be DISPLAYED in a calendar-type display, you will need a Slots table with 7 records, in order to align the entries correctly in a portal.

[nick_jp]

Guys, thanks for your replies

First of all, here's a pic that shows what I'm trying to do (pic shows a table layout, I will use a form layout for the lineitems). The important concept is that the users can add several date/time entries per work item.

I was planning a "timecard" or "week" to have a wrapper entity allowing an admin user to "approve" a logical working week's timecard entry. Also as a display for users to make entries. As you say, it doesn't have to be stored as a week, just displayed as one.

comment, I agree with you that this can be handled with just an entry record. You are suggesting that I make a table "slots" to display this.... can you please elaborate on how this would work. What I picture in my head is:

ENTRY: User choses a project item, enters 30 mins on 3 days.

When user hits save - 3 "entry" records are created. Slots records are cleared out.

VIEW: User selects a "week" to view (from a calculated lookup). Entries which are within that date range are loaded into the slots table and displayed to the user. You canupdate the record, and when you save it finds the initial "entry" record, updates that and clears out the slots record.

The reason I say clear out the slots table, is to avoid differences between your "slots" record and the "entry" records due to user changing the slots record and then navigating away somehow.

How does that match with your suggestion?

Thanks in advance.

Nick

[comment]

I have a feeling we are not speaking of the same kind of slots. See this thread, for example:

http://www.fmforums.com/forum/showtopic.php?tid/176396

In any case, the type of layout you want is going to be very difficult to implement. Is it even a good idea? What if I need different comments for the same task I did on Monday and Tuesday?

I'd suggest you try for something simpler, at least until you get more familiar with Filemaker. You could have a "viewer" table, with a global field to select the week's starting date (or any date that falls within the week), and 7 calculation fields for each day of the week. Define 7 relationships to 7 TO's of the Entries table, each relationship being something like:

Viewer::EmployeeID = MondayEntries::EmployeeID

AND

Viewer::cMonday = MondayEntries::Date

Enable the creation of records on the entries side. Then place 7 portals on the Viewer layout, so the user can fill one row for each entry in the appropriate portal.

[nick_jp]

I think I get it.

7 TO's, the first one's relationship would be

Viewer::EmployeeID = MondayEntries::EmployeeID

AND

Viewer::cMonday = MondayEntries::Date

2nd would be

Viewer::EmployeeID = MondayEntries::EmployeeID

AND

Viewer::cMonday = MondayEntries::Date+1 (or whatever the syntax is)

And so forth...

Thanks. This would allow me to drill down the the date level much easier, and having the time range, instead of just an amount on minutes makes for more interesting display options down the line.

Appreciate your help.

Edited July 20, 2007 by Guest

[nick_jp]

I have a question. Suppose you have several users. For a year of 15min slots, do you need to make a seperate set of slots for each user to display their own events?

[comment]

If you read through that thread, you know the answer depends on who you ask. If you ask me, you only need as many slots as you will ever show at one time.

[nick_jp]

I'm looking at your solution now. It makes more sense with a system that could be used by several people to have the calendar view built dynamically like that.

tvm