Running Slow

[UpNorth]

Here's my situation....

2 filemaker 8 files(work orders and clients)

Each record in the work order database has a

-Client Field

-Hours Used Field

Each record in the client database has

-Client Field

-Summary hours field

In the client database I have a relationship that uses the client field to find matching records. Then the summary hours field adds up the hours used. Seeing I have 1000+ work orders for each client this takes filemaker awhile to calculate the summary hours field.

Is there a different way of doing this that wouldn't take so long??

[Fitch]

Each time you make a new work order you could add it to a stored field in the client file. You'd have to be scrupulous about keeping the stored field up to date, it has to be tightly scripted.

[UpNorth]

I greatly appreciate the suggestion! I've been thinking about how to implement it for the last day or so, and I'm coming up with a few issues....

-where would I put the script once I had it all squared away. It can't be on the create new button because the info is not entered yet. It can't be on the submit/save button because not everyone uses it. (web users have to press it but not the users that have filemaker on their machines.) Maybe in the closing or opening scripts?

The script would have to identify the client and then take the hours and add them to the stored field. How would I do this?

I can't use an if or case statement because that will add up all the hours for all of the new records.

I should also mention that I would like to break down the hours by month.

Please let me know if you have any ideas of what kind of script I could use that would add up the hours per month for a client.

Thanks again!

[Oldfogey]

I am guessing that you only need this information at month end? If so, why not just produce a summary report from the Order table? This shouldn't present a time problem. If you still need to store the summary information in the Client table, you can script it to run (separately from the report) while you have (lots of) coffee.

[UpNorth]

I do run a report, but I'm looking to have the numbers to use in a graph.

GetSummary(summaryField:breakField)

Is what I'm looking for I think...

Edited February 26, 2008 by Guest

[Oldfogey]

Not really. I may be wrong, in which case I'll be corrected, but I suspect that GetSummary() might be a lot slower than just getting totals in a Summary part.

See this quick and dirty example.

You would probably remove the Body from the report layout in real life. Also, if you want to save those Total Hours values, you could script it using GetSummary() but, again, you'd need a steady source of coffee.

I am assuming that getting the report and saving the summary values do not necessarily have to happen at the same time?

Summaries.zip

Edited February 29, 2008 by Guest

[UpNorth]

So, I added to your summary database to show you what I'm trying to accomplish. The get summary function did not work. : The reason I'm not creating relationships for each month is because I would have to create 36 of them for my current database. (I have 3 seperate catagories of hours). And of course the coffee factor... I like coffee, but not that much. :

Summaries.fp7.zip

[Oldfogey]

Sorry for the delay, UpNorth. Been trying to stave off hunger.

Another apology, I should not have mentioned GetSummary(). It doesn't work across tables.

I was just trying to make the point that you can get your basic report, as you have done, fairly quickly; the only thing you haven't got at that stage is the set of stored totals. You'd be lucky to get your coffee made while the report was running.

There are umpteen ways then to get those totals copied into you Client table. (I have to wonder why you want them there when you can always regenerate them.) By far the easiest way is to create those relationships. Do you have three different types of WO records or three different hour fields in each record? If the latter, you only need 12 relationships.

Probably the next simplest is to just loop through the WO records and transfer each subtotal across to the client table. Looong time but let it run overnight.

You could also create a new file by exporting the summary data. It is then a piece of cake to use a simple relationship to display the totals or, if you insist, transfer them to the main Client table.

[UpNorth]

The reason I'm looking to have the calculations in the client database is because I would like to create a graph with those numbers using fusionCharts.

As far as the number of relationships. A work order only can have one total hours and only one hour type, but depending on the client they can have up to three different hour types. The client then lets say has 10 work orders and 2 of the are for A hour type, 3 of them are for B hour type and 5 of them are for C hour type. Along with that I want to break each hour down by month for a selected year.

It just seems to be a lot of relationships to create and it runs slow because some clients have over 1000 work orders, so I was just looking for a way to simplify... and maybe learn something new... make my database run faster... you know the never ending quest as a developer. :)

Edited March 21, 2008 by Guest