I have two related tables Timesheets and Tasks, where users enter time into the Task table via a portal on a layout based on the Timesheet table. The Timesheet table then has a field that concatenates all related tasks for a particular day&project&employee into one calculation (text) field using the ValueListItems function... this concatenated description is then (theoretically) what appears on our client invoices.

I'm running into a problem looking up timesheet records where a field on the task record is a certain value. I have a scripted lookup to filter to all the correct task records, but how can I then switch to a Timesheet-based layout and look up all the timesheet records that are related to the task records I've filtered to?

I'm thinking I need a script to loop through my filtered Task records and add the related timesheet record to a lookup? But I'm not sure how best to go about this or if it will even work.

~Courtney

When you say that you "have a scripted lookup to filter to all the correct task records", do you mean that you are filtering portal contents, or filtering a found set of records? If you're filtering the found set of tasks, I think the following will work:

The Go To Related Record script step has a result option that matches all the records in the current found set. If you are on a tasks-based layout and you want to view all the timesheet table records that relate to that found set of tasks, under the GTRR options, specify:

Get Related Records From: Timesheets

Show Records Using Layout: [Timesheets layout]

[X] Show only related records

( ) Match current record only

(o) Match all records in found set

I could completely misunderstand your need or be way off. Hope this helps.

The Go To Related Record script step has a result option that matches all the records in the current found set

Carefull here, it's a feature first launched by the arrival of fm8, before were rougher measures required:

While you can do a GTRR ("Go To Related Record") on a single record, and view related entries that are one relationship step away, versions of FileMaker Pro prior to FM8 do not offer an obvious way to do either of the following: a) View related entries that are more than one relationship step away (e.g., for a given customer, locate all items they have purchased), or Start from a found set rather than a single record (e.g., in a found set of all September 2000 invoices, go to the found set of all related customers). Using multiline-key GTRRs, you can easily do both of the preceding.

http://www.kevinfrank.com/download/multi-gtrr.zip

--sd

Thanks for both responses.

Soren's linked solution worked perfectly.

The other response prompts me to really urge my boss to update to the latest version of FM.

~Courtney