Real-time subtotal from related file?

[chakadog]

Hi all... it's been some number of years since I dived into FileMaker, but now we need to add a feature to our suite of related databases developed with v3, now running in v5.

OK, so we're a design office and keep all time cards in FMP. We track project time by phase. What I want to do is see the accumulated phase totals, by project, in a related data file where I can crunch the numbers and perform some analysis against budgeted time.

What I can't figure out is how to display the subtotal of hours per phase per project into the related file?

I suppose the first step is to write the correct code to display these subtotals correctly in the parent file, but that too, seems to be beyond me! (So maybe this isn't a realtional question at all and I'm posting in the wrong forum if so please forgive!)

Can anyone toss me a bone here and help me get started? Is this even possible?

Thanks so much.

[-Queue-]

Create a subsummary part by phase and one by project in the time sheet file. Put a summary field for hours in both parts and sort by project then phase. Go to Preview mode to see the result.

[chakadog]

hey thanks for your response. I apologise for not making this clear: by "real time" I mean "no reports". I know how to do that. What I'm looking for is a linked summary that is always accurate in the related file: as data is changed/added to the timesheet file, the changing subtotals are reflected - in real time - in the analysis file.

I might be barking up the wrong tree. Maybe this is a place FM can't take me. Maybe a scripted solution that will copy/paste the subtotals into the analysis file is the avenue I should take?

[chakadog]

hmmm... just read mark andersons query regarding summaries in a related portal. I *think* he's asking the same question, basically, but the response was that he couldn't do that...

[-Queue-]

Correct, as I told him, you need a separate file that contains one record for each piece of information to be summarized. You can display these records from any other file by using a relationship based on a constant calculation (or auto-enter calculation) of 1 in both files.

[chakadog]

Thanks, Queue. I'll give what you suggested a whirl: set up my summary calcs in a third file (created just to summarize the time data), then refer to that file to get analysis data.

[chakadog]

Queue - I'm struggling with this and need some help. I'm guessing the files you posted in response to mark_anderson's question might help but they're V7 and I'm running V5.5.

To spare you the tedium of listening to me explain my entire data structure would you mind posting that tiny solution in version 5? If I can't figure it out then I'll post again here for more help. I, too, need to find the first instance via a self-join, and use that record to generate my subtotals. I've taken your advice but so far can't get the results I need... Thank you.

[-Queue-]

Here you go.

SummaryTable5.zip

[chakadog]

attachment received! thank you...

queue, I haven't looked at your solution yet, but last night I figured that the best way to use the summary file is to run a script that will

delete all old records in the summary file

find the first instance of each unique key in the source data (in my case job+phase),

import these into the summary file (so all new jobs and phases are shown)

does this sound like a good approach to you?

[-Queue-]

If there is a good relationship from your summary file to the line item data, there should be no need to delete/update. The example uses calculations that update themselves. However, if you are working with hundreds of thousands of records, then the speed factor may make your idea much more efficient.

[chakadog]

OK, I'm missing the point on something, and need to study your example. Thanks so much for the help.