pminich, depending on what version of filemaker you have you could use the ExecuteSQL function to pull these data.You have enough information in the clients table to build a query that will return then you would just need to process the string that is returned.
I appreciate the thought but I will save that as a last resort we are currently on 11 but migrating to 12 soon. One of the reasons that I like FM is that I don't need to know SQL.
This is something that FM should be able to handle natively, I'm just missing something.
I think 11 has some built in ExecuteSQL functionality I'm not sure just how much. We went from 10 to 12. Also I'm not sure I would agree with "able to handle natively", Oracle for instance doesn't have a calculated field type. But based on the features the FMP offers I would tend to agree with your statement.
Sounds like an indexing issue. Maybe you can try to : Show all records, change the date range, commit and then do the find to see if that works?
This ended up being an issue of using a global field and a filed calculated from that global in the relationship. I ended up reworking the solution by putting all the globals in the same table and then doing a self join. It ended up being pretty quick.
I believe the problem is that the left side of your relationship join is a global field.
You can get away with this in single user mode, but not on filemaker server.
Remember global fields have the same value for all records and are session specific.
Here's an example for you, let's say I have global field for current year = 2013.
So my join between clients and invoices is
gYear = Year(invoice date)
So I'd like to do a find for total sales for the client for 2013.
So I have a Calc field that summarizes the total = sum(related_invoices_basedongYear::invoice total).
I try to do the find and well it just doesn't work. The data shows right on the screen, you can see the calc field is correct, but you can't find on it. Part of the problem is the way in which filemaker evaluates globals and the way they trigger events (or don't in this case).
One solution here is don't use a global field for left side of Join. Just make it a calc field = 2013 and another field for the prior year. Of course then it isn't changeable and then you have to update it next year so that the current year = 2014. So there are some drawbacks.
Here's yet a different solution for you. Part of your problem will be speed, because it has to calculate the total invoices for each client for each year. Instead, setup a table in clients for total sales by year. You'll relate it to clients by client# and then have another field for year and a third field for total sales (and/or total profit, qty etc.).
So for client ABC there might end up with this related data.
2013 - total sales= 110,000
2012 - total sales = 58,000
2011 - total sales = 73,000etc.
Now it's very fast to do the finds because the data doesn't have to summarize invoices total of every client's in the system before it can perform the find. The problem is what if you have a new invoice... how does the total get updated. Ahhh and here lies the difficulty in creating robust fast systems. This can be done either nightly by a server side script and/or in combination with script triggers to update the data for any sale that happens today. This adds a lot of complication to things, and it's tricky to make sure your static totals get updated in every possible scenario. But using this basic idea you can have a system with hundreds of thousands of clients, millions of invoices and be able to do finds and summarize data like it as a system with only dozens or hundreds of each.
Wow, thank you for the thoughtfulness of your reply!
I had considered your second option but didn't go down that road because I
need to be able to do this for any arbitrary time period. At the end if the
day I opted for using standard summary fields. Which are performing fast
Direct/FaceTime/Text - 781.223.8884