Help, I know there must be a simple solution. I have two DB's (JobBook and timetracker) with a one to many relationship based on a Job# field.

I am trying perform a find on the related field Job# using the active record from JobBook. I have tried using both a Copy and Paste scenario and a self join scenario..

Scenario 1 (just a description, not exact syntax)

in JobBook

- Copy Job# from active JobBook record

- Run external script (timetracker, Find)

in timetracker, Find script

- Paste into a global field (Job# copy)

- Enter Find mode and Set Field (Job#, Job# copy)

- Perform Find

Scenario 2 (also just a description, not exact syntax)

in JobBook

- Run external script (timetracker, selfjoin)

in timetracker, selfjoin script based on selfjoin relationship using Job# as the key

- Show All Records

- Go To Related Record (show, "selfjoin")

Both work on my machine locally but fail when I try and use the same technique on networked files. I've tried everything I can think of and think I'm going a bit crazy. When it runs on the networked files, it finds the wrong info...the self join finds completely unrelated records (the same one over and over actually)...any words of wisdom??

Addition to the original post - I've posted examples of the files. Now, they're not working locally either... I thought about it a bit and what I'm really trying to arrive at is a summary of the timed sessions shown in the portal. Although I'm new enough to this that I haven't figured out how to do that either. Thanks for your advice.

FileMaker Version: 6

Platform: Mac OS X Jaguar

timetracker_test.zip

Could you post example files so that we can try to troubleshoot? My first thought was that global fields may not be acting as you expect in a network situation, but I'm not sure

Your self-join won't work because you're only showing records in the timetracker file based upon whatever record you happen to be on in that file at the time, which is independent of the record you're on in JobBook.

Your copy/paste could work, but is more convoluted than necessary.

You're setting the time summary based upon the first related record in the timetracker file, not all of them, and you're trying to use a summary field in that file to set a field in JobBook, which isn't going to work.

I think you could reduce the entire script to a single step:

Set Field[time summary, Sum( timetracker log::Total Time Entered for session )]

This will set time summary with the total of all related 'Total Time Entered for session' fields for the currently viewed Job. No need to do a find in the related file, since the 'timetracker log' relationship is already defined by Job#.

Wow, blindingly simple, thanks -Queue- my headache has magically disappeared.