9 Replies Latest reply on Oct 1, 2012 4:59 PM by comment

    Stored Calculation with Related Fields

    alas

      I have two tables. One has a Producer name. The other has a Brand name. The Brand names are related to their corresponding Producer name by a unique key for the Producer.

       

      I then have a calculation field in the Brand table that does Producer & " > " & Brand, so I end up seeing: Producer > Brand as the display name.


      But since this is pulling the Producer name from a related table, I cannot store the calculated result. And therefore I cannot index it, which makes for extremely slow finds, especially since I have over 45k records.

       

      How do I work around this to enable me to create a stored calculation result that can be indexed? Or what alternative is there for creating something more efficient for finds?

        • 1. Re: Stored Calculation with Related Fields
          Mike_Mitchell

          Hello, alas.

           

          If I understand your need here, you want to do a Find on a local field (Brand) and a related field (Producer) at the same time. And, you've found that searching on unstored calculations is slow. Couple of ways to accomplish this:

           

          1) In the same Find request, enter the Brand you want in the Brand field the local table, and the Producer you want in a related field from the same context. This will search as fast or nearly as fast (provided both Brand and Producer can be indexed). If you don't want to force the user to put both items in separate fields, you can script it and use a global field for entry (as one method).

           

          2) Make your calculation an auto-enter calculation instead of a calculation field. If the Brand changes, it will update. If the Producer changes, then you'll need to use something like a Script Trigger to force the related fields to update (since data changes in related records do not force a calculation update). There are other methods as well, but basically, you'll need to ensure that the auto-enter calc gets updated when the source data change.

           

          HTH

           

          Mike

          • 2. Re: Stored Calculation with Related Fields
            alas

            Mike,

             

            Thanks for the reply.  In general you summarize what I am facing, but there is a little variation/difference.

             

            In my situation, the users don't usually know if something they want to find is a producer or a brand.  Hence my calculated field that shows Producer > Brand.  This way whatever they search for, they will find, and then see whether it's a producer or a brand.

             

            So as I understand your #1, I am limited b/c people would need to enter search terms accordingly in a fashion which may require them to know more than they do.

             

            And as for #2, I see your point about making autoenter calculation for a field based on the producer info that is entered, and how an edit to the Brand will force an edit to the Producer autoenter calc.   How would I do the trigger for a producer changing, thus requiring the corresponding brands (which could be between 1 and 100 brands) to force an update to this calculation?

             

            Seems like a lot of overhead required by FM just to handle a simple indexing request, right?

             

            Andrew

            • 3. Re: Stored Calculation with Related Fields
              Mike_Mitchell

              Andrew -

               

              In the situation where you have users who just want to search for a term, and don't know (or probably care) which field it goes in, I suggest using FileMaker's Quick Find feature. Let users enter their search term into the Quick Find field (upper right-hand corner of the Status Bar). Turn off Quick Find on all fields you don't want to include (you can do this in the Inspector).

               

              FileMaker then does the work for you.

               

              HTH

               

              Mike

              • 4. Re: Stored Calculation with Related Fields
                alas

                Actually, there is a little bit more to this.  I am trying to build a simple to use selection picker.  The user needs to assign the brand to a record, but doesn't necessarily know the producer/brand combo or the unique ID for that info.  And with 45k choices, there is no way to use a drop down, popup or checkbox.

                 

                I had built a selection portal, which used filter as you type features to filter down the list of choices.  But it was sluggish, and required a lot of "real estate" on the screen to keep that portal present.

                 

                So I create a pop-up window with a List display for selection (borrowing from what Kevin Frank wrote about http://www.filemakerhacks.com/?p=1731). 

                 

                The "auto update" feature of his work was not practical for me b/c the sluggish nature with 45k records (not indexed b/c of unstored calculations) was too much. So I turned it into a "fill in the box and click find" approach.  Which in theory is fine other than the fact that the find still takes a long time b/c of the unstored calculation nature of my Producer > Brand field that it is searching through.

                 

                Does that help explain some of my predicament? 

                 

                So I am trying to enable a user to enter a Brand ID into a product record (into the BrandID field) by allowing them to use the Producer > Brand display name of the Brand records to find the correct one. 

                • 5. Re: Stored Calculation with Related Fields
                  Mike_Mitchell

                  I'm confused. You have a List view of .... combinations of Product and Brand? And you want the user to be able to select that combination to enter the Brand ID into a new record?

                  • 6. Re: Stored Calculation with Related Fields
                    alas

                    Yeah.  Producer table has PID and ProducerName.  Brand table has BID, fkPID and BrandName.  Brand table ALSO has a field called ProducerBrand which is a calculation field which calculates Producer::ProducerName & " > " & Brand::BrandName.

                     

                    I want to display in list view the ProducerBrand for each record in the Brand table, and have the ability to find only those records matching the search criteria entered by the user (which could be more than one word). 

                     

                    Screen Shot 2012-10-01 at 6.06.40 PM.png

                    When they select the ProducerBrand from the list that corresponds to the brand record they want, it pulls the BID for that record and puts it into the BrandID field in the product table, thus assigning the product to the brand.

                     

                    In the above example the search of that one word took 30 seconds b/c of the number of unstored ProducerBrand calculations for the 45k+ records.

                    • 7. Re: Stored Calculation with Related Fields
                      comment

                      alas wrote:

                       

                      In the above example the search of that one word took 30 seconds b/c of the number of unstored ProducerBrand calculations for the 45k+ records.

                       

                      What would happen if instead of searching  the unstored calculation field you'd search both the related Producer::ProducerName field and the local BrandName.field, e.g.

                       

                      Enter Find Mode []

                      Set Field [ Producer::ProducerName ; AnyTable::gSearchCriteria ]

                      New Request

                      Set Field [ Brand::BrandName ; AnyTable::gSearchCriteria ]

                      Perform Find []

                       

                      where gSearchCriteria is a global field containing the word/s to search for.

                      • 8. Re: Stored Calculation with Related Fields
                        Mike_Mitchell

                        Yeah. That.  

                         

                        Mike

                        • 9. Re: Stored Calculation with Related Fields
                          comment

                          Yeah, well - it remains to be seen how fast is that. Another option, which could be very suitable if the expected number of matches is low (as shown in the example) is:

                           

                          Go to Layout [ Producer ]

                          Enter Find Mode []

                          Set Field [ Producer::ProducerName ; AnyTable::gSearchCriteria ]

                          Perform Find []

                          #

                          Go to Related Record [ Brand ; Match found set ]

                          Enter Find Mode []

                          Set Field [ Brand::BrandName ; AnyTable::gSearchCriteria ]

                          Extend Found Set []