9 Replies Latest reply on Jun 21, 2012 3:00 PM by philmodjunk

    Cannot get a summary field to update/refresh

    miw

      Title

      Cannot get a summary field to update/refresh

      Post

      I have a layout which includes a summary field which is calculated based on a field selection.  When the field selection is made (via drop down menu), the calculation should update.  I have a script trigger to refresh the layout once the field selection is made.  All the fileds in the layout except this summary field update (including other summary fileds and calculated fileds in the layout).  What could cause a summary filed not to update on a window refresh?

        • 1. Re: Cannot get a summary field to update/refresh
          philmodjunk

          I suggest describing your design in more detail.

          Am I correct that the summary comes from a related table occurrence and that the relationship is either modified by the value in the drop down or you have a filtered portal where the portal filter expression refers to the drop down field?

          Let me know which method you are using.

          As an "initial quick fix", have you tried using Refresh WIndow [flush cached join results]?

          If that works, I recommend examining your design in more detail to see if we can modify it so that such a step is not needed to update your layout. It works, but can carry a very severe performance penalty in many situations so it it best avoided if possible.

          • 2. Re: Cannot get a summary field to update/refresh
            miw

             PhilMod: you are correct in that the summary field comes from a related table occurence and the relationship is modified by the value in the drop down (it does a mathematical calculation based on the input in the value in the drop down (the drop down allows the user to determine how a calculation is made - cost, indexed cost, market value, modified value).  The dropdown menu has an "on modify" script to refresh the window with flush cached join results.

            A new twist, however - when I had this problem, I was on by Mac.  This morning I opened up my database on a PC so I could work on the problem at a different location and the summary field works just fine!  I have to wait until tonight to see if it still gets hung up on my Mac.  Could this be a bug for Mac?  One thing I did not do last night was exit the program and reopen it to see if the summary field remains unrefreshed as the drop down selection changes.  Could things fix themselves upon closing and reopening? 

            • 3. Re: Cannot get a summary field to update/refresh
              philmodjunk

              What is the relationship between the two tables?

              How does changing the value in this field change the type of calculation?

              Have you tried using Commit Record instead of Refresh Window?

              • 4. Re: Cannot get a summary field to update/refresh
                miw

                 I have an InventoryDetail table and a Place table.  InventoryDetailID is joined to the Place table in a field called InventoryDetailIDLink in the Place table.  In the Inventotry table I calculate, in seperate calculation fields (as named in the quotes), the "Cost", "Indexed Cost", "Market Value" and "Modified Value" of that piece of Inventory (four fields, each with its own calculation).  I then have a global field, "ChooseValue" which is determined by the dropdown menu choice of Cost, Indexed Cost, Market Value or Modified Value.  Depending on the selection in the dropdown menu, a final calculated field (ChoosenValueExtended) takes the quantity of the inventory item multplied by the one calculation field choosen to determine which cost to show from the InventoryDetail table.

                In the Place table, I have a calculated field called InventoryPlaceValue which is calculated taking the sum of (InventoryDetail::ChoosenValueExtended).  This field works well to determine the total value of inventory held in each Place.  Thus in my Place layout, I have a portal listing each Place and the total inventory value for each place noted in the portal.  Outside of the portal, I wanted to show the total value of inventory held in all Places (note - inventory does not need to be designated to a place, so the total of Inventory Detail ChoosenValue Extended is not a proxy for this result).  To do that, I created a Summary field in the Place table called TotalValueswPlace which is a summary of the InventoryPlaceValue field - this is what I cannot get to be updated in a window refresh on the Place layout.  On the PlaceLayout, I have the drop down menu for the global field "ChooseValue" noted above.  When the drop down is selected, everything based on the selection changes (such as InventoryPlaceValue) except for the TotalValueswPlace summary field.

                If I leave the Place layout, and go to the InventoryDetail (or any other layout) layout and then back to the Place layout, the summary field in question is updated.  But I have to leave the Place layout for it to update.  I think I was wrong about it now working in the PC environment as  it does not seem to be working on the PC version as well.

                • 5. Re: Cannot get a summary field to update/refresh
                  philmodjunk

                  Your relationship as I understand it:

                  InventoryDetail::InventoryDetailID = Place::InventoryDetailIDLink

                  a final calculated field (ChoosenValueExtended) takes the quantity of the inventory item multplied by the one calculation field choosen to determine which cost to show from the InventoryDetail table

                  Is this a field of type calculation or is it a number field with an auto-entered calculation?

                  In the Place table, I have a calculated field called InventoryPlaceValue which is calculated taking the sum of (InventoryDetail::ChoosenValueExtended)

                  This uses the relationship that I put at the beginning of this post?

                  Thus in my Place layout, I have a portal listing each Place and the total inventory value for each place noted in the portal.

                  Now that has me confused. On what table occurrence is your "place" layout based? (what is selected in Show Records From in Layout Setup...?)

                  Does this portal use the relationship shown at the beginning of this post or a different relationship?

                  To do that, I created a Summary field in the Place table called TotalValueswPlace which is a summary of the InventoryPlaceValue field

                  This will total up records in the place table--hence my question about how your "place" layout and its portal is setup.

                  Did you ever try using commit record as I suggested in my previous post? (Leaving the layout and returning happens to commit the current record on your layout...)

                  • 6. Re: Cannot get a summary field to update/refresh
                    miw

                     PhilMod:  For your first question, it is a calculation field, numeric, which determines the value to be selected to be multiplied by invenotry quantity to get the desired extended value.  For yur second question, yes, the relationship noted at the beginning of your post is important for the InvenotryPlaceValue calculation to work.  For the third item, the portal in the Place layout is based on Place2 (a dupe of Place) such that I can get a portal listing of all Places on the Place layout (it is another relationship of Place to itself) - sorry I forgot to mention the additional relationship of Place to itself ("x").  I did try the "Commit record" as the on modify script for the drop down menu, it did not make a difference as the Summary field still did not refresh.  I know that all my calculated fields are working the way they should, becuase I can verify and see the results.  The summary field that does not refresh, also is correct when I leave and come back tot he layout so I know it is calculating correctly, just not refreshing when it should.

                    I appreciate your help...

                     

                    • 7. Re: Cannot get a summary field to update/refresh
                      philmodjunk

                      What is the exact relationship between place and place2 (what match fields you use can make a difference.)

                      What is the portal filter expression?

                      Is the summary field from Place or place2?

                      Am I correct that it is not in a portal?

                      • 8. Re: Cannot get a summary field to update/refresh
                        miw

                        PhilMod:  The exact relationship is Place::PlaceID X Place2::PlaceID.  The portal in the Place layout is for Place2 and the summary field was Place::TotalValueswPlace and not in the portal.  I just went back into the layout and changed the summary field to Place2::TotalValueswPlace and that seems to work - I guess that was what you were leading me to.  I am no portal expert but I thought using the Place2 fileds needed to be within the Place2 portal and wouldn't be applicable outside the portal; I did not realize the usage of the duped table relationship outside the context of a portal listing. 

                        Thanks for leading this horse to water and leading me in the right direction!

                        • 9. Re: Cannot get a summary field to update/refresh
                          philmodjunk

                          It was one possibility that I had in mind, the other was to put the summary field inside a one row portal with the same portal filter as your first portal--that insures that the summary field totals the same reocrds as those shown in the filtered portal.

                          Basic rules for putting related fields on a layout outside of a portal:

                          If it's anything but a summary field, the value shown in the field will be for the "first" related record. The first related record will be the oldest related record unless you specify a sort order in the relationship--in which case the sort order determines which record is "first".

                          If you place a summary field from a related table directly on the layout, the summary field will summarize the set of related records and display the total.

                          In your case, the summary field from Place summarizes your current found set, which won't change when you select different values for the portal filter.