7 Replies Latest reply on Oct 22, 2013 12:14 PM by pminich

    Calc field updates but find doesn't work - index issue?


      I have an issue where calculations are updating but when I perform a find on the table I get the records that relate to the previous calculation.


      I have 2 tables - Clients and Catering Invoices. I want to do dynamic year over year sales reporting for each client.


      The client table has global fields for the current year dates and calculated gobal fields for the prior year dates as well as the account code for each client.

      The invoice table has the account code and date on each record. It also had calculated sum fields for each date range (this year and last year) based on 2 TOCs for the invoice table based on client code and date range.


      When I change the date range the date range the summary fields update appropriately. When I do a find for only fields with contents it gives me the correct records the first time. If I change the dates and perform a new find I still get the same set of original records regardless of whether the sum field has anything in it or not.


      I am assuming that this is some kind of indexing issue. I have confirmed that the reindex as neccessary is turned on.


      Any other thoughts on what may cause this or another way to skin this cat?

        • 1. Re: Calc field updates but find doesn't work - index issue?

          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.



          • 2. Re: Calc field updates but find doesn't work - index issue?

            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.

            • 3. Re: Calc field updates but find doesn't work - index issue?

              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.



              • 4. Re: Calc field updates but find doesn't work - index issue?

                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?

                • 5. Re: Calc field updates but find doesn't work - index issue?

                  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.

                  • 6. Re: Calc field updates but find doesn't work - index issue?

                    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.

                    • 7. Re: Calc field updates but find doesn't work - index issue?

                      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





                      Pete Minich



                      Direct/FaceTime/Text  - 781.223.8884