13 Replies Latest reply on Jun 5, 2014 4:29 PM by Stephen Huston

    Constraining a found set and Referenceing related fields for calculations.

    marty72

      Hi, this forum has been excellent and has provided me with lots of helpful information in getting my solution to its current state (FileMaker 13, FMGO 13). [It is attached for reference]

       

      I have an tree auditing solution that I am working on that involves 3 tables. Project table, Plot table (related to Project) and a Tree table (related to Plot table). Each project can have multiple Plots and each Plot will have mulitiple Trees. My Tree layout works great in the field for collection data (on an iPad) - Filemaker has made this process very efficient.

       

      Issue#1

      I am having a issue with a one of my Layouts that is used in the field to provide some quick summary information. I have one Layout called Quick_Summary that provides a summary of a number of items for the Trees within the Plot that is currently being assessed. This one works well and provides numbers as expected based on a number of calculation and summary fields.

       

      However, I have another Layout called Quick_Summary_All that provides a summary after multiple Plots have been assessed. It basically is supposed to provide the same information except that most of the numbers presented are now averages based on the number of Plots assessed to-date, within the current Project. One of the fields in question on this layout that will illustrate my problem is called Total_BA_Marked_Sum from the Tree table. In my Options for this field I reference another field in the Plot table called Plot_Count which is a field I increment as Plots are added. It is obviously not using this number though since the value that is appearing in Total_BA_Marked_Sum is a total of all the trees in the all of the plots, and not an average. My rookie-ness is showing but is the problem my table context or something else? As you can see by some other fields not presently being used I have tried global fields and variables but have not been successful yet.

       

      Issue#2

      When I move from Project layout to Plot layout I only want the plots that are in the current Project to show for reviewing and adding new records(Plots) (currently only 1 Project here but when I do add another Project all the plots show when I navigate to the Plot layout and use my navigate buttons). The same issue exists when I navigate from Plot layout to the Tree layout. I thought that I could do a Find and constain the found set before I moved to the new layout but all the records still appear when I navigate from record to record.

      I suspect this is not a big deal but I am not seeing my error right now.

       

      Thank you for any advice.

       

      Marty

       

      P.S. The orange text and items are just for developement purposes and are not part of the user solution.

        • 1. Re: Constraining a found set and Referenceing related fields for calculations.
          mikebeargie

          I can immediately see the issue with your #2 question. Your find is based in your parent table, NOT in the related plots table where it needs to be. It's MUCH easier to use the "Go To Related Records" script step since your relationship to plots is already sufficient for finding related records.

           

          As for your #1 issue, it's your relationships. When you reference a field from plots from trees, it returns the value from the FIRST related record, so you need to either sort your relationship so the highest number is first, OR use the Last() function to get your value from the LAST record in the relationship. Since your relationship is NOT sorted between trees and plots, I am assuming natural order (creation order), places your Plot_Count value needed as the Last ( PLOTDATA::Plot_Count ) value as what you require.

           

          Also, you primary key calculation is one of the most crazy concatenations I've seen. Do you display this anywhere? Get(UUID) is a much easier unique key to generate for a text field primary key.

           

          I made some adjustments and commented it up, see if this helps!

          1 of 1 people found this helpful
          • 2. Re: Constraining a found set and Referenceing related fields for calculations.
            keywords

            I haven't opend you file yet, but one thing stands out in your description of Issue#1. You state: "the value that is appearing in Total_BA_Marked_Sum is a total of all the trees in the all of the plots, and not an average". That suggest that you have the Total_BA_Marked_Sum field set to TOTAL rather then AVERAGE the data it is summarising (I am assuming it is a summary field). If so, that's an easy fix.

            • 3. Re: Constraining a found set and Referenceing related fields for calculations.
              mikebeargie

              it’s just a field name. It’s a calculation field that has a total / count to calculate an average.

               

              There’s actually a ton of calculated values in the file.

              • 5. Re: Constraining a found set and Referenceing related fields for calculations.
                wimdecorte

                Mike Beargie wrote:

                 

                 

                There’s actually a ton of calculated values in the file.

                 

                No kidding!  TREEDATA has 101 fields and only 13 are regular data fields, the rest are calcs and summaries.

                 

                Marty: I would tackle that first before going any further.  This is a recipe for all sorts of performance problems down the road. 

                • 6. Re: Constraining a found set and Referenceing related fields for calculations.
                  marty72

                  Thanx Mike. The solution you suggested for #2 does what I wanted.

                  As for # 1, I understand what you mean about using the Last function since my records are not sorted. However it still does not give me the desired result. Is the issue maybe something to do with my table context? I edited the script for the ProjSummary button to use GoToRelated records from TreeData. However, my Quick_Summary_All layout is based on my main TMQuality (project) table. I have the Summary items from TreeData in a Portal on this page that I also had filtered to only show the current project [TMQUALITY::PROJNO = TMQUALITY::PROJ_TEMP]. It must still not be finding the correct Plot_Count from PlotData though since the summary items still show a total and not an average.

                  As to the Primary and Foreign keys you mentioned I don’t use these for anything but relationships. I originally had a much simpler key set but I read some posts that mentioned this particular technique to avoid any possibility of duplicate keys. This solution (at least in the near future) will be running on a few iPads and the data will be occasionally downloaded to a single workstation (not with FileMaker server at this time). I assume you are inferring that using the Get (UUID) would provide the same protection?

                  • 7. Re: Constraining a found set and Referenceing related fields for calculations.
                    marty72

                    The solution will be running on a few iPads and there would only ever be ~10 or so projects at a time on the units. How quickly will I run into performance issues?

                    I have seen these calculation and summary field setups in some tutorials on-line so that is why I headed in this direction. I am open to suggestions, what direction as to a different design strategy would you suggest?

                    • 8. Re: Constraining a found set and Referenceing related fields for calculations.
                      mikebeargie

                      Get(UUID) generates a string key that is practically unique, 340,282,366,920,938,463,463,374,607,431,768,211,456 possible combinations.

                       

                      This allows for decentralized keys (as opposed to serials) for when you have “offline data” being entered and synced to a server later on.

                       

                      And Get(UUID) is a lot easier to manage, not to mention faster to execute, then the massive calculation you wrote for your key values.

                      1 of 1 people found this helpful
                      • 9. Re: Constraining a found set and Referenceing related fields for calculations.
                        jlamprecht

                        Going off what Mike and Wim said, you have a ton of calculation fields in the TREEDATA table. I would revisit that area and see if you really have a need for that number of calculation fields.

                         

                        As for performance implications, it appears almost all of your calculations are also Unstored. This means that they have to be recalculated every time. If you have enough of them on a layout while going over WAN with your iPads, you are going to have a bad time. I suggest moving any caclulations you can to Stored, so that they get Indexed and do not require a recalulation except in cases where the criteria changes.

                         

                        I hope this helps.

                        • 10. Re: Constraining a found set and Referenceing related fields for calculations.
                          Stephen Huston

                          And, if your calc results rely on related data so you can't store them, you cans still accumulate the results via script triggers and set the results into stored local fields when records are committed. That can essentially eliminate the need for unstored calc fields, and will speed up reporting time, especially via WAN.

                           

                          One real-world reporting situation, where I did this type of elimination of unstore calcs, it cut the reporting time over a LAN to just 10% of what it had been with calcs, and to nearly 1% of what it had taken to run over WAN!

                           

                          Keep in mind your iPads are all either WAN or downloaded (static) data, so they need a fast solution.

                          • 11. Re: Constraining a found set and Referenceing related fields for calculations.
                            marty72

                            Thank you for all of the great advice. I headed down the unstored calc route after I watched some on-line tutorials. I didn't realize at the time that performance would be such an issue. I will look at the idea of moving a number of these to stored fields instead and use of script triggers.

                            The solution will never be running over a LAN but always in a disconnected environment (no wifi or cell coverage in these remote areas) with local data. There would be syncing of data once back at office locations. However, based on what all of you said I guess I will notice performance issues latter once the number of records on the iPad starts to get higher. Presently this is not the case and the solution runs very quickly but I am only in the testing stage.

                            • 12. Re: Constraining a found set and Referenceing related fields for calculations.
                              jlamprecht

                              You may not notice performance degradation now, but you should always approach your code in the most efficient manner. Those unstored calculations will bite you. Many of the calculations can be turned into Stored Calculations and the user would not notice the difference visually. Performance-wise, you have made a big step in the right direction.

                              • 13. Re: Constraining a found set and Referenceing related fields for calculations.
                                Stephen Huston

                                Testing data never gives the real feel of a long-deployed system. The huge system I mentioned revamping had started off with a few thousand records in the early days, long before script triggers. By the time I took over its care and feeding with FMv10, that table had many dozens of unstored calc fields and a half-million records.

                                 

                                At that point, reports were grinding on for several minutes between screen refreshes, and it was clear to me that it was time for structural changes. Long-term users had gotten used to it just gradually getting slower, and had learned to start their reports before breaking for lunch! After the change to stored local numbers set via script triggers, speed was back to nearly-instant.

                                 

                                Keep in mind that, if your system works, it might be around for the long haul, so it must have scalability built in -- working as well with a million records as it does when testing with a hundred. Sure a little hit for quantity is expected, but anything over a few seconds makes it feel broken to your users.