10 Replies Latest reply on Nov 13, 2012 2:03 PM by philmodjunk

    Update/refresh Portal based on ValueListItems-calculation

    PeteTheFreeze

      Title

      Update/refresh Portal based on ValueListItems-calculation

      Post

           Hi All,

           I got a problem with a portal not "refreshing" or "updating" after I change something in a relationship.

           The case: I created an unstored text-calculation field in my MainTable using only the ValueListItems(Get(FileName);"TheList") function. It shows ID's from a related table.

           The field itself works perfectly. Whenever I change a value in my MainTable upon which the TheList is based, I can see that the field containing the ValueListItems-function changes to a list of the right ID's drawn from the relation.

           Now I created a relationship between the ValueListItems-field and the ID-field of an occurence of another table. I made a portal showing all the related records from this new table occurence. The portal shows the right records when I use the GoToRelatedRecord script step from another table (MetaMainTable) to a related record in the MainTable. The calculation field changes to the right List of ID's and the portals shows the records with these ID's.

           However, when I stay in a record in my MainTable and then change the field that is used for the ralationship upon which TheList used in ValueListItems() is based, I only see the calculation field change. The portals shows me the former selection.

           So 1) I made a mistake in my assumption about any of these matters, or 2) I need a script or something to update a portal to reflect the right records from the ValueListItems calculation field.

           I tried some scripts, e.g.

           1) refresh window

           2) gotolayout[b] and bakc to origininal layout

           3) gotolayout[Metamaintable] and back to original.

           These don't work.

           What does work is changing a value in a portal row, then change it back:

           Commit Record/Request[] //to update the ValueListItems-calculation field

           Go to Object{Object Name: "List Portal"]

           Go to Portal Row[first]

           Set Field[Portal::Amount; Value: Portal::Amount + 1]

           Set Field[Portal::Amount: Value: Portal::Amount -1]

           Because the portal then updates the needed rows, but leaves the rest of the rows as before, I will get the wrong selection when I go from many portal records to a few. (The portal shows me the few updated records and then continues with the ones from the former selection.) So I solved that by:

           Go to Layout["Layout2"]

           Go to Layout[original layout]

           The problem is essentially solved, but any suggestions for a more elegant solution are welcome.

            

        • 1. Re: Update/refresh Portal based on ValueListItems-calculation
          philmodjunk

               I think you just need to commit Records and then possibly refresh the window immediately after editing a field that changes the values returned by your calculation.

               If all else fails, there's Refresh Window [Flush Cached Join Results]

               But that step comes with a potentially major performance hit so we try to avoid using that step whenever possible.

          • 2. Re: Update/refresh Portal based on ValueListItems-calculation
            PeteTheFreeze

                 Hai Phil,

                 Thanks for your reply. Your suggestion to use Commit record doesn't do the trick. It is needed to make sure the ValueListItems-calculation updates, but it doesn't update the portal based on it. Flushing is considerably less elegant (from a user perspective) then my solution because ... well ... of the refresh. That isn't needed if I use the Go to Portal Row - Set Field - switch layout - method (in combination with Freeze Window).

                 So I was actually looking for a replacement of this Set Field-method, because it seems to me there should be some built-in Filemaker thing (an Update All Portal Rows script step) that does the same.

                 But thanks anyway.

            • 3. Re: Update/refresh Portal based on ValueListItems-calculation
              philmodjunk

                   But what I suggested was this:

                   Commit Records
                   Refresh Window[]

                   Flush cached Join results was a last resort option.

              • 4. Re: Update/refresh Portal based on ValueListItems-calculation
                PeteTheFreeze

                     Refresh window doesn't do the trick. Flush is needed, so... Still stuck with my unintuitive method.

                • 5. Re: Update/refresh Portal based on ValueListItems-calculation
                  philmodjunk

                       What version of FileMaker are you using? (I want to run a few tests and want to use the same version as you, if possible.)

                       I can't picture this part of what you are describing:

                       

                            Because the portal then updates the needed rows, but leaves the rest of the rows as before, I will get the wrong selection when I go from many portal records to a few. (The portal shows me the few updated records and then continues with the ones from the former selection.) So I solved that by:

                       

                            Go to Layout["Layout2"]
                            Go to Layout[original layout]

                       Why would changing the value of "amount" in the portal row give you the "wrong selection"? What do you mean by "going from many portal records to a few"?

                       Those statements leave me thinking that there is mroe here than I am currently able to tell from your posts.

                  • 6. Re: Update/refresh Portal based on ValueListItems-calculation
                    PeteTheFreeze

                         Hai,

                         I'm using FMP Advanced 11. The text you cited was indeed a little fuzzy. What I mean is illustrated by the following.

                         I have a ValueListItems() return of, and a portal showing:

                         1

                         2

                         3

                         4

                         5

                         I then make a change, which makes the ValueListItems() return:

                         6

                         7

                         What I see in the portal is:

                         6

                         7

                         3

                         4

                         5

                         So the old 3, 4, and 5 are still there. Going to another layout and back somehow reloads the portal which shows me, correctly, only 6 and 7. The same would happen with a flush, but I don't want the user to see this refresh.

                         Thanks for any further input. I must say I really appreciate you contribution (also to the other topics).

                    • 7. Re: Update/refresh Portal based on ValueListItems-calculation
                      philmodjunk

                           Hmmm, I'll test a FMP 11 file after lunch and see if I can figure something out, but one item you can check on your own:

                           Try using List() instead of Valuelist items in the match field for this relationship. It won't drop out duplicate values, but duplicate ID's won't affect what appears in the portal and this simpler set up may update smoothly where ValueListItems does not.

                      • 8. Re: Update/refresh Portal based on ValueListItems-calculation
                        philmodjunk

                             Ran some tests on this file: https://dl.dropbox.com/u/78737945/ValueListItemsPortalFilter.fp7

                             Using Windows XP and Filemaker 11.03, The commit records, Refresh window script--performed from the OnObjectSave script trigger on the Category field smoothly refreshed the portal.

                             Without the portal, things almost worked for me if I just clicked the layout background after selecting a category. Where I got a refresh issue is when I had 3 items showing in the portal and then changing categories resulted in a list of 2 id numbers. Then the third portal row still showed the third item instead of being empty.

                             But the script I recommended was all I needed to correct that.

                             See anything different between your set up and mine?

                        • 9. Re: Update/refresh Portal based on ValueListItems-calculation
                          PeteTheFreeze

                               Tanks.

                               I have the same setup. The problem is probably that the value list (I'm now using the List() as you suggested) is four table occurences removed from the one showing the portal and there are more records in the related table (some 8 to 30 records) + there are several calculation fields showing that seem to take some time. Anyway, your approach works OK in the sample you gave me. And the approach almost works in my solution, except for the following:

                               1. When I filter the portal, Filemaker neglects the filtering in some cases where there are quite a lot of records to show. Go to Portal Row and Set Field seem to solve that.

                               2. Because there is quite a lot of info on the layout, a refresh is less elegant than a freeze window, got to layout["other"], go to layout[original]

                               Attached is the script I am now using. (Because the portal is on a different tab (called Fix List) then the "value list selector", I was lucky enough to attach it as a script trigger.)

                               I think this is OK for now. It does the job and because of this forum post I learned quite a lot. Maybe you could have a look at the portal filter issue, which was unexpected, but anyway, I think we can leave it at this.

                          • 10. Re: Update/refresh Portal based on ValueListItems-calculation
                            philmodjunk

                                 I'm not sure what else i would do to "take a look at the portal filter issue". Technically speaking, we aren't filtering the portal, that uses a boolean expression specified in Portal Setup... but rather are working with a "filtered relationship" that could affect more than just what records appear in a portal.

                                 Issues like this are why I try very hard not to reference table occurrences more than one or two "away" from the layout's table occurrence. It does lead to "tunneling" issues that can be difficult to deal with--especially when the tables have large numbers of records.

                                 Fortunately, FileMaker 12 has introduced an alternative approach in using ExecuteSQL that is greatly reducing a number of such "special purpose" tangles of relationships in our relationships graph...